Unable to convert text into date with different system language settings in ms access (1 Viewer)

nandini

New member
Local time
Today, 11:27
Joined
Jun 6, 2013
Messages
3
I have a table in access database which contains a text field 'EDate' that stores Date value in format (12-Apr-2013). Now I want to run a sql query on that field. User will give an input date. The sql query needs to fetch me all the records from access database whose Edate is less than or equal to the user input date.

I am using DateValue function to convert my text filed Edate into date. My query is something like this:

select * from table_name where DateValue(EDate)<='user_input_date'

I am able to perform above task if the system language settings are 'English'. But if system language settings are different say Turkish, then the query fails.

I searched a lot on web and found that DateTime function compares test data with the system date time format and gives the result. Thus it fails with different language settings.

Can anyone suggest the possible solution.
 

pr2-eugin

Super Moderator
Local time
Today, 05:57
Joined
Nov 30, 2011
Messages
8,494
Hello nandini, Welcome to AWF.. :)

The simple and easy solution will be to ask your clients to change their system settings to English.. As this might not be the only function that will use the system information.. There might be several other Queries using the System date.. So moving to a more standard method is advisable.. Or you should start coding in the language your client uses..

The long winded process.. Create custom functions that will translate the date and time... Is the User Input Date going to be a standard format? What does DateValue("12-APR-2013") in the immediate window return?
 

Brianwarnock

Retired
Local time
Today, 05:57
Joined
Jun 2, 2003
Messages
12,701
The question that needs answering is why are you storing a date as text rather than as a date, and thus as a Double data type, thus making it more portable.

Brian
 

nandini

New member
Local time
Today, 11:27
Joined
Jun 6, 2013
Messages
3
Hello nandini, Welcome to AWF.. :)

The simple and easy solution will be to ask your clients to change their system settings to English.. As this might not be the only function that will use the system information.. There might be several other Queries using the System date.. So moving to a more standard method is advisable.. Or you should start coding in the language your client uses..

The long winded process.. Create custom functions that will translate the date and time... Is the User Input Date going to be a standard format? What does DateValue("12-APR-2013") in the immediate window return?



Thanks for the reply....

The application will be used in different languages. The user input date will be in the standard format 12-Apr-2013.

If the language settings are other than English, DateValue function gives an error message else it provides me Date in the format I need.

I don't want to ask all clients to change their system settings to English. Is their any other possible way to accomplish the task?
 

Users who are viewing this thread

Top Bottom