Not updating access query for null
If all of the specified fields are Null, the record is not counted. A field with a NULL value is a field with no value.But, since there is usually one field in Table2 that is null, none of the other fields are not null won't update. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the field in table2. If the users want to change a field in the main database (table1), they fill the change in to the form (which is linked to table2) If there is no change to the field, they simply leave it blank and that particular field won't be updated on the main database(table1). So basically I guess my statement above says something like, if field1 in table2 is not null, update field1 in table1 to the value of field1 in table2, if field1 in table2 is null, update field1 in table1 to itself.Also, I've tried to use an IIf statment in the Field line: something like IIf([Table2]! The users can change up to 30 fields listed on the form. Of the 30 available fields, only 3 of them are filled in and the rest are null. The problem here I get is an error that says my IIf(*****) statement is not a valid name or the text is too long.If you use an asterisk, Count calculates the total number of records, including those that contain Null fields.Count(*) is considerably faster than Count([Column Name]).
So I want to be able to update just those 3 fields in the main table (table2). But I'm trying to automate this process and want to be able to do this update many times for different records. Does all this make sense and if so do you know a way to accomplish this? Store each field in a variable, and compare them to the original record using a DLookup.Also, I've tried to use an IIf statment in the Field line: something like IIf([Table2]! Here's something more specific that I need to do or could do in a macro or vba procedure: Check each record (in Table2) to see if the "switch" is flipped to yes, if yes, then I check each field in the record (in Table2 still). If the field is not null, then I change the corresponding field in Table1 to the value in table2. Based on the above detail I added would you still do what you recommended by storing each field in a variable & such??