Dlookup not returning correct value

[Vasago]

My dlookup is returning a 0 when it should return a value of 935. I have double check the spelling of the variable and tables. I can't seem to figure out where I'm going wrong on this on.

[Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "#" & " And [Trip Sign Off]=0"), 0)

Thanks for your help,
Vasago

[Vasago]

I did have one after thought. Could it be because my tables date also has the time stamp attached to the date and how would I add in a format dates to "short date" into the dlookup

[Seth Schrock]

First, lets start by splitting out your criteria into its own string so that we can debug your code.

dim strCriteria as string
strCriteria = "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "# And [Trip Sign Off]=0"
debug.print strCriteria
 
[Punch ID] = Nz(DLookup("Punch ID", "Copy of Employee Work Statistics1", strCriteria), 0)

The fact that it is returning 0 and that you are using the Nz() function with 0 as the return value makes me think that your DLookup() was returning a null value. Try my code, run it and the post back what gets entered into the immediate window (Ctrl + g to view it). It is possible that the square brackets were also causing the problem, but I'm not positive of that.

[Vasago]

Here is what was in the immediate window.

"Employee='Batson, Leroy' And [Date]=#4/7/2014# And [Trip Sign Off]=0"

[Vasago]

My [Date] field that it is looking up is formatted to a Medium Date. Would that cause issues with the dlookup using the current date?

[Seth Schrock]

Access is usually pretty good about comparing dates since it is actually comparing the date value (an integer) not the format of the date. It is a bad idea to use reserved words as field names (such as Date). Even when put in brackets, it can sometimes cause problems.

Can you verify the data types of the Employee and [Trip Sign Off] fields that Employee is a text data type and [Trip Sign Off] is a number or yes/no field?

Try creating a query such as the following and see what errors you get or what the results are.

SELECT [Punch ID]
FROM [Copy of Employee Work Statistics1]
WHERE Employee='Batson, Leroy' 
  And [Date]=#4/7/2014# 
  And [Trip Sign Off]=0

[Vasago]

[Trip Sign Off] Data Type = Date/Time (formatted as short time)
[Employee] Data Type = Short Text

Creating the query listed above outputs the value I am looking for in the Dlookup.

[Vasago]

Here is all the code on the Form Load. Just in case this help out at all.

Private Sub Form_Load()
Dim strCriteria As String
 
strCriteria = "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "# And [Trip Sign Off]=0"
 
Debug.Print strCriteria
 
[Punch ID] = Nz(DLookup("Punch ID", "Copy of Employee Work Statistics1", strCriteria), 0)
 
[Type of Work] = Nz(DLookup("[Type of Work]", "[Copy of Employee Work Statistics1]", strCriteria), 0)
If [Type of Work] = "" Then
 
Else
Me.[Type of Work].Locked = True
[Trip_S_on] = Nz(DLookup("[Trip Sign On]", "[Copy of Employee Work Statistics1]", strCriteria), 0)
If [Type of Work] = "Transportation Specialist" Then
Me.[bus number].Visible = True
[bus number] = Nz(DLookup("[Bus Number]", "[Copy of Employee Work Statistics1]", strCriteria), 0)
Me.[bus number].Locked = True
 
Me.Route.Visible = True
[Route] = Nz(DLookup("[Route]", "[Copy of Employee Work Statistics1]", strCriteria), 0)
Me.Route.Locked = True
Else
Me.Route.Locked = False
Me.School.Locked = False
Me.[bus number].Locked = False
End If
If [Route] = "field Trip" Then
Me.[School].Visible = True
[School] = Nz(DLookup("[School]", "[Copy of Employee Work Statistics1]", strCriteria), 0)
Me.School.Locked = True
Else
Me.School.Locked = False
End If
End If
 
End Sub

[Seth Schrock]

And you are still getting 0 (a wrong value) when using the dlookup()? Hmmm... Try this

dim strCriteria as string
strCriteria = "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "# And [Trip Sign Off]=0"
debug.print strCriteria
debug.print DLookup("Punch ID", "Copy of EmployeeWork Statistics1", strCriteria)
 
[Punch ID] = Nz(DLookup("Punch ID", "Copy of Employee Work Statistics1", strCriteria), 0)

[Seth Schrock]

Since you are doing so many DLookups on the same table, you would probably increase performance if you just opened the recordset and used the values from that. All the domain functions (those that start with D; DLookup, DCount, DSum, etc.) are very resource intensive and therefore slow down your code. You can open your recordset like this:

Dim db as DAO.Database
Dim rst As DAO.Recordset
Dim strRst as String
 
Set db = CurrentDb
strRst = "SELECT [Punch ID] " & _
         "FROM [Copy of Employee Work Statistics1] " & strCriteria
Set rst = db.OpenRecordset(strRst, dbOpenDynaset)
 
'you can reference your values like this
[Punch ID] = rst![Punch ID]
 
 
'At the end of your code, close what you open 
'and release what you set
rst.close
set db = nothing
set rst = nothing

[Vasago]

Seth,

I can't thank you enough for your help and for the "access VBA Class".

Sincerely,
Vasago