I KEEP GET A ERROR MESSAGE run time error 94 invalid use of null (1 Viewer)

MIkeD666

Registered User.
Local time
Yesterday, 18:07
Joined
Jan 12, 2019
Messages
40
When i Add this Dlookup below to my code

"DOB = DLookup("[DOB] ", "staff1", " [DOB] = #" & forms!NewStaff!txtdob& "#") "
I get the errror message

the field DOB in the table and the venerable are both date type values

The full bit of code looks like this
sn = DLookup("[Sirname]", "staff1", " [Sirname] ='" & Forms!Newstaff!TxtSName & "'")
DOB = DLookup("[DOB] ", "staff1", " [DOB] = #" & Forms!Newstaff!TxtDOB & "#")
The first line works great, but the second line give the error message "invalid use of null"
I have check the table and the are no null values stored


AM using then to check the sir name of a user and the dob of a user are not in the table already.

I hope i have explained myself clearly enough.
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:07
Joined
Sep 21, 2011
Messages
7,511
Put the criteria into a string strDOB and use that instead.
Then you can Debug.Print strDOB.

Forms!Newstaff!TxtDOB will likely be Null?
Possibly use the NZ function?

However that is just checking for a date, so if mine and your DOB are the same then it would find mine when looking for yours and vice versa.?
You would also need to check StaffID, so making a string makes even more sense, as you can check the syntax better.?

HTH
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:07
Joined
Oct 29, 2018
Messages
13,766
DOB = DLookup("[DOB] ", "staff1", " [DOB] = #" & Forms!Newstaff!TxtDOB & "#")
Please pardon my intrusion, when I saw this part of the code, I'm sorry, but it cracked me up a bit. You want to "look up" a DOB that matches a date you entered on a form and assign that value to a variable. I always wondered why we sometimes look up a value that we already have. Maybe it's just a matter of personal preference, but if we're trying to check for an existence of a record/value, I usually prefer using the DCount() function. However, I know some folks still use DLookup() arguing it takes less resources than DCount().

Anyway, sorry for the interruption. Please continue... Cheers!
 
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 02:07
Joined
Jul 21, 2014
Messages
557
Depending on your local regional settings/date format, you may wish to try:
Code:
  DOB = DLookup("[DOB] ", "staff1", " [DOB] = " & Format(Forms!Newstaff!TxtDOB, "\#yyyy\-mm\-dd\#") )

If no record matches, you will still get the error as the Nz() returns Null if no match is found and you can't stuff Null in to a string variable.

To cover yourself from that you can use:
Code:
  DOB = Nz(DLookup("[DOB] ", "staff1", " [DOB] = " & Format(Forms!Newstaff!TxtDOB, "\#yyyy\-mm\-dd\#") ), vbNullString)

hth,

d
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:07
Joined
Sep 21, 2011
Messages
7,511
Please pardon my intrusion, when I saw this part of the code, I'm sorry, but it cracked me up a bit. You want to "look up" a DOB that matches a date you entered on a form and assigned that value to a variable. I always wondered why we sometimes look up a value that we already have. Maybe it's just a matter of personal preference, but if we're trying to check for an existence of a record/value, I usually prefer using the DCount() function. However, I know some folks still use DLookup() arguing it takes less resources than DCount().

Anyway, sorry for the interruption. Please continue... Cheers!
Well spotted theDBguy, I was concentrating on the problem. :oops:
 

Cronk

Registered User.
Local time
Today, 13:07
Joined
Jul 4, 2013
Messages
2,469
But the code does check that Forms!Newstaff!TxtDOB exists in the table or not, which is what the OP wanted. Probably the intent of the code is to check if a person has already been added to the table.

As to the error message, I wonder what sort of variable DOB is.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:07
Joined
Oct 29, 2018
Messages
13,766
But the code does check that Forms!Newstaff!TxtDOB exists in the table or not, which is what the OP wanted. Probably the intent of the code is to check if a person has already been added to the table.

As to the error message, I wonder what sort of variable DOB is.
Hi. Yes, both DCount() and DLookup() can work in this case. What I find interesting is "looking up" a value you have to see if it's in a table by using that value. It makes sense in real life, but in the syntax of a DLookup() expression, it just seems redundant to me. I view DLookup() as something you would use to find something you have no idea what its value is.

In other words, this make sense: "Look up the value of DOB for a student with a first and last name of John Doe"

This one seems redundant: "Look up the DOB of a student with a DOB of March 4, 2020"

What do you expect would be the result of DLookup() in the latter case?

Cheers!
 

Cronk

Registered User.
Local time
Today, 13:07
Joined
Jul 4, 2013
Messages
2,469
This one seems redundant: "Look up the value of DOB for a student with a first and last name of John Doe"

But if I put the question another way, is there a record already existing for a person named John Doe with that DoB? If so, we wouldn't want to add a duplicate record.

I've had situations where I've had to import personnel into a db from multiple sources. At first pass, firstname, surname and DOB will catch duplicates. Just first/last names are not enough for John Doe, John Smith etc which are more likely to occur the larger the group of people involved. There's still possible problems with name changes, so address, phone number comparisons and email addresses may help.

One job required combining individual lists of military awards going back to WW1 into one db. In that case, even the service number was inadequate because some had re-enlisted and been issued with a different service number. Date of death (if available) and dates of any long service awards were also used. Any doubtful matches remaining were displayed to the user with possible matches for the decision to add a new record or not.

