Dlookup with conditional problem

[Hasher]

Gday

I am trying to match if a user has already started a record using the date and username. I have written the following code but I keep getting a error 94 about null. I know its an issue involving the date part.

Any help would be great with why this isn't working.

DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#")))

The full code

 
   Dim Date2 As Date


    Date2 = Date
If (Not IsNull(DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#"))) Then 
           lngEmployeeID = DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#")
  DoCmd.OpenForm "frmTimeSheetMain", , , "TimeSheetID=" & Nz(lngEmployeeID), , , "NoTimeSheetID"

[ranman256]

You cant run it if date is null, so prevent it:

if IsNull(Date2) then 
   msgbox " Date missing"
else
    DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#")))
...

[John_G]

There is no problem with Date2, because you specify Date2 = Date.

Can you be sure that this statement:

lngEmployeeID = DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#")

does not return a Null? A Null will cause an error if lngEmployeeID is not a Variant type.

Try this:

lngEmployeeID = nz(DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#"), -1)
msgbox "Employee ID = " & lngEmployeeID

to verify that you are gerring the right data.

John

[Hasher]

Thanks John_G for the reply.

I am still having the problem. I tried your suggestion and it returned "-1"

The problem is how I am doing the date as I have tried it without the conditional AND and date and it finds the employee just fine.

Any other suggestions or should I try another approach ?

Thanks
Paul

[June7]

Why bother with the Date2 variable?

Dim lngEmployeeID As Long
lngEmployeeID = Nz(DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy, 0) & " AND TimeSheetDate=Date()"), 0)
If lngEmployeeID > 0 Then
DoCmd.OpenForm "frmTimeSheetMain", , , "TimeSheetID=" & lngEmployeeID, , , "NoTimeSheetID"
Else
MsgBox "No record"
End If

I am a little confused.
Why is the variable lngEmployeeID set to TimeSheetID? Should the variable be lngTimeSheetID?
If the form should open only if lngEmployeeID has value, why the "NoTimeSheetID" text for OpenArgs?

[Hasher]

Thank you June7 . That worked .Thank you for your help , code is very robust now . Thanks !!!!!!