CODEWITHSUNDEEP
ms-accessvba
R. Kiner
R. Kiner

Reputation: 13

Adding Combobox selection to existing vba to filter on date range and cbovalue

I am at a loss and don't know where else to go to try to get assistance. Here is what I have put together. Most of this is for the date filtering. If a start date and an end date are entered, the records for that range are supplied. If a start date is entered by no end date, records are pulled from that date forward. If an end date is entered but no start date, all records are pulled up to the end date. If no dates are entered the entire universe is retrieved. The section that I am having trouble with the selected value from a combobox that I have added. The combo box is based off of a query that pulls in each unique id (such as 00001, 00073...) and based on the dates entered and the id selected I want to retrieve and report on just those specific records. The way that it is below returns the data for the unique id selected but the date range is ignored. I have tried quite a few avenues and I also attempted to add an if statement for Me.Client_List.Value, but I don't know what I am doing wrong with that either. I have to admit I have not been using access for quite a few years and I am feeling lost.

thank you to anyone that can assist me. -Rick

My combobox name is Client_List and the value to be filtered on is Client ID

attempted If statement: 

If Me.Client_List.Value = "Client ID" Then
    strWhere = strWhere & "([Client ID] = True) AND"
    ElseIf Me.Client_List.Value = "" Then
    strWhere = strWhere & "([Client ID] = False) AND "
End If

VBA that works to include the combobox selection on the reportview but the date filters are being ignored:

Private Sub RunReport_2_button_Click()
On Error GoTo Err_Handler
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"

    strReport = "Audits by DOS_Client"
    strDateField = "[Date of Service]"
    lngView = acViewPreview

    If IsDate(Me.dos_start) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.dos_start, strcJetDate) & ")"
    End If
    If IsDate(Me.dos_end) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.dos_end + 1, strcJetDate) & ")"
    End If

    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If

    Debug.Print strWhere
    DoCmd.OpenReport "Audits by DOS_Client", acViewPreview, , "[Client Id] = '" & Me.Client_List.Value & "'"

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

Upvotes: 0

Views: 166

Answers (1)

Gustav
Gustav

Reputation: 55841

Well, it could be:

If strWhere <> "" Then
    strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[Client Id] = '" & Me.Client_List.Value & "'"

Debug.Print strWhere

DoCmd.OpenReport "Audits by DOS_Client", acViewPreview, , strWhere

Upvotes: 0

Related Questions