Working with dd/mm/yyyy

[aytee111]

I have a continuous form with dates in the European format (I use USA). When the user tries to filter on these dates - right-click, filter between - he gets an invalid date error. The data is stored with date and time and is displayed with short date format.



I have tried everything!

[June7]

And what is 'everything'? Can Allen help > http://allenbrowne.com/ser-36.html

[aytee111]

Thanks for that. It doesn't seem to answer the issue, however. This is a straight display of the date, when you try and filter it a calendar popup is displayed, there is no intervention. Why can you not pick "today" for instance, or any day you want, and Access must recognize it based on your regional settings?

[June7]

Since I don't have to deal with non-US dates, hard for me to advise. I know that whenever I download db with non-US dates, drives me nuts trying to work with.

I thought Allen's tutorial is supposed to handle the translation between user entries of 4/7/2017 as DD/mm/yyyyy and storing as 7/4/2017 MM/DD/YYYY. Otherwise, I am lost on the issue.

[aytee111]

Is that the issue, that I am supposed to handle it? Rewrite the calendar popup that Access provides? There must be a simpler way! Thanks for trying, maybe one of our helpers from across the pond has the answer.

[orange]

aytee111,
You may get more info from this recent post.

[June7]

And Allen is in Australia where international date format is the convention.

[aytee111]

Thanks, Orange - but no VBA code involved here.

[ArviLaanemets]

Always works DateSerial()

Like

DateSerial(Year(YourDate),Month(YourDate),Day(YourDate))

Another way independent of regional settings is to handle dates as long integers (or Double, when time part is included) in formulas/queries.

[Minty]

Access will always display a date based on the users local settings. In the UK that means dd/mm/yyyy .
As long as your dates are stored as DateTime fields and you aren't trying to use VBA to manipulate them you don't need to worry about the formatting.

I'm intrigued that you get a date picker when you right click to filter though - I've not seen that behaviour ? I'm using Access 2010, and I get a list of possible conditions e.g this year, this week etc.
The only time the date picker appears is when I'm doing data entry.

If the control is locked - picking today from the date picker won't do diddly, it certainly won't filter.

[aytee111]

Gone to other forum, will post back if I find a solution.

[aytee111]

For anyone interested in this, I was directed to another thread which is still unresolved. Looks like it is an Access bug, altho it was around in 2010 if not before.
https://access-programmers.co.uk/for...d.php?t=255995