-
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?
Code:
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)
-
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.
-
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!
-
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) :)
