Checking for Null (2003 SP2)

Topic

New Reply

I have a table listing properties, each with its own unique ID. The Vendor associated with these properties holds our data in their system which assigns each property an unique ID also. For security purposes data received from this Vendor holds only their unique ID and generic detail which I store in another table as I receive it. A third table holds their ID and ours as a combined unique ID.

My database does not assign property IDs and the Vendor receives property information from a source other than myself, so I may receive data from them which has an ID that is not in my combined ID table yet. Consequently, when I update data I receive from them with our ID, occasionally some records have a null value in our ID field. The process of receiving, updating, and exporting the Vendor data is automated with code and I have been trying to figure out how I could check the table for a null value in our ID field from within the code and give an error message.

Any suggestions?

Reply | Quote

Replies

Do you want to check the record you’re currently processing, or do you want to check whether there are any records in the table with a missing ID?

Reply | Quote

The data I receive from the Vendor is imported into a table which has a field to hold our ID. After I update that ID field I need to check if any records are missing an ID.

Reply | Quote

You can test like this:

If DCount("*", "NameOfTheTable", "[ID] Is Null") > 0 Then
MsgBox "There are missing IDs!", vbExclamation
End If

Substitute the correct names for the table and ID field.

Reply | Quote

That will do it!

I have been fooling with DCount for the last hour and wasn’t even close.

Reply | Quote