Combining queries

R

Ron

Ive made two queries that I run with macros in my form, they both work fine
however if i use one query it removes the filter applied by the other, I
wonder if anyone could help me create a toggle button that allows multiple
queries to be applied or how I should go about creating queries that will
allow me to overlay them to help search for the record I am after. Any help
appreciated
 
J

John Vinson

Ive made two queries that I run with macros in my form, they both work fine
however if i use one query it removes the filter applied by the other, I
wonder if anyone could help me create a toggle button that allows multiple
queries to be applied or how I should go about creating queries that will
allow me to overlay them to help search for the record I am after. Any help
appreciated
Click to expand...

I don't understand how a query "removes the filter" applied by the
other, and I don't really see what these queries are doing or how.

Could you please open the two queries in SQL view and post the SQL
text here; explain what they're intended to accomplish; and describe
what you mean by "overlay them"?

John W. Vinson[MVP]
 
R

Ron

SELECT [Customer List].[Surname/Company Name]
FROM [Customer List]
WHERE ((([Customer List].[Surname/Company Name]) Like "*a"))
ORDER BY [Customer List].[Surname/Company Name];

and

SELECT [Customer List].County, [Customer List].Company
FROM [Customer List]
WHERE ((([Customer List].County)="Norfolk"))
ORDER BY [Customer List].County, [Customer List].Company;

Are two examples but i've realised I mean there are two macros that run many
queries, and by "overlay" I mean that I want to apply both queries to the
form at the same time, e.g have show results which begin with A AND are in
Norfolk for instance. It would also be helpful if I could toggle this effect
but permanently on would suffice.

Also the macro's use the ApplyFilter action if you needed to know
 
J

John Vinson

SELECT [Customer List].[Surname/Company Name]
FROM [Customer List]
WHERE ((([Customer List].[Surname/Company Name]) Like "*a"))
ORDER BY [Customer List].[Surname/Company Name];

and

SELECT [Customer List].County, [Customer List].Company
FROM [Customer List]
WHERE ((([Customer List].County)="Norfolk"))
ORDER BY [Customer List].County, [Customer List].Company;

Are two examples but i've realised I mean there are two macros that run many
queries, and by "overlay" I mean that I want to apply both queries to the
form at the same time, e.g have show results which begin with A AND are in
Norfolk for instance. It would also be helpful if I could toggle this effect
but permanently on would suffice.

Also the macro's use the ApplyFilter action if you needed to know
Click to expand...

Well...

A couple of things here.

Macros are very limited and hard to use for much beyond the basics.

Query datasheets are even MORE limited; you should probably not view
data on a datasheet at all! A Form gives you much more control and
more flexibility.

Finally - a separate Query for each criterion is really not a good
idea. I would suggest using a Parameter Query instead.

Create a Form, let's call it frmCrit, with controls such as a textbox
txtCompany, labeled "Enter part of company name:", and combo box
cboCounty, labeled "Select county or leave blank for all:"

Use a query

SELECT [Customer List].County, [Customer List].Company
FROM [Customer List]
WHERE [Customer List].County=[Forms]![frmCrit]!cboCounty]
OR [Forms]![frmCrit]!cboCounty] IS NULL
AND [Customer List].[Company] LIKE "*" &
[Forms]![frmCrit]![txtCompany] & "*"
ORDER BY [Customer List].County, [Customer List].Company;

Base a Form (for onscreen display) or a Report (for printing) on this
query - or both! Put a command button on frmCrit to launch the form or
report.

The user can then select a county (or not), type in part of or a full
company name (or leave it blank), click a button, and see a neatly
formatted list of the information you choose, laid out as you wish it
to be seen.

John W. Vinson[MVP]
 
Top