DLookUP and Null

D

DS

I have this DLookup statement that has NULL's in it but I can't seem to nail
down the syntax. Any help apreciated.
Thanks
DS

Me.TxtJobID = DLookup("LogJobID", "tblTimeLogs", "LogEmpID= " &
Forms!frmSignOut!TxtID & " And LogTimeOut = Null And LogDateOut = Null)
 
F

fredg

I have this DLookup statement that has NULL's in it but I can't seem to nail
down the syntax. Any help apreciated.
Thanks
DS

Me.TxtJobID = DLookup("LogJobID", "tblTimeLogs", "LogEmpID= " &
Forms!frmSignOut!TxtID & " And LogTimeOut = Null And LogDateOut = Null)

A Field can't = Null but you can find out if it IsNull() or Is Null.
Also you need to enclose the where clause argument in Quotes. You left
the last one off.

Using [FieldName] Is Null syntax:

Me.TxtJobID = DLookup("LogJobID", "tblTimeLogs", "LogEmpID= " &
Forms!frmSignOut!TxtID & " And LogTimeOut Is Null And LogDateOut Is
Null")

Or using the IsNull(FieldName) syntax:

Me.TxtJobID = DLookup("LogJobID", "tblTimeLogs", "LogEmpID= " &
Forms!frmSignOut!TxtID & " And IsNull(LogTimeOut) And
IsNull(LogDateOut)")

Note: If this code is on the form named frmSignOut you can substitute
the Me keyword for forms!frmSignOut.

"LogEmpID= " & Me!TxtID & " And IsNull(LogTimeOut) And
IsNull(LogDateOut)")
 
S

Stuart McCall

DS said:
I have this DLookup statement that has NULL's in it but I can't seem to
nail down the syntax. Any help apreciated.
Thanks
DS

Me.TxtJobID = DLookup("LogJobID", "tblTimeLogs", "LogEmpID= " &
Forms!frmSignOut!TxtID & " And LogTimeOut = Null And LogDateOut = Null)

Me.TxtJobID = DLookup("LogJobID", "tblTimeLogs", "LogEmpID= " &
Nz(Forms!frmSignOut!TxtID) & " And LogTimeOut Is Null And LogDateOut Is
Null")

Null is just what it says, ie nothing. You can't compare this with anything,
but instead use the Is operator to determine it. Also you were missing an
ending double-quote.

I added the Nz function for good measure, in case Forms!frmSignOut!TxtID
should ever be Null. If this can never be the case, just remove it again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top