SQL query in vba won't find records after a certain date??

[Fionn]

Hi
I'm having a very strange problem with access. In vba I have an sql statement "select StudentID, CourseID from tblAttendance where AttendanceDate = #Whatever Date#".


Now this works for all dates (as far as I can see) before 01/07/2013 but after that it just doesn't find any records. I have manually added dates and checked and it didn't work.
Does anyone know how this could be possible? The date field is not in any relationship and it is Date/Time format. I don't even know how to go about troubleshooting this as it makes no sense to me.
Thanks
Edit: It thinks these records have a date below 1/7/2013 and it can find dates after 1/12/2013.

[JoeM]

Do you date fields have a time component to them?

It is a bit tricky finding records that fall on a certain day if there is a time component, i.e.
if the value of the date field is 1/7/2013 1:23 PM, using criteria of

=#1/7/2013#

will NOT return that record, but

Between #1/7/2013# and #1/8/2013#

will.

[Fionn]

The time component is not the problem. When I add a date to the table I use the date picker and if I pick any date after 1/7/2013 it doesn't work. Say for example 1/1/2015 doesn't work but 1/1/2013 does. Unless for some reason a time (what time?) automatically gets added if I choose a date after 1/7/2013 but that doesn't make sense. It's really annoying me at this stage.
Thanks anyways.
Edit. Ok so tried just to solve it with your solution but as it happens while I can find dates above or below x/x/2013 it will not find any records in the year 2013 after July and before December. In fact it finds these records if I try less than 1/7/2013 despite them being greater than that date. And they don't come up when I say greater than 1/7/2013.

[JoeM]

Are you, perchance, using a European version of Access? I have seen lots of issues regarding dates, as there is often a mix-up between European and American date formats (m/d/y versus d/m/y), and what your system is defaulted to.

Also, please post your SQL query.

[Fionn]

Ok my guess is its that european thing I'll check it now. I live in Ireland so yes the dates are in european style dd/mm/yyyy but maybe somewhere the dates are being processed in the American way.
SQL "select StudentID, CourseID from tblAttendance where AttendanceDate = #1/7/2013#"

[Fionn]

Yes it is the American dates thing. It works to find 7/10/2013 with AttendanceDate = #10/7/2013#". Any ideas how to fix this?

[JoeM]

If the issue is with the Criteria (and not your date entry), here is a variation you can try that should work regardless of European/American format:

select StudentID, CourseID from tblAttendance where AttendanceDate = DateSerial(2013,7,1)

[Fionn]

Thanks but ultimately I want to put a date variable in there. Looks to be a common problem though looking at point 2 http://allenbrowne.com/ser-36.html.
Got it working by changing the format of the date before executing the sql.
Datez = Format$(Datez, "\#mm\/dd\/yyyy#")
Messy but will do.
Strange thing is that it only happens for certain dates.

[JoeM]

It stinks that you guys using the European versions have to do that. I am glad that I don't have to jump through those hoops.