A2K - still have a slight date problem...

[Deano]

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

[TS]

Re: A2K - still have a slight date problem...


"Deano" <martinday66999 @hotmail.com> wrote in message
news:SCFhb.6297 $kA.1930743@war ds.force9.net.. .[color=blue]
> 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[/color]
want.[color=blue]
>
> Any ideas on how to allow the use to enter in dd/mm/yyyy format and for[/color]
that[color=blue]
> date not to be misinterpreted by Access??
> This is driving me quite potty.
>[/color]

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

[Pieter Linden]

Re: A2K - still have a slight date problem...

"Deano" <martinday66999 @hotmail.com> wrote in message news:<SCFhb.629 7$kA.1930743@wa rds.force9.net> ...[color=blue]
> 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[/color]

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)

[Douglas J. Steele]

Re: A2K - still have a slight date problem...

In addition to the feedback you've already got (all of which is correct,
BTW), check Allen Browne's "Internatio nal 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" <martinday66999 @hotmail.com> wrote in message
news:SCFhb.6297 $kA.1930743@war ds.force9.net.. .[color=blue]
> 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[/color]
want.[color=blue]
>
> Any ideas on how to allow the use to enter in dd/mm/yyyy format and for[/color]
that[color=blue]
> date not to be misinterpreted by Access??
> This is driving me quite potty.
>
> thanks
> Martin
>
>[/color]

[Deano]

Re: A2K - still have a slight date problem...

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

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.

[David W. Fenton]

Re: A2K - still have a slight date problem...

martinday66999@ hotmail.com (Deano) wrote in
<ZDUhb.6556$kA. 2026913@wards.f orce9.net>:
[color=blue]
>Douglas J. Steele wrote:[color=green]
>> In addition to the feedback you've already got (all of which is
>> correct, BTW), check Allen Browne's "Internatio nal Dates in
>> Access" at http://users.bigpond.net.au/abrowne1/ser-36.html[/color]
>
>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.[/color]

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

[Douglas J. Steele]

Re: A2K - still have a slight date problem...


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:9411AE41Ed fentonbwaynetin vali@24.168.128 .78...[color=blue]
> martinday66999@ hotmail.com (Deano) wrote in
> <ZDUhb.6556$kA. 2026913@wards.f orce9.net>:
>[color=green]
> >Douglas J. Steele wrote:[color=darkred]
> >> In addition to the feedback you've already got (all of which is
> >> correct, BTW), check Allen Browne's "Internatio nal Dates in
> >> Access" at http://users.bigpond.net.au/abrowne1/ser-36.html[/color]
> >
> >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.[/color]
>
> The only real solution, though, is to use a non-ambiguous date
> format, such as dd-mmm-yyyy or passing your dates with
> DateSerial().[/color]

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)

[Deano]

Re: A2K - still have a slight date problem...

Douglas J. Steele wrote:[color=blue]
> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
> news:9411AE41Ed fentonbwaynetin vali@24.168.128 .78...[color=green]
>> martinday66999@ hotmail.com (Deano) wrote in
>> <ZDUhb.6556$kA. 2026913@wards.f orce9.net>:
>>[color=darkred]
>>> Douglas J. Steele wrote:
>>>> In addition to the feedback you've already got (all of which is
>>>> correct, BTW), check Allen Browne's "Internatio nal 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.[/color]
>>
>> The only real solution, though, is to use a non-ambiguous date
>> format, such as dd-mmm-yyyy or passing your dates with
>> DateSerial().[/color]
>
> 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.[/color]

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.

[David W. Fenton]

Re: A2K - still have a slight date problem...