Coming back to using a number of Dlookups on individual fields, it's inefficient and inelegant. Any reasonably proficient Access user would have a recordset or query to determine duplicates.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:07
Joined
Oct 29, 2018
Messages
13,766
But if I put the question another way, is there a record already existing for a person named John Doe with that DoB? If so, we wouldn't want to add a duplicate record.
Again, as I said, DLookup() is fine for that. But, I just thought a DCount() makes more sense to me. So, when I want to ask the question "does a record exist?," I don't translate it to "look up a value;" but rather, "see if there are any" records in the table.

Does that make sense? In other words, if I want to see if a DOB of March 4, 2020 already exists in the table to avoid a duplicate record, I tend to use something like this:
Code:
If DCount("*","TableName","DOB=#3/4/2020#")>0 Then
    'already exists
Else
    'no match
End If
Hope that clarifies a bit about my "OCD" regarding using DLookup() for checking for duplicate records. Cheers!
 

Cronk

Registered User.
Local time
Today, 13:07
Joined
Jul 4, 2013
Messages
2,469
Can't say I disagree with using DCount. However, if the purpose of the exercise is to see if a record for a person already existed, I'd use a query and combine multiple existence checks in the one operation rather than the OP presumably using a series of dlookups (not that the WHERE clause of Dcount could not be used with multiple criteria). To each his own, no?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:07
Joined
Oct 29, 2018
Messages
13,766
Can't say I disagree with using DCount. However, if the purpose of the exercise is to see if a record for a person already existed, I'd use a query and combine multiple existence checks in the one operation rather than the OP presumably using a series of dlookups (not that the WHERE clause of Dcount could not be used with multiple criteria). To each his own, no?
Hi. Thanks. I agree. That's exactly what I said in Post #3. And I quote: "Maybe, it's just a matter of personal preference."
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:07
Joined
Feb 28, 2001
Messages
18,773
If I recall correctly, though, there IS a reason to use DCount in preference to DLookup sometimes. If there is a NULL in one of the fields being counted, DLookup can't work with that too well, but DCount doesn't barf as often when nulls are involved. (It's a relative thing.)
 

MIkeD666

Registered User.
Local time
Yesterday, 18:07
Joined
Jan 12, 2019
Messages
40
Forms!Newstaff!TxtDOB will likely be Null?
Possibly use the NZ function?
Hi and thank you. I have not tried your method yet, but i could not for the life of me remember the nz function. so that alone is very helpful. i will keep you in formed
 

MIkeD666

Registered User.
Local time
Yesterday, 18:07
Joined
Jan 12, 2019
Messages
40
If I recall correctly, though, there IS a reason to use DCount in preference to DLookup sometimes. If there is a NULL in one of the fields being counted, DLookup can't work with that too well, but DCount doesn't barf as often when nulls are involved. (It's a relative thing.)



thank for your comments, which are very useful, i be testing the ideas later.
 

MIkeD666

Registered User.
Local time
Yesterday, 18:07
Joined
Jan 12, 2019
Messages
40
Put the criteria into a string strDOB and use that instead.
Then you can Debug.Print strDOB.

Forms!Newstaff!TxtDOB will likely be Null?
Possibly use the NZ function?

However that is just checking for a date, so if mine and your DOB are the same then it would find mine when looking for yours and vice versa.?
You would also need to check StaffID, so making a string makes even more sense, as you can check the syntax better.?

HTH
Hi i have finally got back to this just now. been busy on other issues. Yes what am trying to do it use the fields named SirName ( sir name) and DOB to check the details entered on a form are not already in the staff table.
I was assigning the result of the dlookup using theForms!Newstaff!TxtDOB & "#") to a temporary venerable so i could check the result. But i keep getting the null error. YOU have all given me great advice and i AM considering the option and deciding which one my peanut brain can handle best and remember it for next time.

Thank you all
 

MIkeD666

Registered User.
Local time
Yesterday, 18:07
Joined
Jan 12, 2019
Messages
40
Hi i have finally got back to this just now. been busy on other issues. Yes what am trying to do it use the fields named SirName ( sir name) and DOB to check the details entered on a form are not already in the staff table.
I was assigning the result of the dlookup using theForms!Newstaff!TxtDOB & "#") to a temporary venerable so i could check the result. But i keep getting the null error. YOU have all given me great advice and i AM considering the option and deciding which one my peanut brain can handle best and remember it for next time.

Thank you all
I gone with a dcount to check the data, it work first time and it's simple of my brain to hold in storage... Thank a lot
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:07
Joined
Oct 29, 2018
Messages
13,766
I gone with a dcount to check the data, it work first time and it's simple of my brain to hold in storage... Thank a lot
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:07
Joined
Sep 21, 2011
Messages
7,511
I gone with a dcount to check the data, it work first time and it's simple of my brain to hold in storage... Thank a lot
As I mentioned, if we have the same DOB and you are checking for me, and you are already in the table, then the test will be true, but will actually fail.?
You need to check the person as well?
 

MIkeD666

Registered User.
Local time
Yesterday, 18:07
Joined
Jan 12, 2019
Messages
40
As I mentioned, if we have the same DOB and you are checking for me, and you are already in the table, then the test will be true, but will actually fail.?
You need to check the person as well?
yes i am cheeking the individual person on 3 values.
 

Users who are viewing this thread

Top Bottom