Date Range Search in ADO 1

[katoc]

I have a query that I use to open a form which is bound by an ADO recordset. I can't get the date range to work. Here's the syntax:

sqlstring = SELECT [Folders].* FROM [Folders] WHERE [DateEntered] between #" & dateFrom.value & "# and #" & dateTo.value & "#"

It keeps giving me a syntax error.
"[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near the keyword 'between'

 

[PHV]

Perhaps something like this (don't know the exact T-SQL syntax) ?

sqlstring = "SELECT Folders.* FROM Folders WHERE DateEntered BETWEEN '" _
 & Format(dateFrom.Value, "yyyy-mm-dd") & "' And '" _
 & Format(dateTo.Value, "yyyy-mm-dd") & "'"

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

 

[katoc]

I still get the incorrect syntax error. I tried it with the # signs as well and received the same error message (as in my first post).

Here's what the code looks like. Keep in mind, I'm using an ADO connection so let me know if the syntax should be different when making date range searches.

sqlstring = "SELECT Folders.* FROM Folders WHERE [DateEntered] BETWEEN #" _
& Format(datefrom.Value, "mm-dd-yyyy") & "# and #" _
& Format(dateto.Value, "mm-dd-yyyy") & "#)"

[DateEntered] is stored in the tables as Date/Time so it has a Date and a Time.

 

[PHV]

With ADO you should use the native SQL dialect.

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

 

[katoc]

What would be the native SQL dialect?

Sofia

 

[PHV]

What would be the native SQL dialect?
The SQL supported by the DBMS you're connected to with ADO:
T-SQL for SQL Server, JetSQL for msaccess, ...

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

 

[katoc]

It's SQL Server.

Know where I can read about the syntax? Or can give me a clue? Is it close to what I have now?

 

[cmmrfrds]

sqlstring = SELECT [Folders].* FROM [Folders]
WHERE Convert(varchar(10),[DateEntered],101)
between '" & Format(dateFrom.value,'Short Date')
& "' and '"
& Format(dateTo.value,'Short Date') & "'"


The sql server function to convert date is called Convert.

Convert(data type, yourfield, style)
style 101 strips the time off the date.
look in sql server help file for style definitions.

In the Access Format function 'Short Date' strips the time off.

 

[katoc]

Thanks!!!

I didn't need the 'convert' or 'format' for short date since they're already in that format but your syntax worked. The select statement I used was:

SELECT [folders].* from [folders]
WHERE [dateEntered] BETWEEN '" & datefrom.value & "' and '"
& dateadd("d", 1, dateto.value) & "'"

I had to add the dateadd function because if I do a search between 12/5/2006 and 12/5/2006, it brings back no results, but with dateadd on the dateto value, it does a search between 12/5/06 and 12/6/06 and brings back all records on 12/5/2006.

So it works perfectly now.

Thanks!!