Error Message

[Rudi]

Hi,

I have used the statement below to find the unique "ID" for a record in a
table that has a certain name in it's "Names" field and a certain date in
it's "Date" field. It worked great for a while, but recently it gives me the
error below, if the Date and Name isn't found in the table. According to the
DLookup function, it should return a Null. Any ideas what the problem might
be? TempID is declared as a Long.

TempID = DLookup("ID", "Timesheet", "[Names] = '" & CurrentUser() & "' And
[Date] = #" & mDate & "#")

Error ->

Run-time error '94:
Invalid use of Null

 

[Tom Wickerath]

Hi Rudi,

You basically answered your own question....since you cannot assign a null
to a long. I find it hard to believe that it ever worked if the username and
date were not found in the table. You can use the Nz function to convert a
null to some other value, such as zero:

TempID = Nz(DLookup("ID", "Timesheet", "[Names] = '" & CurrentUser() & "' And
[Date] = #" & mDate & "#"),0)

Note that Date is not a good choice for a field name. Date is a reserved word:

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Tom
________________________________________

:

Hi,

I have used the statement below to find the unique "ID" for a record in a
table that has a certain name in it's "Names" field and a certain date in
it's "Date" field. It worked great for a while, but recently it gives me the
error below, if the Date and Name isn't found in the table. According to the
DLookup function, it should return a Null. Any ideas what the problem might
be? TempID is declared as a Long.

TempID = DLookup("ID", "Timesheet", "[Names] = '" & CurrentUser() & "' And
[Date] = #" & mDate & "#")

Error ->

Run-time error '94:
Invalid use of Null