Expr1 in query keeps prompting

[dcman]

My problem is this:

I have a for form with a button to run a report. The report runs a query. The query reads data on the form to feed the report. The data that is from the table works fine but data from a text box on the form will not. It prompts for EXPR1.

In my query I have the following:

EXPR1: [Cnum]

[Forms]![FRM-Customer]![Cnum]

where FRM-Customer is the form with the button to run the report and Cnum in the text box data that I want to pass to the report. Every time I run it it prompts Cnum?

Any ideas... Thanks in advance

 

[rctjoe24]

Is there a reason why you don't want to use the "Where" or "Filter" parameters in the docmd.OpenReport.

In the next examples, replace rpt_ReportName, with your report name

Example Using the filter (assuming Cnum is a number):

docmd.OpenReport "rpt_ReportName",acViewPreview,"[Cnum] = " & [Forms]![FRM-Customer]![Cnum],,acWindowNormal

Example Using the Where (assuming Cnum is a number):

docmd.OpenReport "rpt_ReportName",acViewPreview,,"[Cnum] = " & [Forms]![FRM-Customer]![Cnum],acWindowNormal

This also means that you don't need EXPR1 in the report's recordsource either. Just make sure CNum is actually a field in the recordsource or else neither of these will work.

 

[dcman]

My reason for not doing it there is that I have 2 variables to filter in the query and my Expr1 is just a text variable on the calling form and is not part of any database.

My report works fine, the only thing it prompts me for Expr1 or should I say Cnum everytime.

Thanks for your reply

 

[JHB]

Hmm - I read you post again, therefore I put this in the top:
Do you want to transfer a value from the control "Cnum" on the form to the query, to use in the report?
If yes, then:

EXPR1: [Forms]![FRM-Customer]![Cnum]

What I first wrote, because I thought it was a criteria you had problem with:
It could be a misspelling of the control name, are you sure the name of the control is "Cnum".
And that you have a field in the table with the name "Cnum"?
It make me a little suspicious that you have "EXPR1: [Cnum]", why do you rename the output column-/fieldname in the query to EXPR1?
Else show the query string and the table structure.

 

[dcman]

Thanks JHB that did it. I recreated Expr1 and used the string you provided. It now works... Thanks for your advice

 

[JHB]

You're welcome - luck with your project.