date comparison


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: date comparison

  1. #1
    sxy Guest

    date comparison


    I'm trying to retrieve a set of records with rs!dataDate between two dates,
    d1 and d2. The following code doesn't seem to work as the returned recordset
    is empty when it should be otherwise:

    d1 = DateSerial(1983, 1, 1)
    d2 = DateSerial(1985, 1, 1)
    sql = "Select * From tblName Where dataDate Between " & d1 & " AND " & d2
    rs.Open sql, conn, adOpenDynamic

    I also tried putting the d1 and d2 in '', but that causes mismatch datatype
    error. rs!dataDate is of type Date. What's the problem here?
    Thanks!

    Shannon

  2. #2
    Dean Earley Guest

    Re: date comparison

    > I'm trying to retrieve a set of records with rs!dataDate between two dates,
    > d1 and d2. The following code doesn't seem to work as the returned recordset
    > is empty when it should be otherwise:
    >
    > d1 = DateSerial(1983, 1, 1)
    > d2 = DateSerial(1985, 1, 1)
    > sql = "Select * From tblName Where dataDate Between " & d1 & " AND " & d2
    > rs.Open sql, conn, adOpenDynamic
    >
    > I also tried putting the d1 and d2 in '', but that causes mismatch datatype
    > error. rs!dataDate is of type Date. What's the problem here?


    What are d1 and d2 defined as?

    Try:
    sql = "Select * From tblName Where dataDate > #" & format(d1,"mm/dd/yyyy") & " dataDate < " & format(d2,"mm/dd/yyyy")


    --
    Dean Earley (dean.earley@icode.co.uk)
    Assistant Developer

    iCode Systems




  3. #3
    sxy Guest

    Re: date comparison


    d1 and d2 are of type Date:
    dim d1 as date, d2 as date

    Still doesn't work. No record is being returned.

  4. #4
    Douglas J. Steele Guest

    Re: date comparison

    Take a look at what's being assigned to sql. Depending on your regional date
    setting, you'll find it's something like:

    Select * From tblName Where dataDate Between 1983-01-01 AND 1985-01-01

    That's because the date is being coerced into a string.

    Access wants its string dates delimited with # (and in mm/dd/yyyy format,
    regardless of what your short date format has been set to through Regional
    Settings)

    Try the following instead:

    sql = "Select * From tblName Where dataDate Between " & Format$(d1,
    "\#mm\/dd\/yyyy\#") & " AND " & Format$(d2, "\#mm\/dd\/yyyy\#")


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele


    "sxy" <xiany33@yahoo.ca> wrote in message news:3d04beeb$1@10.1.10.29...
    >
    > I'm trying to retrieve a set of records with rs!dataDate between two

    dates,
    > d1 and d2. The following code doesn't seem to work as the returned

    recordset
    > is empty when it should be otherwise:
    >
    > d1 = DateSerial(1983, 1, 1)
    > d2 = DateSerial(1985, 1, 1)
    > sql = "Select * From tblName Where dataDate Between " & d1 & " AND " & d2
    > rs.Open sql, conn, adOpenDynamic
    >
    > I also tried putting the d1 and d2 in '', but that causes mismatch

    datatype
    > error. rs!dataDate is of type Date. What's the problem here?
    > Thanks!
    >
    > Shannon




  5. #5
    sxy Guest

    Re: date comparison


    Thank you very much =0)

  6. #6
    Sue Harsevoort Guest

    Re: date comparison

    Shouldn't that be:

    sql = "Select * From tblName Where dataDate > #" & format(d1,"mm/dd/yyyy") &
    "# and dataDate < #" & format(d2,"mm/dd/yyyy") & "#"

    Sue

    "Dean Earley" <dean.earley@icode.co.uk> wrote in message
    news:3d04cb16@10.1.10.29...
    >
    > Try:
    > sql = "Select * From tblName Where dataDate > #" & format(d1,"mm/dd/yyyy")

    & " dataDate < #" & format(d2,"mm/dd/yyyy") &
    >
    >
    > --
    > Dean Earley (dean.earley@icode.co.uk)
    > Assistant Developer
    >
    > iCode Systems
    >
    >
    >




  7. #7
    Dean Earley Guest

    Re: date comparison

    > Shouldn't that be:
    >
    > sql = "Select * From tblName Where dataDate > #" & format(d1,"mm/dd/yyyy") &
    > "# and dataDate < #" & format(d2,"mm/dd/yyyy") & "#"
    >
    > Sue


    Oops )

    Forgot the AND

    --
    Dean Earley (dean.earley@icode.co.uk)
    Assistant Developer

    iCode Systems



  8. #8
    cje Guest

    Re: date comparison


    "sxy" <xiany33@yahoo.ca> wrote:
    >
    >Thank you very much =0)


    And from me too! This was really useful.


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