NOSPAM_djsteele @NOSPAM_canada. com (Douglas J. Steele) wrote in
<j21ib.180743$3 r1.152197@news0 2.bloor.is.net. cable.rogers.co m>:
[color=blue]
>"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>news:9411AE41E dfentonbwayneti nvali@24.168.12 8.78...[color=green]
>> martinday66999@ hotmail.com (Deano) wrote in
>> <ZDUhb.6556$kA. 2026913@wards.f orce9.net>:
>>[color=darkred]
>> >Douglas J. Steele wrote:
>> >> In addition to the feedback you've already got (all of which
>> >> is correct, BTW), check Allen Browne's "Internatio nal 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.[/color]
>>
>> The only real solution, though, is to use a non-ambiguous date
>> format, such as dd-mmm-yyyy or passing your dates with
>> DateSerial().[/color]
>
>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.[/color]

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?
[color=blue]
>The ISO standard yyyy-mm-dd is guaranteed to work, though, or, as
>you note, you can use DateSerial.[/color]

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

[David W. Fenton]

Re: A2K - still have a slight date problem...

martinday66999@ hotmail.com (Deano) wrote in
<or2ib.9400$kA. 2108489@wards.f orce9.net>:
[color=blue]
>Douglas J. Steele wrote:[color=green]
>> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>> news:9411AE41Ed fentonbwaynetin vali@24.168.128 .78...[color=darkred]
>>> martinday66999@ hotmail.com (Deano) wrote in
>>> <ZDUhb.6556$kA. 2026913@wards.f orce9.net>:
>>>
>>>> Douglas J. Steele wrote:
>>>>> In addition to the feedback you've already got (all of which
>>>>> is correct, BTW), check Allen Browne's "Internatio nal 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().[/color]
>>
>> 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.[/color]
>
>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.[/color]

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!frmMyFor m!ldDate), _
Month(Forms!frm MyForm!ldDate), _
Day(Forms!frmMy Form!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

[Deano]

Re: A2K - still have a slight date problem...

David W. Fenton wrote:[color=blue]
> martinday66999@ hotmail.com (Deano) wrote in
> <or2ib.9400$kA. 2108489@wards.f orce9.net>:
>[color=green]
>> Douglas J. Steele wrote:[color=darkred]
>>> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>>> news:9411AE41Ed fentonbwaynetin vali@24.168.128 .78...
>>>> martinday66999@ hotmail.com (Deano) wrote in
>>>> <ZDUhb.6556$kA. 2026913@wards.f orce9.net>:
>>>>
>>>>> Douglas J. Steele wrote:
>>>>>> In addition to the feedback you've already got (all of which
>>>>>> is correct, BTW), check Allen Browne's "Internatio nal 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.[/color]
>>
>> 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.[/color]
>
> 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!frmMyFor m!ldDate), _
> Month(Forms!frm MyForm!ldDate), _
> Day(Forms!frmMy Form!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.[/color]

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...

[Douglas J. Steele]

Re: A2K - still have a slight date problem...

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:94128EEEAd fentonbwaynetin vali@24.168.128 .78...[color=blue][color=green][color=darkred]
> >> The only real solution, though, is to use a non-ambiguous date
> >> format, such as dd-mmm-yyyy or passing your dates with
> >> DateSerial().[/color]
> >
> >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.[/color]
>
> 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?[/color]

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
internationaliz ation 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 misconfiguratio n could lead to this causing
problems.

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(No private e-mails, please)

[Wayne Gillespie]

Re: A2K - still have a slight date problem...

On Mon, 13 Oct 2003 12:36:56 +0100, "Deano" <martinday66999 @hotmail.com> wrote:

[color=blue]
>
>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...
>[/color]

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


Wayne Gillespie
Gosford NSW Australia

[Deano]

Re: A2K - still have a slight date problem...

Wayne Gillespie wrote:[color=blue]
> On Mon, 13 Oct 2003 12:36:56 +0100, "Deano"
> <martinday66999 @hotmail.com> wrote:
>
>[color=green]
>>
>> 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...
>>[/color]
>
> Use the On Change event to catch the date picker input.[/color]

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_Aft erupdate 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.