Date issues in different regions (1 Viewer)

[Fantast]

Heys. I have an issue with my VBA tool regarding dates. It is an issue which only popped up after the Access Database was distributed to different countries around the world. The date format is different on each of those regions, causing malfunctioning scripts all around.

In Eastern Europe they use dots as separators, in Western Europe dashes and in the United states slashes. And as if this is not enough, also the order of day and month differ.

The Access database has a datepicker text field where the user can choose the desired date. The date is then compared to dates in the database, after which a query is run and a datasheet shown. To solve this issue I'm considering multiple options, but I hoped that ppl in here had some experience with this issue and could advice me on what solution worked best for them. Two of the solutions I'm thinking about are:
- Using the FORMAT() function in all the queries to ensure the format is the way I want it to be
- Creating a public function which converts dates by the use of Replace() and CDate() and returns them in the right format
I'm a bit affraid that I might not have a full overview of the different date formats and orders out there and thus will have trouble finding a common solution for this issue.

Thanks in advance

 

[spikepl]

1. The issue is not different date formats as such, but that SQL, when fed a string, needs the date string representation to be in the US format, no matter what the local date format is.

2. You may be surprised to know that you are not the first one with Access date issues.

Here is a link: http://allenbrowne.com/ser-36.html

Also a number of posts concerning this are on AWF.
Google is a good search tool.

 

[Fantast]

Thank you spikepl. Your reference helped me a lot with understanding the way dates work in Access and solving the problems. The code seems to work fine now, more testing is scheduled for the coming days.

Cheers
Fantast