How to use select statement using between condition in vba

hvisa · #1 (**permalink**) 04-12-07, 00:42

Hello,

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 .

Any help will be appreciated.

MikeTheBike · #2 (**permalink**) 04-12-07, 03:56

Hi

There are two way (at least)

Assuming startdate and enddate are variable (or control names - and the code is in the form module)

sSQL = "Select count(PrimaryKeyField) as valcount from price " & _
"WHERE date between #"& startdate & "# and #" & enddate & "# "

OR if you use are using UK local date settings

sSQL = "Select count(*) as valcount from price" & _
"WHERE date between #"& format(startdate,"mm/dd/yy") & "# and #" & fromat(enddate,"mm/dd/yy") & "# "

rs.open sSQL, YourConnectionOject

if not rs.EOF then
msgbox "Record Count = " & rs(0)
else
msgbox "No records"
end if

Not you cannot use Count() with * field selection ?

The other way is to return a field(s) and use the recordset recordcount property.

HTH MTB

hvisa · #3 (**permalink**) 04-12-07, 13:03

Hello Mike,

Thanks for the reply, but i am still having problem in the valcount. it seems not to return any value.

I have declared it as integer, so when i run it ,it has a value of 0, irrespective of the startdate and end date.
Any suggestions?

Thanks , again.

MikeTheBike · #4 (**permalink**) 04-13-07, 03:58

Hi

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

Code:

    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"
    
    cn.Open "TEST"
    
    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
    
    MsgBox rs(0)

This also gives the same result (count)

Code:

    SQL = "Select DWN from tblDayworks "
    SQL = SQL & "WHERE StartDate BETWEEN #" & Format(StartDate, "mm/dd/yy") & "# AND #" & Format(EndDate, "mm/dd/yy") & "#"
    
    rs.Open SQL, cn, adOpenStatic, adLockReadOnly
    
    MsgBox rs.RecordCount

You do not say what your computer regional date format is (I'm in the UK).

One thought is that your date field name is 'date' (not a good idea, as I have found out) so try this

sSQL = "Select count(PrimaryKeyFieldName) as valcount from price " & _
"WHERE [date] between #" & format(startdate,"mm/dd/yy") & "# and #" & fromat(enddate,"mm/dd/yy") & "#"

ie put [] round date

I am also not sure what you have dimensioned as integer !?

MTB

hvisa · #5 (**permalink**) 04-18-07, 20:27

Mike,

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 !

MikeTheBike · #6 (**permalink**) 04-19-07, 03:18

Hi

Have you actually tried this syntax (including # sign &[] etc)

sSQL = "Select count(*) as valcount from price " & _
"WHERE [date] between #"& startdate & "# and #" & enddate & "# "

What does this actally return or what actually happen, where/how are you using it? A bit more code may help.

Add

msgbx sSQL

after the above statment and check the dates you expect are concatenated correctly into the SQL statement, and let us know.

Sorry cannot help more without more info.

MTB

ps contrary to my previous statment you can use * with count (I must have had something else wrong when I tried before).

Internet Services

Web Development

Digital Marketing

Consumer Tech