query in vba with dates giving erroneous data

[dharkangel]

Hi,

I wrote this seemingly simple query below that only gives me the correct data when I hardcode the dates like below (1/2/2006 and 1/9/2006). Whenever I substitute these dates with a variable I get errors or bad data.

strSQL = " SELECT TokyoInternalActions.[Issue ID]" & _
"FROM TokyoInternalActions INNER JOIN TokyoOGCommitDate ON TokyoInternalActions.[Issue ID]=TokyoOGCommitDate.[Issue ID]" & _
"WHERE TokyoInternalActions.Status=""Closed"" And DateValue(TokyoInternalActions.[Close Date])<=DateValue(TokyoOGCommitDate.[Original Commit Date]) And (DateValue(TokyoInternalActions.[Close Date])>=DateValue(""1/2/2006"") And DateValue(TokyoInternalActions.[Close Date])<DateValue(""1/9/2006""))"

myRecordSet.Open strSQL, , adOpenStatic

etc...............

I have tried doing the following but none work:

Dim beginningDate As String
Dim endingDate As String

beginningDate = "1/2/2006"
endingDate = "1/9/2006"

and use these variables in my query like this DateValue(beginningDate)...What am I doing wrong?

 

[PHV]

You may try this:
strSQL = "SELECT A.[Issue ID]" & _
" FROM TokyoInternalActions AS A INNER JOIN TokyoOGCommitDate AS C ON A.[Issue ID]=C.[Issue ID]" & _
" WHERE A.Status='Closed' AND DateValue(A.[Close Date])<=DateValue(C.[Original Commit Date])" & _[tt]
" AND DateValue(A.[Close Date])>=DateValue('" & beginningDate & "')" & _
" AND DateValue(A.[Close Date])<DateValue('" & endingDate & "')"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dharkangel]

This worked great! This exact syntax even worked when beginningDate and endingDate were already defined as Dates, which is something I don't understand. For example, I did this:


Dim beginningDate As Date
Dim endingDate As Date

beginningDate = DateValue("1/2/2006")
endingDate = DateValue("1/9/2006)

It looks like even if you predefine a date value like this you still have to use DateValue in the sql query statement. weird.