[RESOLVED] Dates and SQL again

[aikidokid]

Firstly, I have read the new FAQ's about dates that si_the_geek posted a couple of days ago, but I am still having problems:

I have made sure that the dates in the SQL statement have been converted to strings, by using the Format function.
I have also changed them to use the ISO format (YYYY/MM/DD).

I don't get an error with the actual SQL statement when I write it, but I get the error:

Data type mismatch in criteria expression

on the line rs.Open ....

'add new SQL statement to add SELECTED records
strSQL = "SELECT * FROM tbl_Finances"
strSQL = strSQL & " Where Finance_Date >= '" & Format(BetweenDate(0), "yyyy,mm,dd") & "'"
strSQL = strSQL & " And Finance_Date <= '" & Format(BetweenDate(1), "yyyy/mm/dd") & "'"
strSQL = strSQL & " ORDER BY Finance_Date"

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

BetweenDate() As Date

SELECT * FROM tbl_Finances Where Finance_Date >= '2007,07,01' And Finance_Date <= '2007/08/31' ORDER BY Finance_Date

UK Date:
01/07/2007
31/08/2007

[aikidokid]

Answered this myself!

Althought Format makes the value a string, it still needs the '#' surrounding the value, not the ' that strings need.

'add new SQL statement to add SELECTED records
strSQL = "SELECT * FROM tbl_Finances"
strSQL = strSQL & " Where Finance_Date >= #" & Format(BetweenDate(0), "yyyy,mm,dd") & "#"
strSQL = strSQL & " And Finance_Date <= #" & Format(BetweenDate(1), "yyyy/mm/dd") & "#"
strSQL = strSQL & " ORDER BY Finance_Date"

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

[techgnome]

Answered this myself!

Althought Format makes the value a string, it still needs the '#' surrounding the value, not the ' that strings need.

IF it's Acces, then yes.... otherwise, most other DBMS will allow a date to be passed as a string, and it'll convert it to a Date type.

-tg

[aikidokid]

Thanks tg, and yes, it is Access