SQL between two dates (1 Viewer)

[rahulgty]

I want to search a table for records between two dates.

My code is like that

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
Dim Sdate As String
Dim Edate As String

Sdate = Me.txtBeginningDate
Edate = Me.txtEndingDate

Set db = CurrentDb()
str = "SELECT ID, Date, Item, QtyRec, QtyIssue FROM DailyIssue Where Date >= '" & SDate & "' AND DATE <='" & Edate &"'"
Set rs = db.OpenRecordset(str, dbOpenSnapshot)

But it is not working at all. Error message is Data Type Mismatch.

Any help is appreciated in advance.

rahulgty

 

[Bat17]

not a good idea calling the field Date as it is a resreved Access word

still try this

str = "SELECT ID, Date, Item, QtyRec, QtyIssue FROM DailyIssue Where [Date] >= #" & SDate & "# AND [DATE] <='# & Edate &"#"


HTH

Peter

 

[WayneRyan]

R,

str = "SELECT ID, Date, Item, QtyRec, QtyIssue FROM DailyIssue Where Date >= #" & SDate & "# AND DATE <=#" & Edate &"#"

btw, You can use BETWEEN instead of the >= and <=

btw, Don't use the word Date as a field name. It's reserved for use by Access.

Wayne

 

[Bat17]

depending on your date format you may get problems with date conversion issues as well

I would format the text boxes to Date format and dim Sdate and Edate as Dates

 

[rahulgty]

Thanks a lot......

Thanks Bat17 and WayneRyan for your reply

Bat17 some mistake in your syntax, but WayneRyan's synatx is correct and working, I am grateful to both of you.

I have tried lot with between clause but it doesn’t work. While I have searched on Google I found its a common mistake using between clause with date, u can seehere .

Thanks again to solve my problem.

rahulgty

 

[Bat17]

Sorry about the syntax error it was the end of a long day for me and I got a quote in the wrong lace.
Between does work and I have found it to be faster in complex queries than >= and <=.
The only caution that article is giving about "Between" is to remind that it is inclusive and not that there was any problem in the function itself.
so this should work OK
str = "SELECT ID, Date, Item, QtyRec, QtyIssue FROM DailyIssue WHERE (((DailyIssue.Date) Between #" & Sdate & "# And #" & Edate & "#))"


Peter


Peter

 

[rahulgty]

Yes Bat17,
You are right.

its working now. I have replace >= <= with between.

Thanks

rahulgty