How to handle date formats with regional settings

[Vinod]

Hi All,

I've been developing some access databases. I'm storing dates in my local
(Access) database, some tables with dd-mm-yyyy and dd-mmm-yyyy format. When
I'm generating reports with user input dates (using date control) in the form
some times it is working but not some times.

Requirement: Retrieve bookings data from 10-Feb-2009 to 02-Mar-2009.
My dynamic query looks like this Bookings_Date>=10/02/20009 and
Bookings_Date<=02/03/2009.

But the data is not retreiving accordingly. i.e., its treating as
02-Oct-2009 and 03-Feb-2009.

I've faced the above situation when the regional setting was in US format.

In UK regional setting its working well.

If I'd like to create a query to work for all types of regional settings
what needs to be done?

Please share your thoughts and ideas which will be appreciated.

Advanced Thanks
Vinod

 

[Paul Shapiro]

Access is pretty good at interpreting date strings as dates, but it can't
guess for the user. If the computer is set for US regional settings, date
strings default to being interpreted as US-formatted dates. It also sounds
like you're storing dates as character strings instead of using the datetime
data type? You would have better success using datetime, so the underlying
data is independent of the region.

You could suggest the users enter dates using characters for the month, and
Access might do a better job of interpreting their intent. So "March 12,
2009" and "12 March 2009" are both recognized as the same date. You can test
using the CDate(dateString) function.

 

[Douglas J. Steele]

Check what Allen Browne has at http://www.allenbrowne.com/ser-36.html, or
what I had in my September, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Vinod]

Thanks Shaprio for your response.

To answer your question, I'm storing dates in a column which is defined as
date/time data type.

Let me make you clear on the issue which I'm facing.
Table1: (DOB is Date/Time data type and its format is Medium Date)
ID Name DOB(date of birth)
1. Paul 08-Dec-48
2. Smith 19-Feb-52
3. Bush 30-Aug-63
4. Jorge 19-Sep-37
5. Irene 04-Mar-55

The above 5 records are data entried from a user form. Where 'DOB' in data
entry form is locked so user has to provide DOB from date control which is
placed beside of 'DOB' field.

Now I'ld like to gnerate a report from a form where user will select DOB
start date and End date through date control.

On click of generate report button, the query needs to be build based on
given start and endates with following columns (with calculated age as of the
end date)
ID Name DOB(date of birth) Age
1. Paul 08-Dec-48 12
2. Smith 19-Feb-52 08
5. Irene 04-Mar-55 05

I should get above records with calculated age for the following date range
(dates are selected using date contorl i.e., not entered from keyboard):
Start date>=01/10/1945 (01-Jan-1945)
End date<=31/12/1960 (31-Dec-1960)
Age needs to be calculate (End date - DOB) in years

In above scenario I should get same results for different regional settings.

Please try to provide solution for my requirement for dynamic query building.

Advanced Thanks
Vinod

 

[Vinod]

Thanks Douglas for your response.

I've looked at the below links but they are stright away filtered on bound
form. But some times I may need to generate report by exporting to excel with
some calculations on date fields.

Here is one of the example:

Table1: (DOB is Date/Time data type and its format is Medium Date)
ID Name DOB(date of birth)
1. Paul 08-Dec-48
2. Smith 19-Feb-52
3. Bush 30-Aug-63
4. Jorge 19-Sep-37
5. Irene 04-Mar-55

The above 5 records are data entried from a user form. Where 'DOB' in data
entry form is locked so user has to provide DOB from date control which is
placed beside of 'DOB' field.

Now I'ld like to gnerate a report from a form where user will select DOB
start date and End date through date control.

On click of generate report button, the query needs to be build based on
given start and endates with following columns (with calculated age as of the
end date)
ID Name DOB(date of birth) Age
1. Paul 08-Dec-48 12
2. Smith 19-Feb-52 08
5. Irene 04-Mar-55 05

I should get above records with calculated age for the following date range
(dates are selected using date contorl i.e., not entered from keyboard):
Start date>=01/10/1945 (01-Jan-1945)
End date<=31/12/1960 (31-Dec-1960)
Age needs to be calculate (End date - DOB) in years

In above scenario I should get same results for different regional settings.

Please try to provide solution for my requirement for dynamic query building.

Advanced Thanks
Vinod

 

[ken]

Vinod:

When building the string expression for the query the literal dates
should (a) be wrapped in the # date delimiter character and (b) be in
US short date or an otherwise internationally unambiguous date
format, The ISO standard for date notation is YYYY-MM-DD, so this is
a good choice of format. So the string expression would be built
along these lines:

Dim strSQL As String
Dim strWhere as String

strWhere = "DoB BETWEEN #" & _
Format(Forms!YourForm!StartDate,"yyyy-mm-dd") & _
"# AND #" & _
Format(Forms!YourForm!EndDate,"yyyy-mm-dd") & "#"

strSQL = "SELECT ID, [Name], DoB, " & _
"GetAge(Dob) As Age " & _
" FROM YourTable " & _
"WHERE " & strWhere

where GetAge is a function which accepts an argument declared as Date
and returns the current age in years on the basis of the date passed
into it (its slightly more complex than just getting the difference in
years using the DateDiff function). I'm assuming you already have
such a function. If not see:

http://www.mvps.org/access/datetime/date0001.htm

I'm not sure just what you mean by a "date control", a calendar
control perhaps, but whatever it is, it should return either a date/
time value (which is a 64 bit floating point number under the skin) or
a string expression representing the date in the local regional format
or an otherwise internationally unambiguous format such as the ISO
format.

BTW if you have really used Name as a column name, then I'd recommend
you change it to something more explicit such as EmployeeName,
ContactName or similar to avoid confusion with the built in Name
property; I recall a post where someone found that on their report it
was the name of the report returned, not the names of the people for
this reason. If you do use it be sure to wrap it in square brackets
as I've done above.

Ken Sheridan
Stafford, England