View Full Version : Using a date in a dlookup
KimKelly147
05-17-2017, 01:21 AM
Hi All,
I know this is a common question, and I have desperately been searching the forum for answers.
I have used every possible format that I have found and cannot figure out what i'm missing.
Here is my lookup:
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "[StartDate]=#" & Format(Me.StartDate, "d/mm/yyyy") & "#")
I'm in Australia and have tried multiple date formats to make this work but I keep getting the data type mismatch error.
I tested the code without the date and it works fine, but I need the date to ensure that my users aren't submitting duplicate records.
Please help me.
Thank you,
Kim
Date literals are US format #5/17/2017#
KimKelly147
05-17-2017, 02:14 PM
Thank you, I tried both:
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "[StartDate]=#" & Format(Me.StartDate, "mm/dd/yyyy") & "#")
and
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "[StartDate]=#" & Format(Me.StartDate, "m/dd/yyyy") & "#")
And both returned the type mismatch error.
Is there maybe something else I am doing wrong?
FYI, Format(CDate("5/7/2017"), "m/dd/yyyy") = "5/07/2017"
KimKelly147
05-17-2017, 03:09 PM
Still returning the error for both:
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "[StartDate]=#" & Format(CDate(Me.StartDate), "m/dd/yyyy") & "#")
And
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "[StartDate]=" & Format(CDate(Me.StartDate), "m/dd/yyyy"))
HiTechCoach
05-17-2017, 08:10 PM
Try:
Format(Me.StartDate, "mm\/dd\/yyyy")
This should help:
International Dates in Access (http://hitechcoach.com/microsoft-office/access/access-links/62-access-dates-tips-and-tricks/78-international-dates-in-access)
KimKelly147
05-17-2017, 08:44 PM
Thanks for that, I had previously read that article and followed the steps
Unfortunately I still got the error for all of these attempts:
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "[StartDate]=" & Format(Me.StartDate, "mm\/dd\/yyyy"))
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "[StartDate]=#" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#")
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "#[StartDate]#=#" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#")
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]= '" & Me.Combo33 & "'" And "#" & Format([StartDate], "mm\/dd\/yyyy") & "#=#" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#")
I am quickly losing all hope :crying:
Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]='" & Me.Combo33 & "' And [StartDate]=#" & Format(Me.StartDate, "mm/dd/yyyy") & "#")
KimKelly147
05-18-2017, 03:26 AM
I used that one above.
But it's ok. I've solved the problem using indexed relationships and a non-date lookup about 10 mins ago.
Thank you for all of your help.
You used that date format, but I fixed your quote marks and put the field back in the correct place.
Glad you got it working anyway :)
KimKelly147
05-18-2017, 01:40 PM
Oh, I see the difference now. I've saved the formula for the future.
Thank you again for your help.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.