passing start and end dates and a third criteria to a report query

wiklendt · Mar 16, 2009

hi.

i was playing with some VBA code to pass start and end dates to a report, and was using what seemed like the stock standard code everyone seems to use, however, i wanted to add one more criteria to my SQL to pass to the report: person who made the order(s).

i noticed the stock code did not allow for two null dates, so i have added this part to the code, for anyone interested. the query, if not restricted by a filter, returns all records; and if restricted only by person it does not error.

(edit: and you could theoretically continue to add more criteria to this too...)

if there is an easier way to do this, then by all means jump in and correct me.

Code:

Private Sub cmdRunOrderReport_Click()
    
    Dim strReport As String 'Name of report to open.
    Dim strField As String 'Name of your date field.
    Dim strWhere As String 'Where condition for OpenReport.
    
    Const conDateFormat = "\#dd\/mm\/yyyy\#"

    strReport = "rptOrders"
    strField = "[OrderDate]"
    
    ' ================================================================================
    ' CHECK FOR DATE RANGE
    ' ================================================================================
    If IsNull(Me.cmbDateStart) Then
        If Not IsNull(Me.cmbDateEnd) Then  'End date, but no start.
            strWhere = strField & " <= " & Format(Me.cmbDateEnd, conDateFormat)
[COLOR=Red]        Else 'neither start nor end dates
               strWhere = ""[/COLOR]
        End If
    Else
        If IsNull(Me.cmbDateEnd) Then  'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.cmbDateStart, conDateFormat)
            Else 'Both start and end dates.
                strWhere = strField & " Between " & Format(Me.cmbDateStart, conDateFormat) _
                & " And " & Format(Me.cmbDateEnd, conDateFormat)
        End If
    End If
    
    ' ================================================================================
    ' CHECK FOR PERSON FILTER AND ADD TO SQL STATEMENT
    ' ================================================================================
    If IsNull(Me.cmbPerson.Column(0)) Then
        'No person to filter by, so make no changes to strWhere
      Else
        If strWhere = "" Then 'no date filter, just person filter
            strWhere = "[PersonID] = " & Me.cmbPerson
            Else 'date and person filter
            strWhere = strWhere & "AND [PersonID] = " & Me.cmbPerson
        End If
    End If

    ' ================================================================================
    ' RUN THE REPORT
    ' ================================================================================
    DoCmd.OpenReport strReport, acViewPreview, , strWhere, acDialog

End Sub

boblarson · Mar 16, 2009

My sample here
http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip
has similar code.

wiklendt · Mar 16, 2009

thanks for the working sample, bob. it's always helpful to have something tangible to look at.

i had noticed last night, that the code i provided does actually NOT work if the constant date format is changed to UK the way i've got it there. it works fine when switching the ddmm part (to "\#mm\/dd\/yyyy\#"). i remember once reading to use "#" for one type (US?) and something else for the other (UK?), but i can't find it now... anyway, i'm not displaying those formatted dates (at the moment) so it can stay like that.

in addition (you can see this in my screenshot in my previous post) my date selection is a combobox. its source query is taken from the orders table, so no one has to know inherently what dates exist in this option, they can just select form the available dates (or type one in).

i have last night also added a "no data" check and handler:

Code:

On Error GoTo Err_cmdRunOrderReport_Click
    Dim strMessage As String 'Message on no data for selection.

'(... event code)


Exit_cmdRunOrderReport_Click:
    Exit Sub

Err_cmdRunOrderReport_Click:
    Select Case Err.Number
        Case 2501 [COLOR=Red][B]' triggered by a "no data" event (cancel = true) in the report properties[/B][/COLOR]
            strMessage = "No orders were made by " & Me.cmbPerson.Column(1) & " for this date period."
            MsgBox strMessage, vbOKOnly + vbInformation, "JI order database"
        Case Else
            Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
            MsgBox Msg, vbOKOnly, "JI order database", Err.HelpFile, Err.HelpContext
    End Select
    Resume Exit_cmdRunOrderReport_Click

passing start and end dates and a third criteria to a report query

wiklendt

i recommend chocolate

Attachments

boblarson

Smeghead

wiklendt

i recommend chocolate

Similar threads

Users who are viewing this thread