By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

A2K - still have a slight date problem...

P: n/a
Hi,

I generate a report using two dates (From and To). I notice if I enter
01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January
rather than 1st October as I intended.

This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report runs I get records earlier than 1st October which is not what I want.

Any ideas on how to allow the use to enter in dd/mm/yyyy format and for that
date not to be misinterpreted by Access??
This is driving me quite potty.

thanks
Martin
Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
TS

"Deano" <ma************@hotmail.com> wrote in message
news:SC*******************@wards.force9.net...
Hi,

I generate a report using two dates (From and To). I notice if I enter
01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January
rather than 1st October as I intended.

This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report runs I get records earlier than 1st October which is not what I want.
Any ideas on how to allow the use to enter in dd/mm/yyyy format and for that date not to be misinterpreted by Access??
This is driving me quite potty.


I recently had the same problem and was informed Access always compares
dates in mm/dd/yyyy format in the VB SQL code.

I think it works with your International settings everywhere else, unless
otherwised formatted.

Hope this helps
Ken
Nov 12 '05 #2

P: n/a
"Deano" <ma************@hotmail.com> wrote in message news:<SC*******************@wards.force9.net>...
Hi,

I generate a report using two dates (From and To). I notice if I enter
01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January
rather than 1st October as I intended.

This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report runs I get records earlier than 1st October which is not what I want.

Any ideas on how to allow the use to enter in dd/mm/yyyy format and for that
date not to be misinterpreted by Access??
This is driving me quite potty.

thanks
Martin


If you get your dates from a form, you can format the date there to be
in English (dd-mm-yyyy) format instead of US format. Either that or
force the format in your query (just wrap the input in the format
function)
Nov 12 '05 #3

P: n/a
In addition to the feedback you've already got (all of which is correct,
BTW), check Allen Browne's "International Dates in Access" at
http://users.bigpond.net.au/abrowne1/ser-36.html
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Deano" <ma************@hotmail.com> wrote in message
news:SC*******************@wards.force9.net...
Hi,

I generate a report using two dates (From and To). I notice if I enter
01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January
rather than 1st October as I intended.

This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report runs I get records earlier than 1st October which is not what I want.
Any ideas on how to allow the use to enter in dd/mm/yyyy format and for that date not to be misinterpreted by Access??
This is driving me quite potty.

thanks
Martin

Nov 12 '05 #4

P: n/a
Douglas J. Steele wrote:
In addition to the feedback you've already got (all of which is
correct, BTW), check Allen Browne's "International Dates in Access" at
http://users.bigpond.net.au/abrowne1/ser-36.html


OK, thanks all, I think i've got it through my thick skull at last. I've
formatted the date parameters within the SQL as mm/dd/yyyy and this now
*appears* to work ok and is returning the results I expect.

Nov 12 '05 #5

P: n/a
ma************@hotmail.com (Deano) wrote in
<ZD*******************@wards.force9.net>:
Douglas J. Steele wrote:
In addition to the feedback you've already got (all of which is
correct, BTW), check Allen Browne's "International Dates in
Access" at http://users.bigpond.net.au/abrowne1/ser-36.html


OK, thanks all, I think i've got it through my thick skull at
last. I've formatted the date parameters within the SQL as
mm/dd/yyyy and this now *appears* to work ok and is returning the
results I expect.


The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #6

P: n/a

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
ma************@hotmail.com (Deano) wrote in
<ZD*******************@wards.force9.net>:
Douglas J. Steele wrote:
In addition to the feedback you've already got (all of which is
correct, BTW), check Allen Browne's "International Dates in
Access" at http://users.bigpond.net.au/abrowne1/ser-36.html


OK, thanks all, I think i've got it through my thick skull at
last. I've formatted the date parameters within the SQL as
mm/dd/yyyy and this now *appears* to work ok and is returning the
results I expect.


The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with dd-mmm-yyyy if your
users don't all speak the same language: unless everything is configured
properly, it may not accept some of the mmm inputs.

The ISO standard yyyy-mm-dd is guaranteed to work, though, or, as you note,
you can use DateSerial.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

Nov 12 '05 #7

P: n/a
Douglas J. Steele wrote:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
ma************@hotmail.com (Deano) wrote in
<ZD*******************@wards.force9.net>:
Douglas J. Steele wrote:
In addition to the feedback you've already got (all of which is
correct, BTW), check Allen Browne's "International Dates in
Access" at http://users.bigpond.net.au/abrowne1/ser-36.html

