How can I filter by Dates on a Form?

I have this code behind a Form named ‘ReportForm’:
Option Compare Database
Option Explicit

Private Sub cmdRun_Click()
Dim varItem As Variant
Dim strCust As String

Dim strFilter As String
' Check that the report is open

DoCmd.OpenReport "rptFilter", acViewPreview

' If SysCmd(acSysCmdGetObjectState, acReport, "rptFilter") <>
acObjStateOpen Then
' MsgBox "You must open the report first."
' Exit Sub
' End If
' Build criteria string from lstCust listbox
For Each varItem In Me.lstCust.ItemsSelected
strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
& "'"
Next varItem
If Len(strCust) = 0 Then
strCust = "Like '*'"
Else
strCust = Right(strCust, Len(strCust) - 1)
strCust = "IN(" & strCust & ")"
End If

' Build filter string
strFilter = "[Cust] " & strCust
' Apply the filter and switch it on
With Reports![rptFilter]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptFilter].FilterOn = False
End Sub


The code works great, and allows me to filter the results on the Form by
passing in a variable named ‘strCust’, for Customer. What I’m trying to do
now, is pass dates to the Report, so I can filter by both Dates and Customer.
I want to choose the records BETWEEN the cboFrom date and the cboTo date.

On the Form, I have two ComboBoxes, one named ‘cboFrom’ and the other is
named ‘cboTo’ (both are bound to a Table). How can I modify my VBA so I can
filter by both Dates and Customer?

Thanks so much!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

' Build filter string
strFilter = "[Cust] " & strCust
strFilter = strFilter & " And (MyDateField Between " & _
Nz(Me.cboFrom, #100-01-01#) & " And " & _
Nz(Me.cboTo, #9999-12-31#)

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"ryguy7272" <(E-Mail Removed)> wrote in message
news:54C3104C-A61D-4E9C-A720-(E-Mail Removed)...
> How can I filter by Dates on a Form?
>
> I have this code behind a Form named 'ReportForm':
> Option Compare Database
> Option Explicit
>
> Private Sub cmdRun_Click()
> Dim varItem As Variant
> Dim strCust As String
>
> Dim strFilter As String
> ' Check that the report is open
>
> DoCmd.OpenReport "rptFilter", acViewPreview
>
> ' If SysCmd(acSysCmdGetObjectState, acReport, "rptFilter") <>
> acObjStateOpen Then
> ' MsgBox "You must open the report first."
> ' Exit Sub
> ' End If
> ' Build criteria string from lstCust listbox
> For Each varItem In Me.lstCust.ItemsSelected
> strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
> & "'"
> Next varItem
> If Len(strCust) = 0 Then
> strCust = "Like '*'"
> Else
> strCust = Right(strCust, Len(strCust) - 1)
> strCust = "IN(" & strCust & ")"
> End If
>
> ' Build filter string
> strFilter = "[Cust] " & strCust
> ' Apply the filter and switch it on
> With Reports![rptFilter]
> .Filter = strFilter
> .FilterOn = True
> End With
> End Sub
>
> Private Sub cmdRemoveFilter_Click()
> On Error Resume Next
> ' Switch the filter off
> Reports![rptFilter].FilterOn = False
> End Sub
>
>
> The code works great, and allows me to filter the results on the Form by
> passing in a variable named 'strCust', for Customer. What I'm trying to
> do
> now, is pass dates to the Report, so I can filter by both Dates and
> Customer.
> I want to choose the records BETWEEN the cboFrom date and the cboTo date.
>
> On the Form, I have two ComboBoxes, one named 'cboFrom' and the other is
> named 'cboTo' (both are bound to a Table). How can I modify my VBA so I
> can
> filter by both Dates and Customer?
>
> Thanks so much!
> Ryan--
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.

It's great to receive a response from one of the all time legends of Access.
Thanks for the snippet of code Doug, but I seem to be missing a ‘)’
somewhere. I tried several things, but I didn't get the logic sorted out
yet.

The message I get says:
Missing ), ], or Item in query expression ‘([Cust] IN(‘Colleen’) And
(MyDateFieldBetween 12/01/2009 And 01/29/2010)’

I re-typed that exactly as the message appears. See the extra ‘(’
character. I need a mate for that thing. After several attempts, I can't
seem to get one more ')' in the right place to make this work. Can you see
it Doug?

Thanks!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Douglas J. Steele" wrote:

> ' Build filter string
> strFilter = "[Cust] " & strCust
> strFilter = strFilter & " And (MyDateField Between " & _
> Nz(Me.cboFrom, #100-01-01#) & " And " & _
> Nz(Me.cboTo, #9999-12-31#)
>
> ' Apply the filter and switch it on
> With Reports![rptFilter]
> .Filter = strFilter
> .FilterOn = True
> End With
> End Sub
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:54C3104C-A61D-4E9C-A720-(E-Mail Removed)...
> > How can I filter by Dates on a Form?
> >
> > I have this code behind a Form named 'ReportForm':
> > Option Compare Database
> > Option Explicit
> >
> > Private Sub cmdRun_Click()
> > Dim varItem As Variant
> > Dim strCust As String
> >
> > Dim strFilter As String
> > ' Check that the report is open
> >
> > DoCmd.OpenReport "rptFilter", acViewPreview
> >
> > ' If SysCmd(acSysCmdGetObjectState, acReport, "rptFilter") <>
> > acObjStateOpen Then
> > ' MsgBox "You must open the report first."
> > ' Exit Sub
> > ' End If
> > ' Build criteria string from lstCust listbox
> > For Each varItem In Me.lstCust.ItemsSelected
> > strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
> > & "'"
> > Next varItem
> > If Len(strCust) = 0 Then
> > strCust = "Like '*'"
> > Else
> > strCust = Right(strCust, Len(strCust) - 1)
> > strCust = "IN(" & strCust & ")"
> > End If
> >
> > ' Build filter string
> > strFilter = "[Cust] " & strCust
> > ' Apply the filter and switch it on
> > With Reports![rptFilter]
> > .Filter = strFilter
> > .FilterOn = True
> > End With
> > End Sub
> >
> > Private Sub cmdRemoveFilter_Click()
> > On Error Resume Next
> > ' Switch the filter off
> > Reports![rptFilter].FilterOn = False
> > End Sub
> >
> >
> > The code works great, and allows me to filter the results on the Form by
> > passing in a variable named 'strCust', for Customer. What I'm trying to
> > do
> > now, is pass dates to the Report, so I can filter by both Dates and
> > Customer.
> > I want to choose the records BETWEEN the cboFrom date and the cboTo date.
> >
> > On the Form, I have two ComboBoxes, one named 'cboFrom' and the other is
> > named 'cboTo' (both are bound to a Table). How can I modify my VBA so I
> > can
> > filter by both Dates and Customer?
> >
> > Thanks so much!
> > Ryan--
> >
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> .
>

Oops.

strFilter = strFilter & " And (MyDateField Between " & _
Nz(Me.cboFrom, #100-01-01#) & " And " & _
Nz(Me.cboTo, #9999-12-31#) & ")"

Sorry about that.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"ryguy7272" <(E-Mail Removed)> wrote in message
news40417CC-B661-4D51-BEDC-(E-Mail Removed)...
> It's great to receive a response from one of the all time legends of
> Access.
> Thanks for the snippet of code Doug, but I seem to be missing a ')'
> somewhere. I tried several things, but I didn't get the logic sorted out
> yet.
>
> The message I get says:
> Missing ), ], or Item in query expression '([Cust] IN('Colleen') And
> (MyDateFieldBetween 12/01/2009 And 01/29/2010)'
>
> I re-typed that exactly as the message appears. See the extra '('
> character. I need a mate for that thing. After several attempts, I can't
> seem to get one more ')' in the right place to make this work. Can you
> see
> it Doug?
>
> Thanks!
> Ryan--
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Douglas J. Steele" wrote:
>
>> ' Build filter string
>> strFilter = "[Cust] " & strCust
>> strFilter = strFilter & " And (MyDateField Between " & _
>> Nz(Me.cboFrom, #100-01-01#) & " And " & _
>> Nz(Me.cboTo, #9999-12-31#)
>>
>> ' Apply the filter and switch it on
>> With Reports![rptFilter]
>> .Filter = strFilter
>> .FilterOn = True
>> End With
>> End Sub
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "ryguy7272" <(E-Mail Removed)> wrote in message
>> news:54C3104C-A61D-4E9C-A720-(E-Mail Removed)...
>> > How can I filter by Dates on a Form?
>> >
>> > I have this code behind a Form named 'ReportForm':
>> > Option Compare Database
>> > Option Explicit
>> >
>> > Private Sub cmdRun_Click()
>> > Dim varItem As Variant
>> > Dim strCust As String
>> >
>> > Dim strFilter As String
>> > ' Check that the report is open
>> >
>> > DoCmd.OpenReport "rptFilter", acViewPreview
>> >
>> > ' If SysCmd(acSysCmdGetObjectState, acReport, "rptFilter") <>
>> > acObjStateOpen Then
>> > ' MsgBox "You must open the report first."
>> > ' Exit Sub
>> > ' End If
>> > ' Build criteria string from lstCust listbox
>> > For Each varItem In Me.lstCust.ItemsSelected
>> > strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
>> > & "'"
>> > Next varItem
>> > If Len(strCust) = 0 Then
>> > strCust = "Like '*'"
>> > Else
>> > strCust = Right(strCust, Len(strCust) - 1)
>> > strCust = "IN(" & strCust & ")"
>> > End If
>> >
>> > ' Build filter string
>> > strFilter = "[Cust] " & strCust
>> > ' Apply the filter and switch it on
>> > With Reports![rptFilter]
>> > .Filter = strFilter
>> > .FilterOn = True
>> > End With
>> > End Sub
>> >
>> > Private Sub cmdRemoveFilter_Click()
>> > On Error Resume Next
>> > ' Switch the filter off
>> > Reports![rptFilter].FilterOn = False
>> > End Sub
>> >
>> >
>> > The code works great, and allows me to filter the results on the Form
>> > by
>> > passing in a variable named 'strCust', for Customer. What I'm trying
>> > to
>> > do
>> > now, is pass dates to the Report, so I can filter by both Dates and
>> > Customer.
>> > I want to choose the records BETWEEN the cboFrom date and the cboTo
>> > date.
>> >
>> > On the Form, I have two ComboBoxes, one named 'cboFrom' and the other
>> > is
>> > named 'cboTo' (both are bound to a Table). How can I modify my VBA so
>> > I
>> > can
>> > filter by both Dates and Customer?
>> >
>> > Thanks so much!
>> > Ryan--
>> >
>> >
>> > --
>> > Ryan---
>> > If this information was helpful, please indicate this by clicking
>> > ''Yes''.
>>
>>
>> .
>>

Thanks for posting back Doug! Those pesky quotes always get me. I tried
your solution; unfortunately, this still does not work. It was fine when I
was just filtering for a customer. Now that I'm trying to filter for dates
too...ugh! Nothing works anymore. Here is my code:

Option Compare Database
Option Explicit

Private Sub cmdRun_Click()
Dim varItem As Variant
Dim strCust As String
Dim strTrader As String
Dim strFilter As String
Dim MyDateField As String
' Check that the report is open

DoCmd.OpenReport "rptFilter", acViewPreview

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

' Build filter string
strFilter = "[Cust] " & strCust
strFilter = strFilter & " And (MyDateField Between " & _
Nz(Me.cboFrom, #1/1/2000#) & " And " & _
Nz(Me.cboTo, #12/31/2100#) & ")"

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

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptFilter].FilterOn = False
End Sub

Can you tell what's wrong Doug? I can't see what the problem is. It seems
like it should be working. I suspect the problem is with ‘MyDateField’.
When I run the code, and MsgBox pops up and prompts me ‘enter a parameter
value’. If I enter something like this: 12/05/2009 - 12/15/2009

The Report opens, but he dates are not being filtered. I guess the
parameter ‘MyDateField’ is not being created, or passed, correctly.

What do you think the issue is Doug?

Thanks again!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Douglas J. Steele" wrote:

> Oops.
>
> strFilter = strFilter & " And (MyDateField Between " & _
> Nz(Me.cboFrom, #100-01-01#) & " And " & _
> Nz(Me.cboTo, #9999-12-31#) & ")"
>
> Sorry about that.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news40417CC-B661-4D51-BEDC-(E-Mail Removed)...
> > It's great to receive a response from one of the all time legends of
> > Access.
> > Thanks for the snippet of code Doug, but I seem to be missing a ')'
> > somewhere. I tried several things, but I didn't get the logic sorted out
> > yet.
> >
> > The message I get says:
> > Missing ), ], or Item in query expression '([Cust] IN('Colleen') And
> > (MyDateFieldBetween 12/01/2009 And 01/29/2010)'
> >
> > I re-typed that exactly as the message appears. See the extra '('
> > character. I need a mate for that thing. After several attempts, I can't
> > seem to get one more ')' in the right place to make this work. Can you
> > see
> > it Doug?
> >
> > Thanks!
> > Ryan--
> >
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> ' Build filter string
> >> strFilter = "[Cust] " & strCust
> >> strFilter = strFilter & " And (MyDateField Between " & _
> >> Nz(Me.cboFrom, #100-01-01#) & " And " & _
> >> Nz(Me.cboTo, #9999-12-31#)
> >>
> >> ' Apply the filter and switch it on
> >> With Reports![rptFilter]
> >> .Filter = strFilter
> >> .FilterOn = True
> >> End With
> >> End Sub
> >>
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "ryguy7272" <(E-Mail Removed)> wrote in message
> >> news:54C3104C-A61D-4E9C-A720-(E-Mail Removed)...
> >> > How can I filter by Dates on a Form?
> >> >
> >> > I have this code behind a Form named 'ReportForm':
> >> > Option Compare Database
> >> > Option Explicit
> >> >
> >> > Private Sub cmdRun_Click()
> >> > Dim varItem As Variant
> >> > Dim strCust As String
> >> >
> >> > Dim strFilter As String
> >> > ' Check that the report is open
> >> >
> >> > DoCmd.OpenReport "rptFilter", acViewPreview
> >> >
> >> > ' If SysCmd(acSysCmdGetObjectState, acReport, "rptFilter") <>
> >> > acObjStateOpen Then
> >> > ' MsgBox "You must open the report first."
> >> > ' Exit Sub
> >> > ' End If
> >> > ' Build criteria string from lstCust listbox
> >> > For Each varItem In Me.lstCust.ItemsSelected
> >> > strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
> >> > & "'"
> >> > Next varItem
> >> > If Len(strCust) = 0 Then
> >> > strCust = "Like '*'"
> >> > Else
> >> > strCust = Right(strCust, Len(strCust) - 1)
> >> > strCust = "IN(" & strCust & ")"
> >> > End If
> >> >
> >> > ' Build filter string
> >> > strFilter = "[Cust] " & strCust
> >> > ' Apply the filter and switch it on
> >> > With Reports![rptFilter]
> >> > .Filter = strFilter
> >> > .FilterOn = True
> >> > End With
> >> > End Sub
> >> >
> >> > Private Sub cmdRemoveFilter_Click()
> >> > On Error Resume Next
> >> > ' Switch the filter off
> >> > Reports![rptFilter].FilterOn = False
> >> > End Sub
> >> >
> >> >
> >> > The code works great, and allows me to filter the results on the Form
> >> > by
> >> > passing in a variable named 'strCust', for Customer. What I'm trying
> >> > to
> >> > do
> >> > now, is pass dates to the Report, so I can filter by both Dates and
> >> > Customer.
> >> > I want to choose the records BETWEEN the cboFrom date and the cboTo
> >> > date.
> >> >
> >> > On the Form, I have two ComboBoxes, one named 'cboFrom' and the other
> >> > is
> >> > named 'cboTo' (both are bound to a Table). How can I modify my VBA so
> >> > I
> >> > can
> >> > filter by both Dates and Customer?
> >> >
> >> > Thanks so much!
> >> > Ryan--
> >> >
> >> >
> >> > --
> >> > Ryan---
> >> > If this information was helpful, please indicate this by clicking
> >> > ''Yes''.
> >>
> >>
> >> .
> >>
>
>
> .
>

Before you apply the filter, write it out to the Immediate Window

strFilter = strFilter & " And (MyDateField Between " & _
Nz(Me.cboFrom, #1/1/2000#) & " And " & _
Nz(Me.cboTo, #12/31/2100#) & ")"

Debug.Print strFilter

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

What's in the Immediate Window when you check?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ryguy7272" <(E-Mail Removed)> wrote in message
news:6DE338B2-6B34-4921-8865-(E-Mail Removed)...
> Thanks for posting back Doug! Those pesky quotes always get me. I tried
> your solution; unfortunately, this still does not work. It was fine when
> I
> was just filtering for a customer. Now that I'm trying to filter for
> dates
> too...ugh! Nothing works anymore. Here is my code:
>
> Option Compare Database
> Option Explicit
>
> Private Sub cmdRun_Click()
> Dim varItem As Variant
> Dim strCust As String
> Dim strTrader As String
> Dim strFilter As String
> Dim MyDateField As String
> ' Check that the report is open
>
> DoCmd.OpenReport "rptFilter", acViewPreview
>
> ' Build criteria string from lstCust listbox
> For Each varItem In Me.lstCust.ItemsSelected
> strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
> & "'"
> Next varItem
> If Len(strCust) = 0 Then
> strCust = "Like '*'"
> Else
> strCust = Right(strCust, Len(strCust) - 1)
> strCust = "IN(" & strCust & ")"
> End If
>
> ' Build filter string
> strFilter = "[Cust] " & strCust
> strFilter = strFilter & " And (MyDateField Between " & _
> Nz(Me.cboFrom, #1/1/2000#) & " And " & _
> Nz(Me.cboTo, #12/31/2100#) & ")"
>
> ' Apply the filter and switch it on
> With Reports![rptFilter]
> .Filter = strFilter
> .FilterOn = True
> End With
> End Sub
>
> Private Sub cmdRemoveFilter_Click()
> On Error Resume Next
> ' Switch the filter off
> Reports![rptFilter].FilterOn = False
> End Sub
>
> Can you tell what's wrong Doug? I can't see what the problem is. It
> seems
> like it should be working. I suspect the problem is with ‘MyDateField’.
> When I run the code, and MsgBox pops up and prompts me ‘enter a parameter
> value’. If I enter something like this: 12/05/2009 - 12/15/2009
>
> The Report opens, but he dates are not being filtered. I guess the
> parameter ‘MyDateField’ is not being created, or passed, correctly.
>
> What do you think the issue is Doug?
>
> Thanks again!
> Ryan---
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Douglas J. Steele" wrote:
>
>> Oops.
>>
>> strFilter = strFilter & " And (MyDateField Between " & _
>> Nz(Me.cboFrom, #100-01-01#) & " And " & _
>> Nz(Me.cboTo, #9999-12-31#) & ")"
>>
>> Sorry about that.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "ryguy7272" <(E-Mail Removed)> wrote in message
>> news40417CC-B661-4D51-BEDC-(E-Mail Removed)...
>> > It's great to receive a response from one of the all time legends of
>> > Access.
>> > Thanks for the snippet of code Doug, but I seem to be missing a ')'
>> > somewhere. I tried several things, but I didn't get the logic sorted
>> > out
>> > yet.
>> >
>> > The message I get says:
>> > Missing ), ], or Item in query expression '([Cust] IN('Colleen') And
>> > (MyDateFieldBetween 12/01/2009 And 01/29/2010)'
>> >
>> > I re-typed that exactly as the message appears. See the extra '('
>> > character. I need a mate for that thing. After several attempts, I
>> > can't
>> > seem to get one more ')' in the right place to make this work. Can you
>> > see
>> > it Doug?
>> >
>> > Thanks!
>> > Ryan--
>> >
>> >
>> > --
>> > Ryan---
>> > If this information was helpful, please indicate this by clicking
>> > ''Yes''.
>> >
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> ' Build filter string
>> >> strFilter = "[Cust] " & strCust
>> >> strFilter = strFilter & " And (MyDateField Between " & _
>> >> Nz(Me.cboFrom, #100-01-01#) & " And " & _
>> >> Nz(Me.cboTo, #9999-12-31#)
>> >>
>> >> ' Apply the filter and switch it on
>> >> With Reports![rptFilter]
>> >> .Filter = strFilter
>> >> .FilterOn = True
>> >> End With
>> >> End Sub
>> >>
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no private e-mails, please)
>> >>
>> >>
>> >> "ryguy7272" <(E-Mail Removed)> wrote in message
>> >> news:54C3104C-A61D-4E9C-A720-(E-Mail Removed)...
>> >> > How can I filter by Dates on a Form?
>> >> >
>> >> > I have this code behind a Form named 'ReportForm':
>> >> > Option Compare Database
>> >> > Option Explicit
>> >> >
>> >> > Private Sub cmdRun_Click()
>> >> > Dim varItem As Variant
>> >> > Dim strCust As String
>> >> >
>> >> > Dim strFilter As String
>> >> > ' Check that the report is open
>> >> >
>> >> > DoCmd.OpenReport "rptFilter", acViewPreview
>> >> >
>> >> > ' If SysCmd(acSysCmdGetObjectState, acReport, "rptFilter") <>
>> >> > acObjStateOpen Then
>> >> > ' MsgBox "You must open the report first."
>> >> > ' Exit Sub
>> >> > ' End If
>> >> > ' Build criteria string from lstCust listbox
>> >> > For Each varItem In Me.lstCust.ItemsSelected
>> >> > strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
>> >> > & "'"
>> >> > Next varItem
>> >> > If Len(strCust) = 0 Then
>> >> > strCust = "Like '*'"
>> >> > Else
>> >> > strCust = Right(strCust, Len(strCust) - 1)
>> >> > strCust = "IN(" & strCust & ")"
>> >> > End If
>> >> >
>> >> > ' Build filter string
>> >> > strFilter = "[Cust] " & strCust
>> >> > ' Apply the filter and switch it on
>> >> > With Reports![rptFilter]
>> >> > .Filter = strFilter
>> >> > .FilterOn = True
>> >> > End With
>> >> > End Sub
>> >> >
>> >> > Private Sub cmdRemoveFilter_Click()
>> >> > On Error Resume Next
>> >> > ' Switch the filter off
>> >> > Reports![rptFilter].FilterOn = False
>> >> > End Sub
>> >> >
>> >> >
>> >> > The code works great, and allows me to filter the results on the
>> >> > Form
>> >> > by
>> >> > passing in a variable named 'strCust', for Customer. What I'm
>> >> > trying
>> >> > to
>> >> > do
>> >> > now, is pass dates to the Report, so I can filter by both Dates and
>> >> > Customer.
>> >> > I want to choose the records BETWEEN the cboFrom date and the cboTo
>> >> > date.
>> >> >
>> >> > On the Form, I have two ComboBoxes, one named 'cboFrom' and the
>> >> > other
>> >> > is
>> >> > named 'cboTo' (both are bound to a Table). How can I modify my VBA
>> >> > so
>> >> > I
>> >> > can
>> >> > filter by both Dates and Customer?
>> >> >
>> >> > Thanks so much!
>> >> > Ryan--
>> >> >
>> >> >
>> >> > --
>> >> > Ryan---
>> >> > If this information was helpful, please indicate this by clicking
>> >> > ''Yes''.
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

Thanks for sticking with me Doug! I finally got it worked out. Here is my
final version of code:

Private Sub cmdRun_Click()
Dim varItem As Variant
Dim strCust As String
Dim strTrader As String
Dim strFilter As String
Dim MyDateField As String
' Check that the report is open
' Close the report if it is already open
If SysCmd(acSysCmdGetObjectState, acReport, "rptFilter") =
acObjStateOpen Then
DoCmd.Close acReport, "rptFilter"
End If

DoCmd.OpenReport "rptFilter", acViewPreview

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


' Build filter string
strFilter = "[Cust] " & strCust
strFilter = strFilter & " And Trades.[TDATE] Between
[Forms]![ReportForm]![cboFrom] And [Forms]![ReportForm]![cboTo]"
' Apply the filter and switch it on
With Reports![rptFilter]
..Filter = strFilter
..FilterOn = True
End With
End Sub

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Douglas J. Steele" wrote:

> Before you apply the filter, write it out to the Immediate Window
>
> strFilter = strFilter & " And (MyDateField Between " & _
> Nz(Me.cboFrom, #1/1/2000#) & " And " & _
> Nz(Me.cboTo, #12/31/2100#) & ")"
>
> Debug.Print strFilter
>
> ' Apply the filter and switch it on
> With Reports![rptFilter]
> .Filter = strFilter
> .FilterOn = True
> End With
>
> What's in the Immediate Window when you check?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:6DE338B2-6B34-4921-8865-(E-Mail Removed)...
> > Thanks for posting back Doug! Those pesky quotes always get me. I tried
> > your solution; unfortunately, this still does not work. It was fine when
> > I
> > was just filtering for a customer. Now that I'm trying to filter for
> > dates
> > too...ugh! Nothing works anymore. Here is my code:
> >
> > Option Compare Database
> > Option Explicit
> >
> > Private Sub cmdRun_Click()
> > Dim varItem As Variant
> > Dim strCust As String
> > Dim strTrader As String
> > Dim strFilter As String
> > Dim MyDateField As String
> > ' Check that the report is open
> >
> > DoCmd.OpenReport "rptFilter", acViewPreview
> >
> > ' Build criteria string from lstCust listbox
> > For Each varItem In Me.lstCust.ItemsSelected
> > strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
> > & "'"
> > Next varItem
> > If Len(strCust) = 0 Then
> > strCust = "Like '*'"
> > Else
> > strCust = Right(strCust, Len(strCust) - 1)
> > strCust = "IN(" & strCust & ")"
> > End If
> >
> > ' Build filter string
> > strFilter = "[Cust] " & strCust
> > strFilter = strFilter & " And (MyDateField Between " & _
> > Nz(Me.cboFrom, #1/1/2000#) & " And " & _
> > Nz(Me.cboTo, #12/31/2100#) & ")"
> >
> > ' Apply the filter and switch it on
> > With Reports![rptFilter]
> > .Filter = strFilter
> > .FilterOn = True
> > End With
> > End Sub
> >
> > Private Sub cmdRemoveFilter_Click()
> > On Error Resume Next
> > ' Switch the filter off
> > Reports![rptFilter].FilterOn = False
> > End Sub
> >
> > Can you tell what's wrong Doug? I can't see what the problem is. It
> > seems
> > like it should be working. I suspect the problem is with ‘MyDateField’.
> > When I run the code, and MsgBox pops up and prompts me ‘enter a parameter
> > value’. If I enter something like this: 12/05/2009 - 12/15/2009
> >
> > The Report opens, but he dates are not being filtered. I guess the
> > parameter ‘MyDateField’ is not being created, or passed, correctly.
> >
> > What do you think the issue is Doug?
> >
> > Thanks again!
> > Ryan---
> >
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> Oops.
> >>
> >> strFilter = strFilter & " And (MyDateField Between " & _
> >> Nz(Me.cboFrom, #100-01-01#) & " And " & _
> >> Nz(Me.cboTo, #9999-12-31#) & ")"
> >>
> >> Sorry about that.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "ryguy7272" <(E-Mail Removed)> wrote in message
> >> news40417CC-B661-4D51-BEDC-(E-Mail Removed)...
> >> > It's great to receive a response from one of the all time legends of
> >> > Access.
> >> > Thanks for the snippet of code Doug, but I seem to be missing a ')'
> >> > somewhere. I tried several things, but I didn't get the logic sorted
> >> > out
> >> > yet.
> >> >
> >> > The message I get says:
> >> > Missing ), ], or Item in query expression '([Cust] IN('Colleen') And
> >> > (MyDateFieldBetween 12/01/2009 And 01/29/2010)'
> >> >
> >> > I re-typed that exactly as the message appears. See the extra '('
> >> > character. I need a mate for that thing. After several attempts, I
> >> > can't
> >> > seem to get one more ')' in the right place to make this work. Can you
> >> > see
> >> > it Doug?
> >> >
> >> > Thanks!
> >> > Ryan--
> >> >
> >> >
> >> > --
> >> > Ryan---
> >> > If this information was helpful, please indicate this by clicking
> >> > ''Yes''.
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> ' Build filter string
> >> >> strFilter = "[Cust] " & strCust
> >> >> strFilter = strFilter & " And (MyDateField Between " & _
> >> >> Nz(Me.cboFrom, #100-01-01#) & " And " & _
> >> >> Nz(Me.cboTo, #9999-12-31#)
> >> >>
> >> >> ' Apply the filter and switch it on
> >> >> With Reports![rptFilter]
> >> >> .Filter = strFilter
> >> >> .FilterOn = True
> >> >> End With
> >> >> End Sub
> >> >>
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> "ryguy7272" <(E-Mail Removed)> wrote in message
> >> >> news:54C3104C-A61D-4E9C-A720-(E-Mail Removed)...
> >> >> > How can I filter by Dates on a Form?
> >> >> >
> >> >> > I have this code behind a Form named 'ReportForm':
> >> >> > Option Compare Database
> >> >> > Option Explicit
> >> >> >
> >> >> > Private Sub cmdRun_Click()
> >> >> > Dim varItem As Variant
> >> >> > Dim strCust As String
> >> >> >
> >> >> > Dim strFilter As String
> >> >> > ' Check that the report is open
> >> >> >
> >> >> > DoCmd.OpenReport "rptFilter", acViewPreview
> >> >> >
> >> >> > ' If SysCmd(acSysCmdGetObjectState, acReport, "rptFilter") <>
> >> >> > acObjStateOpen Then
> >> >> > ' MsgBox "You must open the report first."
> >> >> > ' Exit Sub
> >> >> > ' End If
> >> >> > ' Build criteria string from lstCust listbox
> >> >> > For Each varItem In Me.lstCust.ItemsSelected
> >> >> > strCust = strCust & ",'" & Me.lstCust.ItemData(varItem) _
> >> >> > & "'"
> >> >> > Next varItem
> >> >> > If Len(strCust) = 0 Then
> >> >> > strCust = "Like '*'"
> >> >> > Else
> >> >> > strCust = Right(strCust, Len(strCust) - 1)
> >> >> > strCust = "IN(" & strCust & ")"
> >> >> > End If
> >> >> >
> >> >> > ' Build filter string
> >> >> > strFilter = "[Cust] " & strCust
> >> >> > ' Apply the filter and switch it on
> >> >> > With Reports![rptFilter]
> >> >> > .Filter = strFilter
> >> >> > .FilterOn = True
> >> >> > End With
> >> >> > End Sub
> >> >> >
> >> >> > Private Sub cmdRemoveFilter_Click()
> >> >> > On Error Resume Next
> >> >> > ' Switch the filter off
> >> >> > Reports![rptFilter].FilterOn = False
> >> >> > End Sub
> >> >> >
> >> >> >
> >> >> > The code works great, and allows me to filter the results on the
> >> >> > Form
> >> >> > by
> >> >> > passing in a variable named 'strCust', for Customer. What I'm
> >> >> > trying
> >> >> > to
> >> >> > do
> >> >> > now, is pass dates to the Report, so I can filter by both Dates and
> >> >> > Customer.
> >> >> > I want to choose the records BETWEEN the cboFrom date and the cboTo
> >> >> > date.
> >> >> >
> >> >> > On the Form, I have two ComboBoxes, one named 'cboFrom' and the
> >> >> > other
> >> >> > is
> >> >> > named 'cboTo' (both are bound to a Table). How can I modify my VBA
> >> >> > so
> >> >> > I
> >> >> > can
> >> >> > filter by both Dates and Customer?
> >> >> >
> >> >> > Thanks so much!
> >> >> > Ryan--
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Ryan---
> >> >> > If this information was helpful, please indicate this by clicking
> >> >> > ''Yes''.
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
> .
>