Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Checking for Null (2003 SP2)

    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?
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking for Null (2003 SP2)

    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?

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Checking for Null (2003 SP2)

    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.
    Thanks
    chuck

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking for Null (2003 SP2)

    You can test like this:
    <code>
    If DCount("*", "NameOfTheTable", "[ID] Is Null") > 0 Then
    MsgBox "There are missing IDs!", vbExclamation
    End If
    </code>
    Substitute the correct names for the table and ID field.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Checking for Null (2003 SP2)

    That will do it!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •