Date Conversions

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

I have a table of client records. In the table I have a
field, telling me when the client first contacted me.
The date format is set to Medium Date: dd-mmm-yy

I have another form to set search parameters to find
clients. One of the paramameters is the date the client
first registered (so that I can archive clients who have
been on the database from more than 2 years for example,
but have been inactive.)

The text box used to input the date parameter is
formatted to Medium date: dd-mmm-yy

The code behind the form carries this value into the
search string as follows:

strWhereJGClient = strWhereJGClient & " And
[InitialContactOn] >= " & Chr$(35) & Me.txtDateCriteria &
Chr$(35)

Some examples of what this returns are:

If I search for records with the date parameter being 08-
Jan-03, the month and the day get switched. All records
created after 01-Aug-03 are returned.

But, if I use, 31-Jan-03 as the date parameter, records
are returned correctly, that is all after or on 31-Jan-03

My regional seetings on my computer are set to the
correct date format (UK). Clearly in the first instance
the code assume US format and inverts the day and the
month. In the second instance I would expect an error,
as it cannot make sense of the date 31-Jan-03 in the US
format by inverting the day and month.

Anyway, what is the easiest way to make sure this all
works perferctly in the UK format, please, and that dates
fomratted in forms and tables in the same UK format are
treated as such when passed through code?
 
What you're describing is exactly what Access does when the dates are in
dd/mm/yyyy (i.e.: the day and month are inverted when the day is less than
or equal to 12, but left alone when the day is greater than 12), but it
should work if the value in txtDateCriteria is in dd-mmm-yyyy format, as you
say it is.

Unfortunately, Access does not respect the user's setting for date format:
you're supposed to use mm/dd/yyyy format (although any unambiguous format,
such as dd-mmm-yyyy or yyyy-mm-dd is supposed to work as well).

One possibility is to use the CDate function, which does respect the user's
settings:

strWhereJGClient = strWhereJGClient & " And
[InitialContactOn] >= CDate(" & Chr$(39) & Me.txtDateCriteria & Chr$(39) &
")"

You might find Allen Browne's "International Dates in Access" at
http://users.bigpond.net.au/abrowne1/ser-36.html useful reading as well.
 
Dear Doug

Thanks for your advise. I now have it working!

All the best Simon
-----Original Message-----
What you're describing is exactly what Access does when the dates are in
dd/mm/yyyy (i.e.: the day and month are inverted when the day is less than
or equal to 12, but left alone when the day is greater than 12), but it
should work if the value in txtDateCriteria is in dd-mmm- yyyy format, as you
say it is.

Unfortunately, Access does not respect the user's setting for date format:
you're supposed to use mm/dd/yyyy format (although any unambiguous format,
such as dd-mmm-yyyy or yyyy-mm-dd is supposed to work as well).

One possibility is to use the CDate function, which does respect the user's
settings:

strWhereJGClient = strWhereJGClient & " And
[InitialContactOn] >= CDate(" & Chr$(39) &
Click to expand...
Me.txtDateCriteria & Chr$(39) &
")"

You might find Allen Browne's "International Dates in Access" at
http://users.bigpond.net.au/abrowne1/ser-36.html useful reading as well.

--
Doug Steele, Microsoft Access MVP



I have a table of client records. In the table I have a
field, telling me when the client first contacted me.
The date format is set to Medium Date: dd-mmm-yy

I have another form to set search parameters to find
clients. One of the paramameters is the date the client
first registered (so that I can archive clients who have
been on the database from more than 2 years for example,
but have been inactive.)

The text box used to input the date parameter is
formatted to Medium date: dd-mmm-yy

The code behind the form carries this value into the
search string as follows:

strWhereJGClient = strWhereJGClient & " And
[InitialContactOn] >= " & Chr$(35) & Me.txtDateCriteria &
Chr$(35)

Some examples of what this returns are:

If I search for records with the date parameter being 08-
Jan-03, the month and the day get switched. All records
created after 01-Aug-03 are returned.

But, if I use, 31-Jan-03 as the date parameter, records
are returned correctly, that is all after or on 31-Jan- 03

My regional seetings on my computer are set to the
correct date format (UK). Clearly in the first instance
the code assume US format and inverts the day and the
month. In the second instance I would expect an error,
as it cannot make sense of the date 31-Jan-03 in the US
format by inverting the day and month.

Anyway, what is the easiest way to make sure this all
works perferctly in the UK format, please, and that dates
fomratted in forms and tables in the same UK format are
treated as such when passed through code?
Click to expand...


.
Click to expand...
 
Back
Top