[RESOLVED] SQL 'BETWEEN' not returning any results

[aikidokid]

My SQL statement sometimes returns some results and other times nothing.
There are records to display, but for some reason it's not finding them.
I get rs.EOF = true.

        strSQL = "SELECT * FROM tbl_Finances"
        strSQL = strSQL & " WHERE Finance_Date BETWEEN #" & dtDateOne & "# AND #" & dtDateTwo & "#"

debug.Print strsql
SELECT * FROM tbl_Finances WHERE Finance_Date BETWEEN #01/06/2007# AND #01/07/2007#

In my tset table, I should be returning 6 records.

The code that loads from the recordset ro the flexgrid works when all records are displayed, so i am guessing that that code is not the problem.

[Hack]

If you run your SELECT form within Access do you get the results?

[si_the_geek]

Are you searching for data from January?

in SQL statements, the date format needs to be like "yyyy-mm-dd" or "mm/dd/yyyy". Using non-US date formats will work occasionally (when the dd part is 13 or more), and at other times be unpredictable.

[techgnome]

What if you change it from 07 to 08.... do you then get your records?

-tg

[aikidokid]

If you run your SELECT form within Access do you get the results?

Not quite sure how to do this. I will take a look at Access.

[aikidokid]

Are you searching for data from January?

in SQL statements, the date format needs to be like "yyyy-mm-dd" or "mm/dd/yyyy". Using non-US date formats will work occasionally (when the dd part is 13 or more), and at other times be unpredictable.

This seems to be the problem here.

How do I reformat the date?
I have tried
FormattedDate(0) = Format(CDate(txtDate(0).Text), "yyyy,mm,dd")
which doesn't seem to do anything.
How about manipulate the text to swap around the month and day?

[aikidokid]

Hmmm...

If I Debug.Print this
FormattedDate(0) = Format(CDate(txtDate(0).Text), "yyyy,mm,dd")
I get this
2007,06,01

Yet if I put the cursor over the variable FormattedDate(0) it shows 01,06,2007

And I still get no records shown!

[GaryMazzone]

Format the date field like this :

strSQL = "SELECT * FROM tbl_Finances"
        strSQL = strSQL & " WHERE Finance_Date BETWEEN #" & Format(dtDateOne,"mm/dd/yyyy") & "# AND #" & Format(dtDateTwo,"mm/dd/yyyy") & "#"

Try that and see if you get any records (US date format)

[aikidokid]

Format the date field like this :

strSQL = "SELECT * FROM tbl_Finances"
        strSQL = strSQL & " WHERE Finance_Date BETWEEN #" & Format(dtDateOne,"mm/dd/yyyy") & "# AND #" & Format(dtDateTwo,"mm/dd/yyyy") & "#"

Try that and see if you get any records (US date format)

Yeah, this returns what is expected each time.

So what is the difference between me formatting it with
mm,dd,yyyy
to
yyyy,mm,dd
as the second didn't seem to work?

[si_the_geek]

What is FormattedDate declared as? If it is a Date data type, there is no point formatting the value you put into it - Date variables do not have formats, only values (the VB IDE automatically formats these values when displaying them to you).

Also, as you are converting from text the CDate and Format functions are likely to be doing things that you don't want - they make guesses as to what the current format of the text is, and convert based on that. It is best to avoid functions like that when converting from a text value to a date value - tho the Format function is ideal to convert from date to text.

The best thing to do is use a control which specifically deals with Date values (rather than text/string), such as the DateTimePicker. When you are then using the values in a string (such as an SQL statement) you should format them as part of adding them to the string (as in Gary's post).

[aikidokid]

I have changed my code to gary's now and it seems to be working.

But, in answer to your questions;
FormattedDate was declared as a Date value.
The text from the text box is validated as the format dd/mm/yyyy

[GaryMazzone]

I think I would go with SI comment on using DateTimePickers for allowing the user to enter date data. (I always do). That way you are sure that it is a date and then will have no problem with formating.

If not I would use an IsDate() function before using the data and test it.

[aikidokid]

Which component has the DateTimePickers ?

[si_the_geek]

They are in Common Controls 2, along with a MonthView control which is also useful for this kind of thing.

If not I would use an IsDate() function before using the data and test it.

Unfortunately IsDate is even worse - all it tells you is that somehow the value can be implicitly converted into a date (which is true for lots of values that we can easily tell aren't actually valid input).

[aikidokid]

I have now changed this to DateTimePickers, and along with Gary's code it all seems to be working now.

Thanks ALL