How can I filter by Dates on a Form?

R

ryguy7272

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--
 
D

Douglas J. Steele

' 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
 
R

ryguy7272

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 said:
' 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

(no private e-mails, please)


ryguy7272 said:
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--
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

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

(no private e-mails, please)


ryguy7272 said:
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 said:
' 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

(no private e-mails, please)


ryguy7272 said:
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--
Click to expand...


.
Click to expand...
Click to expand...
 
R

ryguy7272

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 said:
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

(no private e-mails, please)


ryguy7272 said:
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 said:
' 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

(no private e-mails, please)


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''.


.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

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

(no e-mails, please!)



ryguy7272 said:
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 said:
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

(no private e-mails, please)


ryguy7272 said:
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''.


:

' 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

(no private e-mails, please)


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''.


.
Click to expand...


.
Click to expand...
Click to expand...
 
R

ryguy7272

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 said:
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

(no e-mails, please!)



ryguy7272 said:
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 said:
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

(no private e-mails, please)


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''.


:

' 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

(no private e-mails, please)


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''.


.



.
Click to expand...
Click to expand...

.
Click to expand...
 

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

Similar Threads

Build Dynamic Query from Form 6
Delete a Query, or Empty it and Reload With New SQL? 3
Query to Filter by Dates, Using Form 7
Filter a Report with a Form 2
Cost History by Customer & by Month. Extending existing code. 0
Error 3085: Undefined function 'N' in expression. - Access 2010 1
Code to Dynamically Filter a Report; Not Working 2
Form linked to Linked Excel Table via ComboBox 10

Top