I have a query to find records with a certain date here:
select *
from data
where start_date = 7/12/2014
This returns one empty record.
The start_date field is of format "Date/Time" as seen in Design view. When in Table view, each date in that field is in the mm/dd/yyyy format.
I manually converted the date to days since 1900, as is used in Excel, and the query worked and returned what I needed, but I have many dates and don't want to do this for each one. I also tried things such as '7/12/2014' etc but to no avail.
Is there a way to be able to search with the current format (mm/dd/yyyy)?
Not sure if this has any effect, but when I populated the start_date field, all the dates were in days since 1900 format, and the data type auto-set to Number. I changed the data type to Date/Time in design view and when I went back in Table view, the values were in mm/dd/yyyy.
You need to enclose dates in # signs in Access SQL.
This should work:
select *
from data
where start_date = #7/12/2014#