IF I NEVER DO A QUERY WITH A DATE AGAIN IT'LL BE TOO SOON!!

[Macca]

Somebody please help me, Arthur, come to my rescue again! Herein lies my plight:

I am using the rs.open (argument list) method to pass a Query to an Access
database and for ages I couldn't figure out what was happening but it seems
that Access is switching around my date formats. In the table I am querying
the date is stored in the UK format (dd/mm/yyyy). Now, if I pass a query
where the WHERE clause requires a date of, for example, 25/07/2003 then it
the query retrieves a recordset with the appropriate information in it. However,
if I pass a query where the WHERE clause requires a date of, for example,
06/08/2002 then, even though there are matching records for that date, the
recordset is empty.

To try and diagnose the problem I pasted my SQL String direct from VB into
the SQL view whilst designing a query in access. The Query appears as follows:
"SELECT * FROM tbl_data WHERE Date = #06/08/2002#". When I then change over
to have a look at the design view of the query Access lists the criteria
for date as 08/06/2002 (in other words, Access converts the date I have passed
it in the query to US format (MM/DD/YYYY). When I tried that with a date
like 25/02/2002, Access didn't convert it at all.

Anyone? (Answers preferably not on a postcard!)

[Arthur Wood]

Macca,
What is the Regional Settings (Control Panel) set to for both Locale and
then check the Date format. I just changed mine (in the US) to English(UK)
and the Date format correctly changes to "DD/MM/YYYY", and Access then correctly
changes 5/31/02 to #31/05/2002#, but DOES NOT change "06/08/2002", or "08/06/2002",
as BOTH of those "dates" are in fact perfectly valid, so Access accepts what
it is given. It will only change the format id the date PROVIDED is NOT valis
in the Regional Format, but if switched, would be valid --> Access will then
switch to the apparently Valid formatting.


Arthur Wood

"Macca" <Tim@agcl.net> wrote:
>
>Somebody please help me, Arthur, come to my rescue again! Herein lies my
plight:
>
>I am using the rs.open (argument list) method to pass a Query to an Access
>database and for ages I couldn't figure out what was happening but it seems
>that Access is switching around my date formats. In the table I am querying
>the date is stored in the UK format (dd/mm/yyyy). Now, if I pass a query
>where the WHERE clause requires a date of, for example, 25/07/2003 then
it
>the query retrieves a recordset with the appropriate information in it.
However,
>if I pass a query where the WHERE clause requires a date of, for example,
>06/08/2002 then, even though there are matching records for that date, the
>recordset is empty.
>
>To try and diagnose the problem I pasted my SQL String direct from VB into
>the SQL view whilst designing a query in access. The Query appears as follows:
>"SELECT * FROM tbl_data WHERE Date = #06/08/2002#". When I then change over
>to have a look at the design view of the query Access lists the criteria
>for date as 08/06/2002 (in other words, Access converts the date I have
passed
>it in the query to US format (MM/DD/YYYY). When I tried that with a date
>like 25/02/2002, Access didn't convert it at all.
>
>Anyone? (Answers preferably not on a postcard!)
>

[Steve]

What I recommend is to leave date formatting up to the application, and when
communicating with the database, use only ISO date format. yyyy-mm-dd. This
way, regional settings won't confuse Access, and both VB and Access understand
this format as a valid (and correct date.

Steve.

"Macca" <Tim@agcl.net> wrote:
>
>Somebody please help me, Arthur, come to my rescue again! Herein lies my
plight:
>
>I am using the rs.open (argument list) method to pass a Query to an Access
>database and for ages I couldn't figure out what was happening but it seems
>that Access is switching around my date formats. In the table I am querying
>the date is stored in the UK format (dd/mm/yyyy). Now, if I pass a query
>where the WHERE clause requires a date of, for example, 25/07/2003 then
it
>the query retrieves a recordset with the appropriate information in it.
However,
>if I pass a query where the WHERE clause requires a date of, for example,
>06/08/2002 then, even though there are matching records for that date, the
>recordset is empty.
>
>To try and diagnose the problem I pasted my SQL String direct from VB into
>the SQL view whilst designing a query in access. The Query appears as follows:
>"SELECT * FROM tbl_data WHERE Date = #06/08/2002#". When I then change over
>to have a look at the design view of the query Access lists the criteria
>for date as 08/06/2002 (in other words, Access converts the date I have
passed
>it in the query to US format (MM/DD/YYYY). When I tried that with a date
>like 25/02/2002, Access didn't convert it at all.
>
>Anyone? (Answers preferably not on a postcard!)
>

Arthur,

Thanks for the idea. I suspected it was my international settings but I tried
that and it still didn't work. My international settings are still set to
UK and the correct date format is in place but Access is still playing games,
heaven knows why. My next step is going to be to try and impose an imput
mask and see if that works. If you have any other ideas in the meantime,
let me know.

In SQL view the query is:

SELECT tblApplications.*
FROM tblApplications
WHERE (((tblApplications.Date)=#06/08/2002#));

And yet, when I switch to design view, the criteria field for Date says

#08/06/2002#


Confused&*!?

[Macca]

Steve, thanks for the tip, mate ( I'd never heard of ISO format before). I
will have to go that route if I don't figure this one out! I suppose it would
mean that everything that is input on my client application would have to
be converted to ISO in code before it is passed to the DB. You don't happen
to have a handy function that will do that conversion do you? (I know, it's
not too tough but I'm a lazy bugger if I can get away with it!)

Cheers,

Macca

[Steve]

You can use Format$() to convert any date value to ISO.
sISODate = Format$(sDateString, "yyyy-mm-dd") It works with either "-" or
"/" characters. Whatever's your preference.

You may still run into issues if you let a user text enter a date value in
something like a text box, deciding if they type something like "06/07/2002"
whether they mean June 7th or July 6th, but it at least takes the variable
out of the data communication. Typically I transmit dates between layers
using the ISO format, then format the date for presentation using the system
settings.

And even if Access or another RDBMS gets finicky with the date format, ISOs
can be saved and sorted properly in strings as well.

Steve.

"Macca" <tim@agcl.net> wrote:
>
>Steve, thanks for the tip, mate ( I'd never heard of ISO format before).
I
>will have to go that route if I don't figure this one out! I suppose it
would
>mean that everything that is input on my client application would have to
>be converted to ISO in code before it is passed to the DB. You don't happen
>to have a handy function that will do that conversion do you? (I know, it's
>not too tough but I'm a lazy bugger if I can get away with it!)
>
>Cheers,
>
>Macca

[Joe \Nuke Me Xemu\ Foster]

"Macca" <Tim@agcl.net> wrote in message <news:3d525bd4$1@10.1.10.29>...

> Somebody please help me, Arthur, come to my rescue again! Herein lies my plight:
>
> I am using the rs.open (argument list) method to pass a Query to an Access
> database and for ages I couldn't figure out what was happening but it seems
> that Access is switching around my date formats. In the table I am querying
> the date is stored in the UK format (dd/mm/yyyy).

No, it isn't. It's stored as a count of seconds since midnight
30/12/1899, divided by (24 * 60 * 60). Or not, in .NET... =)

> Now, if I pass a query
> where the WHERE clause requires a date of, for example, 25/07/2003 then it
> the query retrieves a recordset with the appropriate information in it. However,
> if I pass a query where the WHERE clause requires a date of, for example,
> 06/08/2002 then, even though there are matching records for that date, the
> recordset is empty.

DAO and, it seems, ADO will try to interpret date/time strings in
a Merkin format if they can possibly find absolutely any excuse
whatsoever to do so, no matter how flimsy. A locale-independent
way to defeat this is to use a yyyy-mm-dd or similar format:

Const JetDateTimeFmt = "\#yyyy\-mm\-dd hh\:nn\:ss\#;;;""NULL"""
Const JetDateFmt = "\#yyyy\-mm\-dd\#;;;""NULL"""
Const JetTimeFmt = "\#hh\:nn\:ss\#;;;""NULL"""

SQL = SQL & "and mytable.when >= " & Format$(Now, JetDateTimeFmt)

You might also be able to play games with conversion functions...

URL:http://groups.google.com/groups?selm...ews.remarQ.com

--
Joe Foster <mailto:jlfoster%40znet.com> Space Cooties! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!

[MarkN]

>No, it isn't. It's stored as a count of seconds since midnight
>30/12/1899, divided by (24 * 60 * 60). Or not, in .NET... =)

This is very important to keep in mind otherwise it will be come the source
of confusion (again) went crossing datelines and/or timezone settings or
date/times being messed up/different. Files exhibit this behavior too.

[macca]

I just want to say that you all make me so proud it brings a tear to my eye
and a frog to my throat! I combined all the best of what you had told me
and ended up using the split function to put the User input date into an
array and then passed the date to the Jet engine in ISO format. The date
still appears in Access in UK format (Yes, I know, seconds from midnight
1899, etc!) but I don't experience the same Jet engine conversion problems.

Cheerz guys!