Query based on two criteria ? (1 Viewer)

[amir0914]

Hello everyone,
I have a query which one field's criteria is refrenced to a form :

[Forms]![frm_InsurancePolicy]![txtInPolicy]

And Also it is needed to refrence to another form, it means I want to use two criterias for the field of the query. (the query is linked to a report which it's possible to create report by two forms)
I solved this problem by making a copy of the query and the report then linking the field to the second form, but I would know if there is an easier way ?

Thanks in advance.

 

[theDBguy]

Hi. Just to make sure we understand what you mean, can you please post the complete SQL statement of both queries for your two reports? Thank you!

 

[amir0914]

to put it simply, I have two forms and one query, I want to reference query criteria to both forms that way if form1 is opened, criteria of query is :

[Forms]![frm_InsurancePolicy]![txtInPolicy]

And if form2 is opened, criteria of query is :

[Forms]![frm_NewInsurance]![txtInsurance]

 

[Gasman]

Is that criteria for the same field?

 

[theDBguy]

to put it simply, I have two forms and one query, I want to reference query criteria to both forms that way if form1 is opened, criteria of query is :

[Forms]![frm_InsurancePolicy]![txtInPolicy]

And if form2 is opened, criteria of query is :

[Forms]![frm_NewInsurance]![txtInsurance]

Unfortunately, to use a form reference in a query criteria, that form has to be open. If it's closed, then the query will produce a parameter prompt. What happens if both forms are open? Would you want the query to be filtered by both open forms? I am thinking you might be able to use a TempVar instead.

 

[plog]

(the query is linked to a report

The solution you are pursuing isn't the best one. You should use DoCmd.OpenReport to solve this:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

You base your report on the query without any criteria. Then you put buttons on both your forms to open that report using DoCmd.OpenReport which allows you to pass criteria with it. You can build your criteria based on whichever form you are using.

 

[amir0914]

The solution you are pursuing isn't the best one. You should use DoCmd.OpenReport to solve this:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

You base your report on the query without any criteria. Then you put buttons on both your forms to open that report using DoCmd.OpenReport which allows you to pass criteria with it. You can build your criteria based on whichever form you are using.

Thank you so much,
As your advices, I removed criteria from the query and carried it out by passing criteria with form to report.