date comparison

[sxy]

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

[Dean Earley]

> 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

[sxy]

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

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

[Douglas J. Steele]

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

[sxy]

Thank you very much =0)

[Sue Harsevoort]

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

[Dean Earley]

> 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

[cje]

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

And from me too! This was really useful.