Querying date

[ocm]

Greetings,

I’ve a date field STARTDT that I tried to create a query to get all records with STARTDT of 2017 & 2018. I learned that users are entering dates in various format for STARTDT:

4/25/17
04/25/2017
October 1, 2017
10/1/17


7/16/18
07/16/2018

For my query, I used the Following syntax which of course didn’t give me what I want:

>=#1/1/2017# And <=#12/31/2018#

The fact that there were about 50 records for STARTDT 2017/2018, I exported the query to excel and manually filtered those records I needed.
1. Is there a workaround that I can use
2. Going forward, how can I force data entry to a certain format?

TIA

Regards,

[pbaldy]

It sounds like the data type of the field is text rather than date/time? If you want to query it like a date, it should have the date/time data type. In that event, your query would/should have worked.

[ranman256]

it the field is DATE/TIME then it will accept any date format.

[ocm]

Thanks,

Correct, I checked the table, and the data type for this field is Short Date. I changed it to Date/Time.

Regards,

[pbaldy]

Short date is a format, not a data type (unless it's something new). If you changed the data type to date/time, does your query work now? If not, can you attach the db here?

[Micron]

ocm, do you have screen magnification set to something much larger than normal? That would explain why I, at 100% view, see your posts as tiny text. It's as if you're formatting your html font to be about size 1 which might look normal to you if your screen magnification is at 150% or something like that.

Going forward, how can I force data entry to a certain format?

Best to make sure date field isn't text type. If users have different regional settings, might require more than that to see the results you want.

[ocm]

Pbaldy,
Yes, the query works after changing the data type to date/time.

Micron,
Sorry, it’s very likely that I’d my screen magnification high on my work monitor when I posted in the forum.

Thank you everyone for your input, I was able to get the query.

Regards,