openreport filter is being ignored

[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

 

[Marshall Barton]

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"

Your quotes are out of whack. Use

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

to make the result be:

Terminates = "sep 07"

 

[keers]

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"

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 -

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

 

[Marshall Barton]

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?

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.