Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    3

    Unanswered: How to use select statement using between condition in vba

    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.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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
    Last edited by MikeTheBike; 04-12-07 at 04:59.

  3. #3
    Join Date
    Apr 2007
    Posts
    3
    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.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  5. #5
    Join Date
    Apr 2007
    Posts
    3
    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 !

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •