How to set the filter thru VBA

[Alain]

Hi,
I need to open a report based on a date as a filter criteria, the filter
contains 3 date field that need to be checked.
The problem is when I open the report I always get the same error
2451.......report is not open or does not exist,
I do not want to use a criteria at the Query level because it will popup 3
times unless there is a way to apply one input value on three different
field at the same time that I am not aware of, this is the code I am
currently using:
Private Sub Report_Open(Cancel As Integer)
Dim datinput As Date
Dim strFilter As String, strmsg As String
Dim dat1 As Date, dat2 As Date, dat3 As Date

dat1 = Forms!Budget!From
dat2 = Forms!Budget!Exerdaterenoption
dat3 = Forms!Budget!Exerdatecanrights

strmsg = "Veuillez inscrire une date."
datinput = InputBox("Entrez la date de début du budget [MM/DD/YYYY].",
"Budget Date")

'validate if empty
If IsNull(datinput) Then
MsgBox strmsg, vbOKOnly, "Budget Date"
Exit Sub
End If

strFilter = "dat1 <= & #datinput# & OR dat2 <= & #datinput# & OR dat3 <= &
#datinput# &"
With Reports!rptLeaseExpiresBudget
.Filter = strFilter
.FilterOn = True
End With
End Sub

How can I solve the report open problem, I even tried to create a function
to open the report and then applying the filter but no success

TIA

Alain

 

[Allen Browne]

There are a few issues here.

Firstly, it is possible to apply the same parameter 3 times in a query. Open
your query in design view. Choose Parameters on the Query menu. In the
dialog, enter:
datinput Date/Time
Then in the Criteria row under the first date field, enter:
<= [datinput]
and do the same for the other 2 date fields.
Remove the code from the report.
The query should then do the work of filtering the 3 date fields on the
input parameter.

Secondly, it is possible to have the source queries read the date from a
form where you need the date applied multiple times (e.g. to a main report
and a subreport.) In the Criteria row of your query, under the date fields,
you would enter something like this:
[Forms].[Form1].[Text1]
Again, declear this as a parameter in the query.

If you want to do the job with a Filter string, you need to concatenate the
values into the string, so something like this:
conDateFormat = "\#mm\/dd\/yyyy\#
strFilter = "dat1 <= " & Fomat(datinput, conDateFormat) & _
" OR dat2 <= " & Fomat(datinput, conDateFormat) & _
" OR dat3 <= " & Fomat(datinput, conDateFormat)
Note that dat1, dat2, and dat3, need to be the names of the date fields in
the report's RecordSource, not references to items on a form.

 

[Alain]

Thanks Allen

I'll try your suggestion, I am eager to try the filter string since I want
to understand the error 2451 at the form_open

There are a few issues here.

Firstly, it is possible to apply the same parameter 3 times in a query.
Open your query in design view. Choose Parameters on the Query menu. In
the dialog, enter:
datinput Date/Time
Then in the Criteria row under the first date field, enter:
<= [datinput]
and do the same for the other 2 date fields.
Remove the code from the report.
The query should then do the work of filtering the 3 date fields on the
input parameter.

Secondly, it is possible to have the source queries read the date from a
form where you need the date applied multiple times (e.g. to a main report
and a subreport.) In the Criteria row of your query, under the date
fields, you would enter something like this:
[Forms].[Form1].[Text1]
Again, declear this as a parameter in the query.

If you want to do the job with a Filter string, you need to concatenate
the values into the string, so something like this:
conDateFormat = "\#mm\/dd\/yyyy\#
strFilter = "dat1 <= " & Fomat(datinput, conDateFormat) & _
" OR dat2 <= " & Fomat(datinput, conDateFormat) & _
" OR dat3 <= " & Fomat(datinput, conDateFormat)
Note that dat1, dat2, and dat3, need to be the names of the date fields in
the report's RecordSource, not references to items on a form.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I need to open a report based on a date as a filter criteria, the filter
contains 3 date field that need to be checked.
The problem is when I open the report I always get the same error
2451.......report is not open or does not exist,
I do not want to use a criteria at the Query level because it will popup
3 times unless there is a way to apply one input value on three different
field at the same time that I am not aware of, this is the code I am
currently using:
Private Sub Report_Open(Cancel As Integer)
Dim datinput As Date
Dim strFilter As String, strmsg As String
Dim dat1 As Date, dat2 As Date, dat3 As Date

dat1 = Forms!Budget!From
dat2 = Forms!Budget!Exerdaterenoption
dat3 = Forms!Budget!Exerdatecanrights

strmsg = "Veuillez inscrire une date."
datinput = InputBox("Entrez la date de dibut du budget [MM/DD/YYYY].",
"Budget Date")

'validate if empty
If IsNull(datinput) Then
MsgBox strmsg, vbOKOnly, "Budget Date"
Exit Sub
End If

strFilter = "dat1 <= & #datinput# & OR dat2 <= & #datinput# & OR dat3 <=
& #datinput# &"
With Reports!rptLeaseExpiresBudget
.Filter = strFilter
.FilterOn = True
End With
End Sub

How can I solve the report open problem, I even tried to create a
function to open the report and then applying the filter but no success

TIA

Alain