OK, thanks all, I think i've got it through my thick skull at
last. I've formatted the date parameters within the SQL as
mm/dd/yyyy and this now *appears* to work ok and is returning the
results I expect.


The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with dd-mmm-yyyy if
your users don't all speak the same language: unless everything is
configured properly, it may not accept some of the mmm inputs.

The ISO standard yyyy-mm-dd is guaranteed to work, though, or, as you
note, you can use DateSerial.


I did look at using Dateserial as I felt i could break up the user's date
input and assign to a date variable via Dateserial. However I was running
into problems as it was requiring integers simply weren't working for me. I
was getting overflow errors and the like.
This is still a concern for me so I will revisit DateSerial.
Nov 12 '05 #8

P: n/a
NOSPAM_djsteele@NOSPAM_canada.com (Douglas J. Steele) wrote in
<j2*********************@news02.bloor.is.net.cable .rogers.com>:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78. ..
ma************@hotmail.com (Deano) wrote in
<ZD*******************@wards.force9.net>:
>Douglas J. Steele wrote:
>> In addition to the feedback you've already got (all of which
>> is correct, BTW), check Allen Browne's "International Dates
>> in Access" at
>> http://users.bigpond.net.au/abrowne1/ser-36.html
>
>OK, thanks all, I think i've got it through my thick skull at
>last. I've formatted the date parameters within the SQL as
>mm/dd/yyyy and this now *appears* to work ok and is returning
>the results I expect.
The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with dd-mmm-yyyy
if your users don't all speak the same language: unless everything
is configured properly, it may not accept some of the mmm inputs.


I didn't know that -- I thought it was always an unambiguous
format.

And I don't mean for display here, or for input. Users can use
whatever format they want for display and input, I was only talking
about formatting the date in a SQL string. Are you saying that
Format([value from date field],"dd-mmm-yyyy") might produce
incorrect results?
The ISO standard yyyy-mm-dd is guaranteed to work, though, or, as
you note, you can use DateSerial.


I certainly use DateSerial(), myself, but I've only ever written
one application that was used outside the US.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #9

P: n/a
ma************@hotmail.com (Deano) wrote in
<or*******************@wards.force9.net>:
Douglas J. Steele wrote:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
ma************@hotmail.com (Deano) wrote in
<ZD*******************@wards.force9.net>:

Douglas J. Steele wrote:
> In addition to the feedback you've already got (all of which
> is correct, BTW), check Allen Browne's "International Dates
> in Access" at
> http://users.bigpond.net.au/abrowne1/ser-36.html

OK, thanks all, I think i've got it through my thick skull at
last. I've formatted the date parameters within the SQL as
mm/dd/yyyy and this now *appears* to work ok and is returning
the results I expect.

The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with
dd-mmm-yyyy if your users don't all speak the same language:
unless everything is configured properly, it may not accept some
of the mmm inputs.

The ISO standard yyyy-mm-dd is guaranteed to work, though, or,
as you note, you can use DateSerial.


I did look at using Dateserial as I felt i could break up the
user's date input and assign to a date variable via Dateserial.
However I was running into problems as it was requiring integers
simply weren't working for me. I was getting overflow errors and
the like. This is still a concern for me so I will revisit
DateSerial.


Passing date values with DateSerial requires that parse the input
date (presumably from a control on a form) with the Year(), Month()
and Day() functions. So, if a user types into a control called
fldDate on frmMyForm:

6/2/2003

you'd pass that in SQL as:

DateSerial(Year(Forms!frmMyForm!ldDate), _
Month(Forms!frmMyForm!ldDate), _
Day(Forms!frmMyForm!ldDate)

Now, this assumes that the user is putting in the date in an order
that is in accordance with the user's localized date settings. If I
sat down at a machine that was set for UK dates and thought it was
set for US dates, I could get garbled data.

This is one good reason for choosing unambiguous date formats for
display, not just for processing behind the scenes. But that can
lead to other problems.

Another option would be to use a date picker by itself, or a date
picker in conjunction with a text field. Personally, I'd hate to
have to use a date picker, as I like to type. The combination of
the two gives the user the choice while also giving visual
feedback. This means that if I typed 6/2/2003 meaning June 2 on a
PC that was using UK dates, I'd immediately see the date picker
show February 6, and that would tell me that I'd typed it wrong.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #10

P: n/a
David W. Fenton wrote:
ma************@hotmail.com (Deano) wrote in
<or*******************@wards.force9.net>:
Douglas J. Steele wrote:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
ma************@hotmail.com (Deano) wrote in
<ZD*******************@wards.force9.net>:

> Douglas J. Steele wrote:
>> In addition to the feedback you've already got (all of which
>> is correct, BTW), check Allen Browne's "International Dates
>> in Access" at
>> http://users.bigpond.net.au/abrowne1/ser-36.html
>
> OK, thanks all, I think i've got it through my thick skull at
> last. I've formatted the date parameters within the SQL as
> mm/dd/yyyy and this now *appears* to work ok and is returning
> the results I expect.

