DLookUP and Null

  • Thread starter Thread starter DS
  • Start date Start date
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)
 
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)
Click to expand...

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)")
 
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)
Click to expand...

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.
 
Back
Top