Varying date formats

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

Access97.

I have the following date criteria set as part of a DCount expression for a
field on a form, and also (seperately) in a query:
#31/03/2003# And <#1/04/2004#
Click to expand...

but I had noticed that the number of records returned was not as expected.
Finally I have realised that the records returned were those where the date
field was between 31 March 2003 and 4 January 2004!
i.e. the first, minimum date criteria was being treated as if in the format
dd/mm/yyyy, but the second, maximum date criteria was being treated as if in
the format mm/dd/yyyy.
How can that be: is it a bug?
In Regional settings the format is specified as dd/mm/yyyy.

Hope someone can help.

Many thanks
Leslie Isaacs
 
Leslie Isaacs said:
Hello All

Access97.

I have the following date criteria set as part of a DCount expression for a
field on a form, and also (seperately) in a query:


but I had noticed that the number of records returned was not as expected.
Finally I have realised that the records returned were those where the date
field was between 31 March 2003 and 4 January 2004!
i.e. the first, minimum date criteria was being treated as if in the format
dd/mm/yyyy, but the second, maximum date criteria was being treated as if in
the format mm/dd/yyyy.
How can that be: is it a bug?
In Regional settings the format is specified as dd/mm/yyyy.

Hope someone can help.

Many thanks
Leslie Isaacs
Click to expand...

It's more of a shortcoming than a bug. Regardless of regional settings,
whenever you use a date literal in an SQL criterion it must be in US format
i.e. mm/dd/yyyy, although Access has figured it out for 31/03/2003 because,
in US format, it's not a valid date.
 
Greetings, Leslie!

If you are using a date range for criteria, remember you can also use the
Between #date# And #date# expression.

The expression you provided appears to be looking for dates after March 31st
'03 and before April 1st '04. If you meant to include those end-points,
using Between ... And ... can do that (as well as the >=, <= version).

And if you are using date criteria against a date/time field (e.g., set with
Now(), not Date()), remember that all the minutes/seconds starting the first
second after midnight, March 31st '03 will be found by your use of
March31st'03.
Click to expand...

Regards

Jeff Boyce
<Access MVP>
 
Brian

Thanks for your reply.

I take it then that I have to enter 4/1/03 for 1 April 2003?

What about if a user is to enter a parameter date value? I would want them
to be able to enter dd/mm/yyyy format: do I have to do some nifty
Mid-function stuff to alter the format of what they enter- surely (&
hopefully!) not?

Thanks for the help.
Les
 
Hello Jeff

Thnaks for your reply.

My problem is that I wanted April 1st 04 as my latest date criteria, but
entering 1/4/04 was interpretted as 4th January 04 - despite my having
specified dd/mm/yyyy in regional settings. It seems that this is Bill Gate's
insistance on US formatting!!

Thanks.
Les (a Brit!).
 
Leslie said:
Hello Jeff

Thnaks for your reply.

My problem is that I wanted April 1st 04 as my latest date criteria,
but entering 1/4/04 was interpretted as 4th January 04 - despite my
having specified dd/mm/yyyy in regional settings. It seems that this
is Bill Gate's insistance on US formatting!!
Click to expand...

They had to pick "some" format. Otherwise a date value hard-coded as
#1/4/04# would be interprettted differently from one PC to another depending
on the regioanl settings. Given the company's roots one can hardly blame
them for choosing the US format.
 
Leslie Isaacs said:
Brian

Thanks for your reply.

I take it then that I have to enter 4/1/03 for 1 April 2003?

What about if a user is to enter a parameter date value? I would want them
to be able to enter dd/mm/yyyy format: do I have to do some nifty
Mid-function stuff to alter the format of what they enter- surely (&
hopefully!) not?

Thanks for the help.
Les
Click to expand...

No indeed, it's only when you, as the programmer, hardcode an actual date
literal into an SQL criterion that the issue arises. In all other
circumstances (AFAIK) Access will respect your regional settings.
Incidentally, if/when you need to write code that builds SQL containing a
data literal, the Format function can be used to render your dates as
strings in US format.
 
Generally, there is no problem if the user enters the date in response to
the parameter value request in the format dd/mm/yyyy as set in the Regional
Settings. In this case Access will interpret / type-cast the date according
to the Regional Settings. If you want to use explicit conversion, you can
use:

CDate([DateParameter])

since the CDate() follows the Regional Settings. I normally use CDate() just
to be sure.

