Unanswered: How to use select statement using between condition in vba
Can someone tell me what is the syntax for using select statement with between condition , in vba excel?
I am trying to use a ADO connection to get data from Sql into excel. I am trying to pull data from the table 'price' if the date value is between start date and end date ( which are input boxes from the user) and i need to count the number of records which is got from the below statement.
sSQL = "Select valcount=count(*) from price" & _
"WHERE date between 'startdate' and 'enddate' "
While executing it , it passes through the select statement but does not work as i do not get any value in valcount .
Difficult to say what the problem is without seeing your query string construction.
I assume there are records between the dates selected ?
For information this code works find
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
Dim StartDate As String
Dim EndDate As String
StartDate = "31/12/06"
EndDate = "31/01/07"
SQL = "Select Count(DWN) as DwkCount from tblDayworks "
SQL = SQL & "WHERE StartDate BETWEEN #" & Format(StartDate, "mm/dd/yy") & "# AND #" & Format(EndDate, "mm/dd/yy") & "#"
rs.Open SQL, cn, adOpenStatic, adLockReadOnly
Thanks for replying. Yes, there are records between the dates specified, but my field name is using the datatype as date. Wat should I change it to ? I live in USA. Hope I have answered all your questions.
Thanks again !