Filter report by beginning and ending dates



I have a form button containing code that filters my report. I have a
multi-select list box and option group with the code below that works great.
I also have BeginDate and EndDate controls on my form that I need to use to
filter the form and I'm not sure how to write the VBA to do this. I'm
assuming I start with Dim datBeginDate As Date and Dim datEndDate As Date,
but I'm not sure how to write the rest of the code that will filter my report
with the date paramenter typed in my form. Can someone please help? Thanks.

' Build criteria string from StoreRoom multi-select listbox
For Each varItem In Me.cmdStoreRoom.ItemsSelected
strStore = strStore & ",'" & Me.cmdStoreRoom.ItemData(varItem) _
& "'"
Next varItem
If Len(strStore) = 0 Then
strStore = "Like '*'"
strStore = Right(strStore, Len(strStore) - 1)
strStore = "IN(" & strStore & ")"
End If

' Build criteria string from Location option group
Select Case Me.cmdLocation.Value
Case 1
strLocation = "='1'"
Case 2
strLocation = "='2'"

End Select

' Build filter string
strFilter = "[StorageRoom] " & strStore & " AND [Location] " & strLocation

' Apply the filter and switch it on
With Reports![LocationsReportbyStorage]
.Filter = strFilter
.FilterOn = True
End With


Try that
strFilter = "[StorageRoom] " & strStore & " AND [Location] " & strLocation
If not isnull(me.BeginDate) then
strFilter = strFilter & " AND BeginDate >= #" & me.BeginDate & "#"
If not isnull(me.EndDate) then
strFilter = strFilter & " AND EndDate <= #" & me.EndDate & "#"

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question