The problem only occurs when you use explicit (#-delimited) date value.
 
Rick Brandt said:
They had to pick "some" format. Otherwise a date value hard-coded as
#1/4/04# would be interprettted differently from one PC to another depending
on the regioanl settings. Given the company's roots one can hardly blame
them for choosing the US format.
Click to expand...

Although, of course, the US format makes no sense. It's like having a time
format of mm:hh:ss!

Lights the blue touch paper and stands well back...

;-)
 
In the interest of internationalism (and sortability), I vote for
"yyyymmdd". Of course, "regional" settings would be, in an ideal world,
"transparently" converted back and forth to such a "universal" setting by
the OS.

So even if Leslie entered #01/04/04#, if his regional said "Brit", the
OS/application would convert appropriately.

Or have I overlooked something blatant?!

Jeff Boyce
<Access MVP>
 
Jeff said:
In the interest of internationalism (and sortability), I vote for
"yyyymmdd". Of course, "regional" settings would be, in an ideal
world, "transparently" converted back and forth to such a "universal"
setting by the OS.

So even if Leslie entered #01/04/04#, if his regional said "Brit", the
OS/application would convert appropriately.

Or have I overlooked something blatant?!

Jeff Boyce
<Access MVP>
Click to expand...

It's only an issue in a self prompting parameter query which I never use
anyway. If you have the user specify dates on a form then you can display
"whatever" he enters in a completely non-ambiguous format so that there is
never any question.
 
Jeff Boyce said:
In the interest of internationalism (and sortability), I vote for
"yyyymmdd". Of course, "regional" settings would be, in an ideal world,
"transparently" converted back and forth to such a "universal" setting by
the OS.

So even if Leslie entered #01/04/04#, if his regional said "Brit", the
OS/application would convert appropriately.

Or have I overlooked something blatant?!

Jeff Boyce
<Access MVP>
Click to expand...

I agree on your vote for yyyymmdd, but I do think you have missed something
blatant. What if you are selling this app worldwide, and you sold it to a
guy in the UK, and a guy in the US? One guy would get 1st April, the other
would get 4th January. The only way around that would be to change the
syntax of Access SQL to allow some means of specifying the format whenever
you used a date literal.
 
It is rather confusing for people who use Regional Settings "dd/mm/yyyy"
(including my). AFAIK (not sure whether it applies to all versions of
Access):

1. If "#01/04/2005#" is entered in the Query grid, it will be interpreted
as 01/Apr/2005.

2. If the same "#01/04/2005#" is entered in the SQL View, it will be
interpreted as 04/Jan/2005.

This quirky behaviour can be seen when switching between QBE & SQL views
(with Regional Settings "dd/mm/yyyy", of course)..

3. If "01/04/2005" is entered in Parameter prompt, it will be interpreted
per Regional Settings, i.e. 01/Apr/2005. The Parameter may need to be
declared as datetime. It seems that the automatic type-casting uses
(something similar to) CDate() which observes the Regional Settings.

4. Personally, I don't use Parameter prompt for datetime but if I do, I
wrap the Parameter in CDate() to make sure, e.g.:

....
WHERE T1.DateField <= CDate([Enter End Date:])
 
Brian

I'm not sure I follow...
I agree on your vote for yyyymmdd, but I do think you have missed something
blatant. What if you are selling this app worldwide, and you sold it to a
guy in the UK, and a guy in the US? One guy would get 1st April, the other
would get 4th January. The only way around that would be to change the
syntax of Access SQL to allow some means of specifying the format whenever
you used a date literal.
Click to expand...
If the date was stored as yyyymmdd, UK would see it displayed in UK format,
but US would see it displayed in US format. The confusion about whether
4/1/.... was april or january wouldn't exist.

Or did I still miss something?

Jeff
 
Jeff Boyce said:
Brian

I'm not sure I follow...

If the date was stored as yyyymmdd, UK would see it displayed in UK format,
but US would see it displayed in US format. The confusion about whether
4/1/.... was april or january wouldn't exist.

Or did I still miss something?

Jeff
Click to expand...

No, I missed something. I didn't realise that your comment about regional
settings was a corollary to your vote for yyyymmdd, I thought you were
talking about the way it currently works.

Having cleared that up, I agree with you. Unfortunately, however, I suspect
that our votes for yyyymmdd will have no discernible influence upon
Microsoft!
 
Back
Top