Dloopup Run Time error '3075' data type Mismatch

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Vasago
    New Member
    • Mar 2014
    • 46

    #1

    Dloopup Run Time error '3075' data type Mismatch

    Can't seem to correct the last criteria



    [Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee=' " & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "#" & "' And [Trip Sign On]=0"), 0)
  • Seth Schrock
    Recognized Expert Specialist
    • Dec 2010
    • 2965

    #2
    You have an extra single quote before the second and. The reason you needed it before the first AND was because you needed to close the single quote that you had (and needed) after the Employee='. You also don't need to split up the ending hash mark from the rest of your criteria. So something like this should work fine (added a little change to make troubleshooting easier).
    Code:
    Dim strCriteria As String
    strCriteria = "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "# And [Trip Sign On]=0"
    [Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", strCriteria), 0)

    Comment

    • jimatqsi
      Moderator Top Contributor
      • Oct 2006
      • 1293

      #3
      Vasago,
      It's a really bad idea to name a column, in a table or in a query, "Date". That's because "Date" is already a keyword as you demonstrate in your code, calling the Date function.

      I think if you change the & Date to
      Code:
      Format(Date(),'MM/DD/YY')
      it will work.

      Jim

      Comment

      Working...