How do I select on a SQL Datetime field in Excel Query?

[Guest]

I can't seem to find the correct format for the date so that it will actually
exclude or include certain dates.
I am trying to do a parameter query where I can then enter a date range of
the records I want this time.
This is a database query from a SQL table.

 

[Robin Hammond]

Record adding a query table then edit the query to get the values you want

e.g.

Sub AddDateRangeQuery(dtStart as date, dtEnd as date)
Dim strDateStart as string
Dim strDateEnd as string

strDateStart = format("yyyy-mm-dd",dtStart)
strDateEnd = format("yyyy-mm-dd",dtEnd)

'the query connection stuff goes here, but replace the command text line
with something like this
'watch out for the single quote marks in this statement around the dates
CommandText = Array("SELECT * FROM GetSecurityRSIDaily WHERE SecurityIndex=1
" & _
"AND PeriodEnd BETWEEN '" & strDateStart & "'AND '" & strDateEnd &
"' ORDER BY PeriodEnd DESC"
'remainder of the query connection stuff here

End Sub

If you want to get just a specific few days, that are non-contiguous, have a
look at the IN clause. I would probably add all the selected dates to a
collection, then pass the collection to the sub that builds the query and
have the sub build the SQL command string in this case.
e.g.
PeriodEnd IN ('2004-01-01','2004-06-30','2004-02-29')

If you need help on SQL, you can download Books Online for free from MS.

Robin Hammond
www.enhanceddatasystems.com