Using a date in a dlookup

[KimKelly147]

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

[jonh]

Date literals are US format #5/17/2017#

[KimKelly147]

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?

[SamT]

FYI, Format(CDate("5/7/2017"), "m/dd/yyyy") = "5/07/2017"

[KimKelly147]

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]

Try:

Format(Me.StartDate, "mm\/dd\/yyyy")

This should help:

International Dates in Access

[KimKelly147]

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

[jonh]

Me.Text68 = DLookup("ID", "Leave Database", "[StaffName]='" & Me.Combo33 & "' And [StartDate]=#" & Format(Me.StartDate, "mm/dd/yyyy") & "#")

[KimKelly147]

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.

[jonh]

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]

Oh, I see the difference now. I've saved the formula for the future.

Thank you again for your help.