I have below table in SQL Server which i have linked to MS Access 2010.
Create table test
(
col1 int not null
col2 int not null
)
Insert into Test values (1,2)
Insert into Test values (1,'')
Now when i link the table to access database and open up the table i dont have any issues with that. I tried to copy the test table data from access table and paste it to another access table which is linked to sql server with same schema structure thats when it says "You tried to assign the NULL value to a variable that is not a variant data type"..Its treating the blanks as NULLS when copy/paste the records from one table in access to another.
I'm responsible for an Access 2000 database which three people have open more or less constantly and another four open from time to time.
The database is on a P2P network and is split, with the back end in a single location. The network is wireless.
At the moment there are no problems with this setup but changes are being contemplated which will mean that eight people will have the database open constantly with another four using it occasionally.
For other reasons ( There were only three other wireless networks within range when we started, now there are eight), we are switching the network from wireless to cable. The back end will be transferred to a new PC with a powerful processor and loads of memory.
Does anyone have experience of allowing that many users to log on at the same time in a P2P network? And was the experience good or bad?
:confused: I need to find a way using macros to accomplish this task: I am trying to set the LimitToList property as a YES, and enter data that is not in the list already, but instead of the NotInList property giving me the following error "The item you entered is not in the list. Please choose one from the contents of the drop down box", I want to branch to a macro entered at theNotInList property to branch to that will eliminate the error, and give me the opportunity to enter my own message with options for the user to enter the item into the list, or try to choose an item from the drop down box instead. I want to do this with a macro if possible. I have seen many VB solutions that purport to solve the issue, but to be honest, I am not very well versed in VB procedures. That's why I would like to do it with a macro. I am very competent with macro procedures! Please help ASAP. Thanks.
(value=0 AND name <> HOT) OR (value=Null AND name <>HOT)
I also have some records (4 specifically) that have a name = nullvalue. When I run this query it picks out all the records with a name, and skips HOT(which is correct), but skips the nulls which I also want to include. Can someone help me in finding the reason for this error?
I already posted this problem in the query section, but got no where with it... here is the problem now...
I have a form with three combo boxs - cboStatus - cboFunder - cboResource on my form. Depending on what you chose as an option the subform, which is based on a query will show the data with the chosen criteria.
Only the Status field is a required field, so there are lots of nulls in the funders and the resource fields.
In the all the fields I have "ALL" or "*" as an option.
So in the criteria in the query I use this...
Like fCboSearch (forms!form name!combobox)
This calls up this function...
Public Function fCboSearch(vCboSearch As Variant)
If IsNull(vCboSearch) Or vCboSearch = " " Or vCboSearch = "*" Then fCboSearch = "*" Else fCboSearch = vCboSearch End If
End Function
This works great to show me all the records or just records with a certain criteria selected, BUT BUT BUT I cannot get it to show my the NULLS, they are no where to be found. How can I change this to show me NULLS??
I have a table which records are appended to in batch format. I would like to update a column called batch number for newly appended records with the Max of that column + 1 but I keep getting errors such as "operation must be an updateable query". Anybody have any suggestions. Mind you I am not doing this in VB or SQL formats, I use the query design format. Thanks, Jim
In my Accounts table I have a field called Status. This field is populated by single letters. A=Active, P=Paid and so on. I have a status (R) that is occasionally used. It won't show up in my crosstab queries since we currently do not have an account that is in R status. How can I get my crosstab to show the R status as having 0?
I've been struggling with a problem and I hope one of you can please help.
I created a crosstab query that displays group names on the vertical axis and dates on the horizontal axis. I use a Count for the values. I use a pop-up dialog to get parameters for the dates (between... and), which are organized as Quarters. The query works fine and I can insert it into a report, which also works fine... with one problem. The dates only work when the date parameters request a year (4 quarters), and only work when each quarter has at least one entry. (Only work = I get an error message when one or both of these conditions are violated.)
Sometimes, I want to look at less than a full year, but when I use parameters for three or fewer quarters, I get an error. Same thing if I request a quarter that doesn't have any data. I assume the solution is to convert Nulls to Zeros and have wrestled with both Nz function and IIf, but have not been able to convert the values. I get the correct values in the quary's dataset if they are numbers, and blanks if no data, but no zeros anywhere. I also don't know how to make my report flexible or dynamic, so I can limit the number of quarters if I only want to look at a specific quarter or quarters (less than four).
Any ideas? Thank you so much for your time, and, Happy Holidays!
So, I have two tables that, cut down, look like this:
Table1: Code budget 100 5 110 7 120 3 150 6
Table2: Code actual 100 4 110 9 130 2 150 1
I have another table that is all the codes plus a description.
I thought I was being clever because I realised that there are items in Table1 that do not appear in Table2 and vice versa. I need a query that is, in effect, Table1 minus Table2.
I linked all three tables via “code”, created my query with the minus calculations and thought it had worked.
One problem. Where there is a “code” in one table but not in the other, the query puts a blank or “null” into that field. Then the minus calculation gives, say:
null - 2 = null
I’m used to Excel where:
blank - 2 = -2
Is there any way to get round this please? This must be a common problem, no?
So, I've been doing doing reading on Append Queries and Nulls. I have the following SQL statement
INSERT INTO tblTestResults ( EnrollmentID, TestType, Result, CompletedDate, BoldFace ) SELECT tblEnrollment.EnrollmentID, tblTests.TestsID, [Forms]![frmHome]![subfrmTesting]![txtResult] AS Result, [Forms]![frmHome]![subfrmTesting]![txtCompletedDate] AS CompletedDate, tblOptionsBF.OptionsBFID FROM tblEnrollment, tblOptionsBF, tblTests WHERE (((tblEnrollment.EnrollmentID)=[Forms]![frmHome]![EnrollmentID]) AND ((tblTests.TestsID)=[Forms]![frmHome]![subfrmTesting]![cboSelectTest]) AND ((tblOptionsBF.OptionsBFID)=IIf(IsNull([Forms]![frmHome]![subfrmTesting]![cboSelectBF]),0,[Forms]![frmHome]![subfrmTesting]![cboSelectBF])));
Basically what I'm trying to say is that if the cboSelectBf is Null, then leave it null for the the append query or at least leave it blank. If it isn't, use the value that cboSelectBF has according to the combo box selection.
Will someone look at my option group in my form (frmcustomer) and help me figure out why the option group selections for existing records nulls out (visually you cannot see which button had been previously selected).
The option group is working fine in that it is adding the appropriate selection to the record, but when you open the form up in edit mode the selection has been grayed out.
Hi! I want to add two columns a and b. However, there are rows in which EITHER a OR b is NULL. This should be handled as if the cell would be zero. Is there any way besides filling in 0s in the tables?
I tried SELECT a, b, IIF( a = NULL, b, IIF(b = NULL, a, a+b)) AS c FROM
I'm using Access 2010 and I'm a novice with databases.I have a table that contains water flow readings taken 4 times a day from multiple wells. I am trying to create a select query that will generate the daily average of the 4 flow readings.
Also, my table also has several 0 values and NULLS meaning the well was turned off - I want to exclude the zeros and nulls when I average. How to average these numbers? Do I need to restructure my table?
I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;
DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2)))) DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2)))) DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4)))) Time: IIf([TIME] Is Not Null,[TIME])
When I have a value of Null, i keep getting #Error, I think when it's null.
I'm building an ASP based email application which is to send emails to different user groups. The email addresses in the database need to be able to be assigned to more than one user-group, and an administrator needs to be able to add user-groups to the database.
So effectively, there may be hundreds of user groups and hundreds of users.
What is the best way to store this info in an Access database? Do I let the administrator create a new table column in the userEmails table whenever there is a new user-group added, or do I store a series of userGroupIds in a text field along with each email address and delimit them so that I can split them into an array?
I'm really not sure how to go about this, so any help would be much appreciated.
The DB is for recording Quotes and Orders taken over the phone.
Is it best to have a seperate table w/ a NotesType field so that all notes are stored in a central location? Or is it better to store notes for each quote/order in the Orders / Quotes table itself?
Also, if data is entered into these fields using subforms, is it possible to have the notes displayed outside of the subform in a box format, to allow plenty of typing room?
If anyone has a good example of a smart notes system implementaion, it would be very appreciated.
I have Four tables, CustomerTBL(holds all the customers for both companies); NoteTbl; AgeingTBL1 (this is for one company) ;AgeingTBL2 (this is for a child company)
I need to keep the 2 ageings seperate.
I want to creat a form from the customer table (so you can make edits to contact info and such)
Then I would like a subform with the ageingtbl1 and the note table to be linked together, but I only want the records from the customer table and note table to come back that match the ageing for that perticular customer.
I think I made this more confussing then it needs to be
I have a parent form (called PR_CR_Form) that has a subform called SubFrm_DefectClassification. I am trying to log all changes to the Access database. I am using the VBA code from the Microsoft website for article 197592. It works great. However, it only allows me to capture the changes to the main form. The VBA code has the following statement:
Set MyForm = Screen.ActiveForm
This makes MyForm point to the parent form (PR_CR_Form). What I would like to do is to make MyForm point to the subform (SubFrm_DefectClassification). However, I can't seem to do this. I tried the following:
Set MyForm = Forms!PR_CR_Form!SubFrm_DefectClassification
but this didn't work.
I would very much appreciate it if someone could please tell me how to do this.
I've noticed a few posts on the subject of images but there's lots of different scenarios and I'm hoping someone with far more knowledge can give me some basic pointers for my specific case so I can research it more fully.
Basically part of my database is storing properties, then there's a separate table for property photos as each property can have between 0 and infinity photos (theoretically, the max. is likely to be between 5 or 10).
From my understanding so far it will be best to store a path to each picture as a text field in the database as we're looking at 100+ photos easily from the start which I understand would cause a huge database using OLE linking.
I've got a couple of articles on how to take the path and display it as a picture on the form, not tried it yet but had a read through and it all seems fine.
What I need to ask is the best way to do a couple of things...
When viewing a property via a form is it easy-ish/possible to display thumbnails and captions for all the associated images? Could it then be set up so you could click each image to view it full size?
I need a slightly more user friendly way of adding the photos than just typing in file paths. What I imagine the cleanest way would be blank boxes which you'd click, get a Browse... window to pick the image then once you'd picked one it would place a thumbnail in the box, or something similar. However I'm not sure how well that would scale to a non-specific number of images (ie 0 - 10).
I have no fixed specifications for displaying/adding these images so if you have other ideas/ways of doing it then please suggest - the above is just what I imagine would be 'nice' - if it involves way too much coding then it's not worth it at this stage.
Any help / pointers would be most appreciated :) Let me know if you need more info.
Any suggestions on how to handle delimiters when creating an SQL query string?There's a query form with a number of text boxes. User can enter text in one or more boxes. The VBA code checks the text boxes and generates the query string from what's been entered.eg. strSQLString = "SELECT * FROM [myTable] WHERE [Surname] = ' " & txtSurname & " ' "If the user enters a name such as O'Neil this will result in an invalid SQLString because of mis-matched single quotes."SELECT * FROM [myTable] WHERE [Surname] = ' O ' Neil ' "How can I handle input text which may contain delimiters?
Thanks buddies for all ur help for the threads that i posted earlier!! Again i have some questions..
The form that i created is one that shows records that are not in a particular table , once its updated to the table through a submit button on the form, it does not appear on the form (meaning, the form shows only ones that are still not been processed,) so finaaly when it reaches the end of file, the form becomes blank and "ACCESS says it had encountered an error and needs to close" and creates backup copy..i dont want this to happen when the users use it. I tried giving a recordset.Eof condition , when true giving a message to the user that "all have been done", but as soon as it jumps into that if block when it reaches EOF condition Access Pops up that message, How to solve this???
