CODEWITHSUNDEEP
sqlvbams-accessms-access-2010
alexw
alexw

Reputation: 15

Trouble using Between in Select query in ms access 2010 database

Morning, please could someone help me get this SELECT query working? I have a form (frm_Reports) upon which I have two text boxes (Text98 and Text100 both formatted to take ShortDate) and a button (RunReport). The user enters a start date in Text98 and an end date in Text100 and then clicks RunReport button. The dates from Text98 and Text100 are stored in variables as Dates (called stardate and enddate) and then a SELECT query (see code below) is meant to run that retrieves all records in a table (tbl_details) that have an entry date (under a column headed DateTime) falling BETWEEN the start date and end date entered by the user. The problem is I cannot get the SELECT query to work.

I am not good at sql type statements so I have tried building my query string a bit at a time testing it as I go. I have managed to get this far successfully:

sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) > #" & startdate & "#;"

However, as soon as I try and turn this string into a BETWEEN type query I get

"Run-time Error '13': Type mismatch.

    Private Sub RunReport_Click()

    Dim selectedreport As String
    Dim startdate As Date
    Dim enddate As Date
    Dim sqlstr As String

    selectedreport = Me.ComboReport.Column(1)
    startdate = Me.Text98
    enddate = Me.Text100
    'sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) > #28/04/2019#;" - THIS WORKS
    'sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) > #" & startdate & "#;" - THIS WORKS
    sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) BETWEEN #" & startdate & "#" And "#" & enddate & "#;" 'THIS DOES NOT WORK

    Dim dbs As Database
    Dim rst As Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sqlstr)
    With rst
        While Not .EOF()
        vName = .Fields("DateTime").Value
        Debug.Print vName

    .MoveNext
    Wend
    End With

    dbs.Close

    End Sub

I would be very grateful if anyone could show me where I am going wrong with this statement and suggest one that would actually work. Thanks

Upvotes: 1

Views: 154

Answers (2)

Parfait
Parfait

Reputation: 107652

Consider using a parameterized query using QueryDefs and avoid any messy concatenation or quote/symbol enclosures depending on data type. Then bind form values to querydef object and assign querydef to recordset object.

SQL (save below as a stored query object)

PARAMETERS [param_start] Date, [param_end] Date;
SELECT * FROM tbl_details 
WHERE (tbl_details.DateTime) BETWEEN [param_start] AND [param_end]

VBA (bind query parameters to report's recordset)

Private Sub RunReport_Click()
    Dim qdef As QueryDef
    Dim rst As Recordset
    Dim selectedreport As String

    ' OPEN QUERY AND BIND PARAMS 
    Set qdef = CurrentDb.QueryDefs("mySavedParamQuery")

    qdef!start_param = Me.Text98
    qdef!end_param = Me.Text100

    ' ASSIGN QUERY RESULT TO RECORDSET
    Set rst = qdef.OpenRecordset()    

    With rst
        While Not .EOF()
            Debug.Print .Fields("DateTime").Value
            .MoveNext
        Wend
    End With

    Set qdef = Nothing    
    Set rst = Nothing
End Sub

Upvotes: 1

Zack
Zack

Reputation: 2341

When generating SQL from VBA, it's always a good ideal to Debug.Print the SQL initially. In this case, it seems you can't even get this far because you're building your SQL string incorrectly---the And needs to be within the quotation marks:

sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) BETWEEN #" & startdate & "# And #" & enddate & "#;"
' instead of this:
sqlstr = "SELECT * FROM tbl_details WHERE (tbl_details.DateTime) BETWEEN #" & startdate & "#" And "#" & enddate & "#;"

A couple of other points:

  • If your DateTime column contains times, you need to change your code a bit. If a user enters 2019-05-03 as the end date, values that occur on May 3 will be excluded (e.g. 2019-05-03 15:00).
  • DateTime is a bad column name, since it doesn't describe your data. You may also want to consider renaming tbl_details. Details of what?
  • You should also go ahead and give your textboxes meaningful names. txtStartDate instead of Text98.
  • When declaring dbs and rst, it's best to add the library (e.g. Dim dbs as DAO.Database), since both the ADO and DAO libraries use the same classes.
  • Instead of hard-coding SQL in VBA, you might also be able to use a parameterized query, so you don't have to generate SQL (which can be tricky sometimes).

Upvotes: 1

Related Questions