Access dlookup invalid use of null

[cuddihy1990]

Hi, in a nutshell I have a button on a form that is used to update a table based on data entered into a multi line textbox.

However, using this data I need to find information from a different table based on information stored in variables.

Below is a snippet of the code used;

EmpName = DLookup("[Name]", "dbo_BomEmployee", "Employee =" & BUILDID)

OvenName = DLookup("[Name]", "dbo_BomEmployee", "Employee =" & OVENID)

Status = DLookup("[Status]", "Employees_Performance", "LotNumber =" & LOTNUMBER)

The top 2 dlookups work, but the bottom one doesnt and returns an Invalid Use of Null error message. This shouldn't be the case because the data is in the Employee Performance table (The data in the variable 'LOTNUMBER' is in the Employee_Performance table under the LotNumber column.

Most forums that I have checked out have said use the NZ function in front of the DLOOKUP to allow null values to return but null values should not be returning in the first place.




Any help would be greatly appreciated...

Bill

[hapm]

What is the type of your Status variable? And are you sure that there is no LOTNUMBER with a null value?

[lfpm062010]

Is "LotNumber" a text or number data type?

If text then you need sigle or double quote around the "LOTNUMBER".

[cuddihy1990]

lfpm062010 you beauty, cheers I have been banging my head against a brick wall for the majority of the afternoon, I didn't think that double quotes would work as I thought that it would take the word LOTNUMBER rather than what was in the variable but yes it has worked now thanks both

[atuhacek]

Mark as solved