Including Date variables in SQL

[drewpee]

Im trying to create a report that only includes records that was created between to Date Variables, dateTo and dateFrom.

There are three combo boxes used to get the values for the two dates. Here's the code

I didn't include the select case for the month(in string) to be converted into an integer (ex. if "Jan" nFromMonth=1"

dateFrom = CDate(nFromMonth & "/" & cmbFromDay & "/" & cmbFromYear)
dateTo = CDate(nToMonth & "/" & cmbToDay & "/" & cmbToYear)

I've tested this and im confident that theres nothing wrong with this.

Now, I want to do something like this:

Select CCIF_No, Date_Recieved From serviceRS Where Date_Recieved >=dateFrom AND Date_Recieved <=dateTo

This is my actual code so far and it doesnt work:

SELECT CCIF_No, Date_Recieved, FROM serviceRS WHERE (Date_Recieved <= '"&dateFrom&"' AND Date_Recieved <= '"&dateTo&"')

I tried Playing with the quotation marks and &s and #s but still doesnt work, either i get a "Failed getting rowset(s) from current data source" or a "Data type mismatch". Please advice. Thanks!

and sorry if this has been already tackled but i have searched daniweb and other forum sites but didn't get any solution.

[kinwang2009]

Hi,
Try this.

"SELECTCCIF_No, Date_Recieved, FROM serviceRS WHERE Date_Recieved <= '" & DateFrom & "' AND Date_Recieved <= '" & DateTo & "'"

Hope this helps.

[vb5prgrmr]

And if you are using access, instead of the single ticks (') you will need to use the pound (#) sign...

Good Luck

[drewpee]

"SELECTCCIF_No, Date_Recieved, FROM serviceRS WHERE Date_Recieved <= #" & DateFrom & "# AND Date_Recieved <= #" & DateTo & "#"

"SELECTCCIF_No, Date_Recieved, FROM serviceRS WHERE Date_Recieved <= '" & DateFrom & "' AND Date_Recieved <= '" & DateTo & "'"

both codes doesn't ..:(( When i try to put it in the report designer, it says, no fields found. or am I doing the # thing wrong?

[vb5prgrmr]

I think it is in your logic... less that equal to #" & DateFrom & "# AND less than equal to #" & DateTo & "#" (<=#" & DateFrom & "# AND... <=)

Should that not be >=#...AND...<=#

Good Luck

[drewpee]

I don't think so. Because even if it was wrong i think it would still run but wont return any records. XD