Trouble using Between in Select query in ms access 2010 database

Question

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

You may have to use: startdate = DateValue(Me.Text98) and enddate = DateValue(Me.Text100). — Gustav
– Gustav, Commented May 3, 2019 at 10:58
Thanks Gustav, I have implemented your suggestion and whilst it does not cause any problem I am still getting the same Type Mismatch error. I agree with kiks73 that this is probably something to do with how the dates are being interpreted but even after implementing his formatting of the variables the error remains. Any further thoughts greatly appreciated. — alexw
– alexw, Commented May 3, 2019 at 13:07

Zack · Accepted Answer · 2019-05-03 14:41:24Z

1

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).

answered May 3, 2019 at 14:41

Zack

2,3511 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

alexw Over a year ago

Thanks Zack, all excellent advice which I will implement. Also your code has solved my problem! My DateTime column has its format set to Short Date so only stores dd/mm/yyyy. Thanks everyone for your efforts.

Zack Over a year ago

From the documentation: "Formatting only changes how the data is displayed and does not affect how the data is stored or how users can enter data." Your table will still store the time portion, regardless of the format. This article describes using validation rules to enforce date-only values. You might also be able to use data access macros for the same purpose.

alexw Over a year ago

Thanks again Zack, I will check that out.

Parfait · Accepted Answer · 2019-05-03 14:45:48Z

1

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

answered May 3, 2019 at 14:45

Parfait

108k19 gold badges103 silver badges138 bronze badges

2 Comments

alexw Over a year ago

Thanks Parfait, I will give this a try as this is a totally new approach for me.

Parfait Over a year ago

Understood but do note the concept of parameterization is an industry standard and advised beyond MS Access (wherever SQL is used in application layer). A clever, malicious user of your form can possibly run SQL injection.

Collectives™ on Stack Overflow

Trouble using Between in Select query in ms access 2010 database

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related