[RESOLVED] Access Date searching using VB6 & SQL

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.

VB Code:

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.

You can also use a between statement

VB Code:

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

Quote:

---

Originally Posted by GaryMazzone

You can also use a between statement

VB Code:

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

---

I never get those # signs right. :D

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" :o , so I changed it and made it a text field.

Thanks to your help I have a working search facility. :thumb: