Can a Form Set Up Criteria in a Query

[RFJ]

I have a database of salary survey information (D1) that I use to produce
about twenty customised reports, based on about sixty separate queries.

The report customisation is achieved by selecting subsets of the main data
tables in D1 (eg by company turnover, headcount, etc.) and then running all
the queries and reports on those subsets.

I can set up the subsets by using an initial query, Q1, (where I can put in
the selection criteria) and then running all other queries and reports off
that.

It works fine but I have to enter the criteria into Q1 manually in
query/design.

My question is can I set up a form (QBF seems too limited) where I could
enter the criteria and that form would update the criteria automatically in
Q1. If I can, can you point me in the right direction - my competence level
is low to medium ?

If not, any other ideas would be much appreciated.

TIA

Rob

 

[Allen Browne]

Hi Rob

Could you have the queries refer directly to the text boxes on your form?
For example, you might type something this into the Criteria row of your
query under the SaleDate column:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

Where are are not stacking one query on another, you may be able to just the
WhereCondition of the OpenReport action.

If necessary, you can re-write the SQL of the QueryDef before you
OpenReport:
Dim strSQL As String
strSQL = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSQL

 

[RFJ]

Hi Allen,

Thanks for the quick reply. I quickly got your suggested solution working
just on one criteria - so I understand the concept.

However, I need just one more piece of help. That is, if the text box is
blank then the query returns no records.

What I'd like to be able to do is return all records when the text box is
blank and selected records (meeting the criteria) when the text box includes
the criteria.

Can you help.

TIA again <G>

Rob

Hi Rob

Could you have the queries refer directly to the text boxes on your form?
For example, you might type something this into the Criteria row of your
query under the SaleDate column:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

<cut>

 

[Allen Browne]

There are various ways to achieve that.

If it is a text field, you can use Criteria of:
Is Null Or Like [Forms].[Form1].[Text1] & "*"

The best solution is to use the WhereCondition of the OpenReport action if
you can. Details in this article:
http://members.iinet.net.au/~allenbrowne/casu-08.html
But that does not work for the queries in the subreports, or where you need
to provide grouping on the field.

If you are working with subreports, you may need to write the SQL of the
QueryDef, as per the example in the previous thread. Post back if you want
to go that route and need more help.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Hi Allen,

Thanks for the quick reply. I quickly got your suggested solution working
just on one criteria - so I understand the concept.

However, I need just one more piece of help. That is, if the text box is
blank then the query returns no records.

What I'd like to be able to do is return all records when the text box is
blank and selected records (meeting the criteria) when the text box
includes
the criteria.

Can you help.

TIA again <G>

Rob

Hi Rob

Could you have the queries refer directly to the text boxes on your form?
For example, you might type something this into the Criteria row of your
query under the SaleDate column:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]