What's wrong with this SQL code?

[yenni]

Where am I going wrong with this code?
I'm trying to retrieve all records that fall between
Date1 and Date2.
Is there a simpler way of writing SQL code like this?

Set rst = dbsRecdata.OpenRecordset("SELECT rechdr.recno, RecHdr.ponum," _
            & " RecHdr.daterecvd, RecLine.ItemNum, RecLine.Descrip, RecLine.QtyRecvd, RecLine.UnitPrice" _
            & " FROM RecHdr, RecLine" _
            & " WHERE RecHdr.recno = RecLine.recno" _
            & " and RecHdr.DateRecvd >= " & Date1 _
            & " And <= " & Date2 & "" _
            & " ORDER BY RecHdr.recno ASC,RecLine.descrip ASC", dbOpenDynaset)

[batleycr]

Yes yenni, there is the SQL command, "BETWEEN":

Set rst = dbsRecdata.OpenRecordset("SELECT rechdr.recno, RecHdr.ponum," _
& " RecHdr.daterecvd, RecLine.ItemNum, RecLine.Descrip, RecLine.QtyRecvd, RecLine.UnitPrice" _
& " FROM RecHdr, RecLine" _
& " WHERE RecHdr.recno = RecLine.recno" _
& " and RecHdr.DateRecvd BETWEEN #" & Date1 _
& "# And #" & Date2 & "#" _
& " ORDER BY RecHdr.recno ASC,RecLine.descrip ASC", dbOpenDynaset)

Dates need to appear like this: #24/11/2000# in an SQL query, that's why I've included them in mine.

I'm not sure this query works though - I don't have your tables to test it on and I'm not really in the mood to create a test Db. My reading through makes me think it should though.

[batleycr]

Sorry Yenni,

I'm from Australia, it's hard to slip from our date format to the US one. The actual date format SQL requires is #11/24/2000#.

And yes it is the 24th here!

[yenni]

Thanks for yours ans batleycr, I'll give a try.
Actually, I'm from Jamaica, and we use the same date
format as you, i.e dd/mm/yy.
By the way, take it easy with our guys down there,
o.k? (cricket, that is)