The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().

While admittedly picky, you can run into problems with
dd-mmm-yyyy if your users don't all speak the same language:
unless everything is configured properly, it may not accept some
of the mmm inputs.

The ISO standard yyyy-mm-dd is guaranteed to work, though, or,
as you note, you can use DateSerial.


I did look at using Dateserial as I felt i could break up the
user's date input and assign to a date variable via Dateserial.
However I was running into problems as it was requiring integers
simply weren't working for me. I was getting overflow errors and
the like. This is still a concern for me so I will revisit
DateSerial.


Passing date values with DateSerial requires that parse the input
date (presumably from a control on a form) with the Year(), Month()
and Day() functions. So, if a user types into a control called
fldDate on frmMyForm:

6/2/2003

you'd pass that in SQL as:

DateSerial(Year(Forms!frmMyForm!ldDate), _
Month(Forms!frmMyForm!ldDate), _
Day(Forms!frmMyForm!ldDate)

Now, this assumes that the user is putting in the date in an order
that is in accordance with the user's localized date settings. If I
sat down at a machine that was set for UK dates and thought it was
set for US dates, I could get garbled data.

This is one good reason for choosing unambiguous date formats for
display, not just for processing behind the scenes. But that can
lead to other problems.

Another option would be to use a date picker by itself, or a date
picker in conjunction with a text field. Personally, I'd hate to
have to use a date picker, as I like to type. The combination of
the two gives the user the choice while also giving visual
feedback. This means that if I typed 6/2/2003 meaning June 2 on a
PC that was using UK dates, I'd immediately see the date picker
show February 6, and that would tell me that I'd typed it wrong.


Talking about date pickers I notice that if I use my date picker then when
the date is selected or changed then the Afterupdate event of the control
does not fire. It does fire if you type in the date manually.
I'm working on a way of making the event fire if the date picker is used -
if anyone has already cracked this i would be interested...
Nov 12 '05 #11

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with dd-mmm-yyyy
if your users don't all speak the same language: unless everything
is configured properly, it may not accept some of the mmm inputs.


I didn't know that -- I thought it was always an unambiguous
format.

And I don't mean for display here, or for input. Users can use
whatever format they want for display and input, I was only talking
about formatting the date in a SQL string. Are you saying that
Format([value from date field],"dd-mmm-yyyy") might produce
incorrect results?


I don't use that approach (I format as yyyy-mm-dd) so I'm probably being too
pessimistic, but I seem to recall hearing that you can run into
internationalization issues due to the fact that, say, it would be
04-Jul-2003 in English, but 04-Jui-2003 in French. Unfortunately, I don't
remember the details as to what misconfiguration could lead to this causing
problems.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

Nov 12 '05 #12

P: n/a
On Mon, 13 Oct 2003 12:36:56 +0100, "Deano" <ma************@hotmail.com> wrote:


Talking about date pickers I notice that if I use my date picker then when
the date is selected or changed then the Afterupdate event of the control
does not fire. It does fire if you type in the date manually.
I'm working on a way of making the event fire if the date picker is used -
if anyone has already cracked this i would be interested...


Use the On Change event to catch the date picker input.
Wayne Gillespie
Gosford NSW Australia
Nov 12 '05 #13

P: n/a
Wayne Gillespie wrote:
On Mon, 13 Oct 2003 12:36:56 +0100, "Deano"
<ma************@hotmail.com> wrote:


Talking about date pickers I notice that if I use my date picker
then when the date is selected or changed then the Afterupdate event
of the control does not fire. It does fire if you type in the date
manually. I'm working on a way of making the event fire if the date
picker is used - if anyone has already cracked this i would be
interested...


Use the On Change event to catch the date picker input.


Thanks Wayne. I tried that but that event doesn't fire either.

My idea is that i know how to find out what control is being populated by
the datepicker, so I just need somewhere to put a Call
controlname_Afterupdate line. Ideally this should happen in the Close event
but if I do that then it just claims the sub or function is not defined.

Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.