[RESOLVED] date between ... not working.. :((

[developerno1]

i'm trying to generate a report of entries between two dates....
here date1 and date2 are date-time picker values...

Select * From Expense where supplier = '" & supp & "' AND Date BETWEEN " & date1 & " AND " & date2

and in the db they are date-time values...
but even if i pass the dates as 1/1/2013 and 1/1/2014 it retrieves nothing.. though there are many entries...

[techgnome]

This is why it is a good idea to:
1) stuff the SQL into a string variable and display it before running it, because then things like this:
Select * From Expense where supplier = 'some value' AND Date BETWEEN 1/1/2013 AND 1/1/2014
become noticable... and if it's still not clear... it's the dates... it'll use division rather than dates... that is why you're not getting anything... to have them treated you need to use date delimiters... or...
2) use parameters and avoid all of the above problems


-tg

[developerno1]

it'll use division rather than dates... that is why you're not getting anything... to have them treated you need to use date delimiters... or...
2) use parameters and avoid all of the above problems


-tg

how do i fix this then? passing via a string doesnt help.. and arent my date1 and date2 values treated as parameters? also help on date delimiters pls. :-)

[developerno1]

    .Open "Select * From Expense where supplier = '" & supp & "' AND trdate BETWEEN #" & date1 & "# AND #" & date2 & "#", con, adOpenStatic

worked... !! thanks a lot..

[si_the_geek]

arent my date1 and date2 values treated as parameters?

No, they are treated as values... but only if:

• you delimit them properly (as you were failing to do, hence this thread),
• format them properly (which you don't seem to be doing, so could fail [especially on different computers])
• deal with the variety of special characters properly (such as the ' character inside text values)

A mistake on any of those can cause a wide variety of errors and bugs.


If you use parameters they are always treated as values, and you don't need to worry about the points above.

For a fuller explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).