SQL not returning date records 1

[MattSTech]

I am missing something simple here. If you can assist, it would be appreciated.

Connecting to a SQL database that stores well over a bajillion records. Trying to narrow the scope of my search a titch.

I am trying the following SQL statements.
#1 returns all records but there are ton of them
#2 returns 0 records
#3 returns 0 records

dteFilterStart = Format("1/4/2007", "mm/dd/yyyy")
dteFilterEnd = Format("1/8/2007", "mm/dd/yyyy")

        'strSQL = "SELECT * FROM ladetail"
        'strSQL = "SELECT * FROM ladetail WHERE fdate BETWEEN " & dteFilterStart & " AND " & dteFilterEnd
        strSQL = "SELECT * FROM ladetail WHERE fdate =" & dteFilterStart

The following returns true when the sql statement includes all records, so my formatting must be right.
MsgBox dteFilterStart = objRS.Fields("fdate").Value

What am I blatently missing here?

 

[gmmastros]

The problem is that you are not delimiting your dates in the query.

If this is SQL Server, then:

strSQL = "SELECT * FROM ladetail WHERE fdate BETWEEN [!]'[/!]" & dteFilterStart & "[!]'[/!] AND [!]'[/!]" & dteFilterEnd [!]& "'"[/!]

For Access:

strSQL = "SELECT * FROM ladetail WHERE fdate BETWEEN [!]#[/!]" & dteFilterStart & "[!]#[/!] AND [!]#[/!]" & dteFilterEnd [!]& "#"[/!]

You see.... without the delimiter, your query looks like this....

Where fdate between [!]1/4/2007[/!] AND [!]1/8/2007[/!]

The part in red LOOKS like math to the database engine. Integer math, too. So it will be evaluated like this:

Where fdate between [!]0[/!] AND [!]0[/!]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MattSTech]

Thank you so much. I had searched through the forum and found the #' delimiters but when I used this it gave me Line 1: Error near '# or something like that. This has fixed the problem.
Why the different formats for SQL vs Access?
Thanks again!

 

[gmmastros]

Why the different formats for SQL vs Access?

I don't know for sure. It's probably a backward compatibility thing.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom