Date format

[NT100]

Hi
I've a field name called "DtTeach" type Date and is set to medium date as "dd-mmm-yy". e.g. 10-Nov-16.

I do a look up into the same table with the following DLookup and got the date whose format is 11/10/2016.

However, I got run-time error "3021": No current record. Actually, I got 2 records with the same date, e.g. 10-Nov-16.


The following is scripts

Stop

dDtTeach = DLookup("[DtTeach]", "tblTeachTT_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation & " AND [Session] = " & iSession)
dTeach = Format(dDtTeach, "dd\-mmm\-yy")

Debug.Print "dDtTeach format is "; dDtTeach

Debug.Print "dTeach format is "; dTeach

sSelectDt = "SELECT * FROM tblTeachTT_FPA WHERE DtTeach = " & dTeach & " AND AcademicYr = " & txtApptYr.Value


The debug output are below

dDtTeach format is 11/10/2016
dTeach format is 11/10/2016
SQL sSelectDt is SELECT * FROM tblTeachTT_FPA WHERE DtTeach = 11/10/2016 AND AcademicYr = 2016

Would you have an idea on this.

 

[arnelgp]

make the variable dDtTeach as Date. then on your code:


"SELECT * FROM tblTeachTT_FPA WHERE DtTeach = #" & dDtTeach & "# AND AcademicYr = " & txtApptYr.Value


or



"SELECT * FROM tblTeachTT_FPA WHERE DtTeach = #" & Format(dDtTeach,"mm/dd/yyyy") & "# AND AcademicYr = " & txtApptYr.Value

 

[NT100]

Great!
Thank you very much.

 

[GaryDab]

make the variable dDtTeach as Date. then on your code:


"SELECT * FROM tblTeachTT_FPA WHERE DtTeach = #" & dDtTeach & "# AND AcademicYr = " & txtApptYr.Value


or



"SELECT * FROM tblTeachTT_FPA WHERE DtTeach = #" & Format(dDtTeach,"mm/dd/yyyy") & "# AND AcademicYr = " & txtApptYr.Value

Thank you Arnel, that works perfectly.