2003 Date var in SQL str for ADODB recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm back,
Building an SQL string for an ADODB recordset:
. . .
Dim dteDate1, dteDate2 As Date
cnn1.Open strSourceDB

dteDate1 = #7/1/2004#
dteDate2 = #7/31/2004#

strWhere = "WHERE (((tblTransactions.TransactionDate) >= " & #7/1/2004# _
& " And (tblTransactions.TransactionDate) < " & "#7/31/2004#" _
& "))"


'strWhere = "WHERE (((tblTransactions.TransactionDate) >= " & #7/1/2004# _
'& " And (tblTransactions.TransactionDate) < " & dteDate2 _
'& "))"


strWhere = "WHERE (((tblTransactions.TransactionDate) >= " & dteDate1 _
& " And (tblTransactions.TransactionDate) < " & "#7/31/2004#" _
& "))"

. . .

A. works; B. does too but gives wrong result; C. does not. (I get a 3021, BOF or EOF, or record is deleted or there is not one)

I thought the Date variables and the string literals would be interchangable?
 
Oh never mind. This is like my old days, taking my truck by Robby's garage.
I wrrapped the dteVars in # signs and it proves OK.
 
I wrrapped the dteVars in # signs and it proves OK.
Click to expand...

It's safest to format the dates properly using a Format() function too. One
day you _will_ get hit by the Swedish temp who changes her Control Panel
settings to what she knows; and all your queries will start giving very sad
results. Jet gives you the chance to proof your application completely
against international formats: take it!

"tblTransactions.TransactionDate < " Format(dteDate2, "\#mm\/dd\/yyyy\#")


Hope that helps


Tim F
 
Back
Top