[RESOLVED] Access Date searching using VB6 & SQL

[slaphead109]

I have a list of product serial numbers and the dates the numbers were issued in an access database. I am querying from a VB program which lets the users pick a start and end date to assign the relevant serial numbers within that date range to a recordset.

I can a list for a specific date using SQL = "SELECT * From Serial WHERE Date LIKE " & "'" & FirstDate & "'"
Set rsFirst = cnn1.Execute(SQL, , adCmdText)

But can anyone tell me how to populate a recordsetwith dates in a range of say June 3rd to june 24th?

Thank you.

[Hack]

WHERE datefield => '#" & txtFromDate.Text & "#'"
AND datefield <= '#" & txtToDate.Text & "#'"

I may have, and probably did, mess up the # signs (I always had trouble with those things), so you may have to tweak it a bit, but the fundamential logic is:

Greater Than/Equal To [StartDate] AND Less Than/Equal To [End Date]

Also, if your date field really is called date (and I hope it is not), then you would need to put that in brackets [Date]. Date is a reserved word, and you should always avoid using reserved words as field names.

[GaryMazzone]

You can also use a between statement

WHERE Format(datefield,'Short Date')  Between '#" & format(txtFromDate.Text,'Short Date') & "# AND #" & Format(txtToDate.Text,'Short Date') & "#'"

[Hack]

You can also use a between statement

WHERE Format(datefield,'Short Date')  Between '#" & format(txtFromDate.Text,'Short Date') & "# AND #" & Format(txtToDate.Text,'Short Date') & "#'"

I never get those # signs right.

[slaphead109]

That was great help, thank you both.

Just out of interest, the visual basic code:WHERE datefield => '#" & txtFromDate.Text & "#'" AND datefield <= '#" & txtToDate.Text & "#'"

only works for me when I leave out the # signs. I HAD named the date field "Date" , so I changed it and made it a text field.

Thanks to your help I have a working search facility.