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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

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

  1. #1
    Macca Guest

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


    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!)


  2. #2
    Arthur Wood Guest

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


    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!)
    >



  3. #3
    Steve Guest

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


    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!)
    >



  4. #4
    Guest

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


    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&*!?


  5. #5
    Macca Guest

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


    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

  6. #6
    Steve Guest

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


    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



  7. #7
    Joe \Nuke Me Xemu\ Foster Guest

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

    "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!



  8. #8
    MarkN Guest

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


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

  9. #9
    macca Guest

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


    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!



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center