This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello,
I'm hoping someone here can help me. I have a database which I want to pull reports from. However, new data is added weekly, so I want to filter the data in the report by certain variables. I'm having issues setting the filter up through. I currently have two textboxes, txtBeginDate and txtEndDate, which I want to use to filter a range of dates. I also currently have a combobox named cboDMA to choose a Market. I have a button (btnFilterMarket) which is supposed to run the filters and then open the filtered report. My query which has the data I need filtered is named qryProduction, the form I want to use to filter is named frmFilters, and the report is named rptProductionNoStores.
I've tried a few things, but I just can't get this thing to work...see below for my code as it currently stands:
Private Sub btnFilterMarket_Click()
Dim strFilter As String
If Not IsNull(Me.cboDMA) Then
strFilter = strFilter & " AND qryPRoduction.DMA = '" & Me.cboDMA & "'"
End If
If txtBeginDate <> "" And txtEndDate <> "" Then
strFilter = strFilter & " AND txtBeginDate >= #" & Format(CDate(Me.txtBeginDate), "mm/dd/yyyy") & "# AND txtEndDate <= #" & Format(CDate(Me.txtEndDate), "mm/dd/yyyy") & "#"
End If
If strFilter = "" Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = Mid(strFilter, 5)
Me.FilterOn = True
End If
DoCmd.OpenReport "rptProductionNoStores", acViewPreview, "qryPRoduction"
End Sub
Thank you!
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(284.8, 62%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDH%3C/text%3E%3C/svg%3E)
Sorry I didn't look at your code very thoroughly. Try replace the date part with : If Not IsNull(Me.txtBeginDate) And Not IsNull(Me.txtEndDate) Then strFilter = strFilter & " AND [Your Date Field] BETWEEN #" & _ Format(CDate(Me.txtBeginDate), "mm/dd/yyyy") & _ "# AND #" & Format(CDate(Me.txtEndDate), "mm/dd/yyyy") & "#" End If
Answer accepted by question author
I would change your code to use the WHERE CONDITION of the DoCmd.OpenReport method. The report record source would not contain any of these filters.
Dim strFilter As String strFilter = "1=1 " If Not IsNull(Me.cboDMA) Then strFilter = strFilter & " AND [DMA] = '" & Me.cboDMA & "'" End If If Not IsNull(Me.txtBeginDate) And Not IsNull(Me.txtEndDate) Then strFilter = strFilter & " AND txtBeginDate BETWEEN #" & Format(CDate(Me.txtBeginDate), "mm/dd/yyyy") & _ "# AND txtEndDate <= #" & Format(CDate(Me.txtEndDate), "mm/dd/yyyy") & "#" End If DoCmd.OpenReport "rptProductionNoStores", acViewPreview, , strFilter End Sub
That works! Thank you so much!
Ok, so now I'm getting the Enter Parameter Value error for the Begin Date and the End Date...
Please make sure you don't miss line breaks/wrap issues with my reply :-)