Syntax Error

[khurram7x]

Where's the syntax error in this DLookUP Query please, when there's a NULL value in WorkPackID field? If it is NOT NULL, it works fine.

DLookup("EmployeeID", "tblEmployee_WorkPack", "WorkPackID = " & Me.WorkPackID & " And DateValue(Assignment_Date) = DateValue(#" & Me.Assignment_Date & "#) And EmployeeID = " & Me.EmployeeID)

Attaching error screenshot.

Thanks,
K

 

[bob fitz]

Have you tried using the Nz() to return a value if Me.WorkPackID is Null.
Something like:

DLookup("EmployeeID", "tblEmployee_WorkPack", "WorkPackID = " & Nz(Me.WorkPackID,0) & " And DateValue(Assignment_Date) = DateValue(#" & Me.Assignment_Date & "#) And EmployeeID = " & Me.EmployeeID)

 

[khurram7x]

Have you tried using the Nz() to return a value if Me.WorkPackID is Null.

Thanks, sorted my problem. Never thought of it!!
Why it happens by the way, if you don't mind sharing.

Thank you,
K

 

[bob fitz]

Glad it worked for you.
If you look at the error message carefully you will see that it says:
"....WorkPackID= AND...."
It has no value in the expression for the value of "Me.WorkPackID" and so we must use the Nz() function which will return a value when a variant is null.
For more about Nz() see: http://www.techonthenet.com/access/functions/advanced/nz.php