openreport filter is being ignored

  • Thread starter Thread starter keers
  • Start date Start date
K

keers

Hi All,

I have a form which calculates a date (in the format of mmm yy),I want
to use this date to apply a filter to a query in order to produce the
report. The relevent field in the query is evaluated;

Terminates: IIf(IsNull([DelDateActual]),"No Delivery
Date",Format(DateAdd("m",[Term],[DelDateActual]),"mmm yy"))

The code in my form is;

Private Sub btnRenewals_Click()
Dim strWhere As String
Dim stDocName As String

On Error GoTo Err_btnRenewals_Click
strWhere = """Terminates =" & hdnRenewal.Value & """"
MsgBox strWhere
stDocName = "RepRenewalLetters"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_btnRenewals_Click:
Exit Sub

Err_btnRenewals_Click:
MsgBox Err.Description
Resume Exit_btnRenewals_Click

End Sub


The output from the evaluation being displayed by the msgbox looks
correct to me i.e.

"Terminates = sep 07"

However, the filter is being ignored. If i attempt to filter on a
different field i.e. Surname - the filter works.

Any pointers would be greatly appreciated.


Regards

Keers
 
keers said:
I have a form which calculates a date (in the format of mmm yy),I want
to use this date to apply a filter to a query in order to produce the
report. The relevent field in the query is evaluated;

Terminates: IIf(IsNull([DelDateActual]),"No Delivery
Date",Format(DateAdd("m",[Term],[DelDateActual]),"mmm yy"))

The code in my form is;

Private Sub btnRenewals_Click()
Dim strWhere As String
Dim stDocName As String

On Error GoTo Err_btnRenewals_Click
strWhere = """Terminates =" & hdnRenewal.Value & """" [snip]

The output from the evaluation being displayed by the msgbox looks
correct to me i.e.

"Terminates = sep 07"
Click to expand...


Your quotes are out of whack. Use

strWhere = "Terminates =""" & hdnRenewal.Value & """"

to make the result be:

Terminates = "sep 07"
 
keers said:
I have a form which calculates a date (in the format of mmm yy),I want
to use this date to apply a filter to a query in order to produce the
report. The relevent field in the query is evaluated;
Click to expand...
Terminates: IIf(IsNull([DelDateActual]),"No Delivery
Date",Format(DateAdd("m",[Term],[DelDateActual]),"mmm yy"))
Click to expand...
The code in my form is;
Click to expand...
Private Sub btnRenewals_Click()
Dim strWhere As String
Dim stDocName As String
Click to expand...
On Error GoTo Err_btnRenewals_Click
strWhere = """Terminates =" & hdnRenewal.Value & """" [snip]

The output from the evaluation being displayed by the msgbox looks
correct to me i.e.
Click to expand...
"Terminates = sep 07"
Click to expand...

Your quotes are out of whack. Use

strWhere = "Terminates =""" & hdnRenewal.Value & """"

to make the result be:

Terminates = "sep 07"

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Marsh,

thanks for the response. It turns out that this was only part of the
problem. The report is still asking for the date, though I have now
narrowed down the problem. The query I use to produce this report is
also used by another report and it has 'criteria' specified - which
prompts the user for the date - so it looks like I need to override
this. The 'criteria' for the [Terminate] field is;

[Enter Termination Date (eg: sep 06)]

is it possilbe to override this or do i just create a new query
without 'criteria' being specified for the new report?

Thanks for your time.

Kiers
 
keers said:
thanks for the response. It turns out that this was only part of the
problem. The report is still asking for the date, though I have now
narrowed down the problem. The query I use to produce this report is
also used by another report and it has 'criteria' specified - which
prompts the user for the date - so it looks like I need to override
this. The 'criteria' for the [Terminate] field is;

[Enter Termination Date (eg: sep 06)]

is it possilbe to override this or do i just create a new query
without 'criteria' being specified for the new report?
Click to expand...


No, you either need another query without the prompt
criteria or you should do the other report differently.

Since I think the quick and dirty query prompts should never
be used in a real application, I vote for the latter
approach. Remove the prompt criteria from the query you
have now and use a form to open that report too (using the
same techique as the report we have been discussing. You
might even want to use the same form for both reports, just
different command buttons.
 
Back
Top