Multiple parameters in a union query

K

Karen

I have the following union query SQL language that combines four tables into
1. I'd like for the user to be prompted to enter a bit of the employee's
name and then to enter the equipment type for the search.

However, when I run the query, the prompts currently read like this:
prompt 1: Enter all or part of employee's name], 1
prompt 2: Enter all or part of employee's name], 3

I'd like for the prompts to read like this:
prompt 1: Enter all or part of employee's name
prompt 2: Enter equipment type

Here's my language:
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Calling Cards]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Cell Phones]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Pagers]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL SELECT [Name], [Equipment Type], [Equipment #], [Bureau],
[Division], [Unit]
FROM [Vehicles]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))
ORDER BY [Name], [Equipment Type];

How do I get the prompts to read like I want. As well, I'm just learning
about union queries. What other uses may it have.
 
K

Karen

That's what I thought...until I read the prompt. As my original enquiry
expressed, the prompts do not show what's in the brackets. It never asks me
the question about the Equipment type but when I enter the equipment type in
the second prompt, it reads it as such.

Does it matter that the equipment type field is a lookup instead of a text
field?
--
Thanks, Karen


KARL DEWEY said:
The prompts are the exact wording you put inside the brackets.
--
KARL DEWEY
Build a little - Test a little


Karen said:
Thanks Karl. However, the prompts still show up without the language as I
wish. Any other suggestions? The search produces the correct results but I
can't figure why the prompts do not ask accurately for the information.
--
Thanks, Karen


KARL DEWEY said:
WHERE [Name] Like "*" & [Enter all or part of employee's name] & "*"
AND [Equipment Type] Like "*" &[Enter equipment type] & "*"

--
KARL DEWEY
Build a little - Test a little


:

I have the following union query SQL language that combines four tables into
1. I'd like for the user to be prompted to enter a bit of the employee's
name and then to enter the equipment type for the search.

However, when I run the query, the prompts currently read like this:
prompt 1: Enter all or part of employee's name], 1
prompt 2: Enter all or part of employee's name], 3

I'd like for the prompts to read like this:
prompt 1: Enter all or part of employee's name
prompt 2: Enter equipment type

Here's my language:
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Calling Cards]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Cell Phones]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Pagers]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL SELECT [Name], [Equipment Type], [Equipment #], [Bureau],
[Division], [Unit]
FROM [Vehicles]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))
ORDER BY [Name], [Equipment Type];

How do I get the prompts to read like I want. As well, I'm just learning
about union queries. What other uses may it have.
Click to expand...
Click to expand...
Click to expand...
 
K

Karen

Thanks Karl. However, the prompts still show up without the language as I
wish. Any other suggestions? The search produces the correct results but I
can't figure why the prompts do not ask accurately for the information.
--
Thanks, Karen


KARL DEWEY said:
WHERE [Name] Like "*" & [Enter all or part of employee's name] & "*"
AND [Equipment Type] Like "*" &[Enter equipment type] & "*"

--
KARL DEWEY
Build a little - Test a little


Karen said:
I have the following union query SQL language that combines four tables into
1. I'd like for the user to be prompted to enter a bit of the employee's
name and then to enter the equipment type for the search.

However, when I run the query, the prompts currently read like this:
prompt 1: Enter all or part of employee's name], 1
prompt 2: Enter all or part of employee's name], 3

I'd like for the prompts to read like this:
prompt 1: Enter all or part of employee's name
prompt 2: Enter equipment type

Here's my language:
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Calling Cards]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Cell Phones]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Pagers]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL SELECT [Name], [Equipment Type], [Equipment #], [Bureau],
[Division], [Unit]
FROM [Vehicles]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))
ORDER BY [Name], [Equipment Type];

How do I get the prompts to read like I want. As well, I'm just learning
about union queries. What other uses may it have.
Click to expand...
Click to expand...
 
K

KARL DEWEY

I donno. Never run across that before.

Maybe post again saying prompt outputting more than it should.
--
KARL DEWEY
Build a little - Test a little


Karen said:
This is a simple thing; I've used prompts many times in ordinary queries and
the prompts asks me the informaton w/in the brackets, exactly as I've typed
it in the brackets.

However, I've never used a union query until today and can't figure out why
the second prompts doesn't ask my user the question that I want. I think
maybe the system doesn't understand what is a field and what is a prompt from
the way that it is written.

It continues to prompt exactly like this:
prompt 1: "Enter all or part of employee's name], 1"
prompt 2: "Enter all or part of employee's name], 3"
--
Thanks, Karen


KARL DEWEY said:
Yes it do. I think Access is looking for what is stored in the field and
not what it is translated to. That is just one of the problems with with
lookups.
You can have a translation table joined in the query and have criteria on
the human side of the translation.
--
KARL DEWEY
Build a little - Test a little


Karen said:
That's what I thought...until I read the prompt. As my original enquiry
expressed, the prompts do not show what's in the brackets. It never asks me
the question about the Equipment type but when I enter the equipment type in
the second prompt, it reads it as such.

Does it matter that the equipment type field is a lookup instead of a text
field?
--
Thanks, Karen


:

The prompts are the exact wording you put inside the brackets.
--
KARL DEWEY
Build a little - Test a little


:

Thanks Karl. However, the prompts still show up without the language as I
wish. Any other suggestions? The search produces the correct results but I
can't figure why the prompts do not ask accurately for the information.
--
Thanks, Karen


:

WHERE [Name] Like "*" & [Enter all or part of employee's name] & "*"
AND [Equipment Type] Like "*" &[Enter equipment type] & "*"

--
KARL DEWEY
Build a little - Test a little


:

I have the following union query SQL language that combines four tables into
1. I'd like for the user to be prompted to enter a bit of the employee's
name and then to enter the equipment type for the search.

However, when I run the query, the prompts currently read like this:
prompt 1: Enter all or part of employee's name], 1
prompt 2: Enter all or part of employee's name], 3

I'd like for the prompts to read like this:
prompt 1: Enter all or part of employee's name
prompt 2: Enter equipment type

Here's my language:
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Calling Cards]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Cell Phones]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL
SELECT [Name], [Equipment Type], [Equipment #], [Bureau], [Division], [Unit]
FROM [Pagers]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))

UNION ALL SELECT [Name], [Equipment Type], [Equipment #], [Bureau],
[Division], [Unit]
FROM [Vehicles]
WHERE (([Name] Like "*" & [Enter all or part of employee's name] & "*")
AND ([Equipment Type]=[Enter equipment type]))
ORDER BY [Name], [Equipment Type];

How do I get the prompts to read like I want. As well, I'm just learning
about union queries. What other uses may it have.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Karen,

I just tested the SQL statement that you included in your initial posting. I
did this by copying the Employees table (structure + data) to a new table
named Cell Phones. Then I renamed some fields to match your fieldnames, for
example LastName--->Name, Title ---> Equipment Type, etc. I deleted left over
fields. Then I copied this table + data three more times, producing tables
with the appropriate names. The SQL statement that you provided prompted me
as expected, ie:

First prompt: "Enter all or part of employee's name" and
Second prompt: "Enter equipment type"

As Karl indicates, lookup fields defined at the table (or query) level can
be problematic. In fact, lookup fields art thy creation of thy evil one. See
the Second Commandment of Access, here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Without having your actual database available, I cannot say for sure that
the lookup field is causing you this grief, but they cause enough other
problems that you really should perform an exocism to remove them.

Also, while your choice of field names did not seem to cause a problem this
time, when I ran my test, you are using reserved words and special
characters. For example, "Name" is considered a reserved word in Access.

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
B

Bob Quintal

This is a simple thing; I've used prompts many times in
ordinary queries and the prompts asks me the informaton w/in
the brackets, exactly as I've typed it in the brackets.

However, I've never used a union query until today and can't
figure out why the second prompts doesn't ask my user the
question that I want. I think maybe the system doesn't
understand what is a field and what is a prompt from the way
that it is written.

It continues to prompt exactly like this:
prompt 1: "Enter all or part of employee's name], 1"
prompt 2: "Enter all or part of employee's name], 3"
Click to expand...

you might try to explicitly define the parameters.
Put the following into the query as the first line
Parameters [Enter all or part of employee's name] text(255)
[Enter equipment type] text(255);
 
K

Karen

Bob Quintal said:
This is a simple thing; I've used prompts many times in
ordinary queries and the prompts asks me the informaton w/in
the brackets, exactly as I've typed it in the brackets.

However, I've never used a union query until today and can't
figure out why the second prompts doesn't ask my user the
question that I want. I think maybe the system doesn't
understand what is a field and what is a prompt from the way
that it is written.

It continues to prompt exactly like this:
prompt 1: "Enter all or part of employee's name], 1"
prompt 2: "Enter all or part of employee's name], 3"
Click to expand...

you might try to explicitly define the parameters.
Put the following into the query as the first line
Parameters [Enter all or part of employee's name] text(255)
[Enter equipment type] text(255);
Click to expand...
I tried this also. However, when I select Query, then try to choose
Parameters to type in your recommendations, Parameters is greyed out. It's
not greyed out in the oridinary queries however. Just in the union.
 
B

Bob Quintal

Bob Quintal said:
This is a simple thing; I've used prompts many times in
ordinary queries and the prompts asks me the informaton
w/in the brackets, exactly as I've typed it in the
brackets.

However, I've never used a union query until today and
can't figure out why the second prompts doesn't ask my user
the question that I want. I think maybe the system doesn't
understand what is a field and what is a prompt from the
way that it is written.

It continues to prompt exactly like this:
prompt 1: "Enter all or part of employee's name], 1"
prompt 2: "Enter all or part of employee's name], 3"
Click to expand...

you might try to explicitly define the parameters.
Put the following into the query as the first line
Parameters [Enter all or part of employee's name] text(255)
[Enter equipment type] text(255);
Click to expand...
I tried this also. However, when I select Query, then try to
choose Parameters to type in your recommendations, Parameters
is greyed out. It's not greyed out in the oridinary queries
however. Just in the union.
Click to expand...

You can't edit a union query in the query builder. Using the
parameters dialog depends on the builder. You can type them in
manually. or even build one query, set the parameters there, go
to SQL View and cut and paste the parameters line into the union
query.
 
K

Karen

I wrote a regular query on one of the tables and looked at its SQL. Here it
is:

SELECT [Calling Cards].[Name], [Calling Cards].[Equipment #], [Calling
Cards].[Equipment Type], [Calling Cards].[Bureau], [Calling
Cards].[Division], [Calling Cards].[Unit]
FROM [Calling Cards]
WHERE ((([Calling Cards].[Name]) Like "*" & [Enter all or part of employee's
name] & "*") And (([Calling Cards].[Equipment Type]) Like "*" & [Enter
equipment type] & "*"));

It is exactly what I have in my union query. Oddly enough, when I run this
query, its prompts look queer too. They appear like the ones in my union
query. When I put the parameters in, it included the word "Text" as part of
the prompt even though I selected it as data type.

--
Thanks, Karen


Bob Quintal said:
Bob Quintal said:
in
This is a simple thing; I've used prompts many times in
ordinary queries and the prompts asks me the informaton
w/in the brackets, exactly as I've typed it in the
brackets.

However, I've never used a union query until today and
can't figure out why the second prompts doesn't ask my user
the question that I want. I think maybe the system doesn't
understand what is a field and what is a prompt from the
way that it is written.

It continues to prompt exactly like this:
prompt 1: "Enter all or part of employee's name], 1"
prompt 2: "Enter all or part of employee's name], 3"

you might try to explicitly define the parameters.
Put the following into the query as the first line
Parameters [Enter all or part of employee's name] text(255)
[Enter equipment type] text(255);
Click to expand...
I tried this also. However, when I select Query, then try to
choose Parameters to type in your recommendations, Parameters
is greyed out. It's not greyed out in the oridinary queries
however. Just in the union.
Click to expand...

You can't edit a union query in the query builder. Using the
parameters dialog depends on the builder. You can type them in
manually. or even build one query, set the parameters there, go
to SQL View and cut and paste the parameters line into the union
query.
Click to expand...
 
T

Tom Wickerath

Sure. There is a contact e-mail address shown for me at the bottom of the
contributors.html page indicated below, in my signature. Use the same subject
at this thread, ie. "Multiple parameters in a union query". Please compact
your database first (Tools > Database Utilities > Compact and Repair
Database) and then add it to a .zip file.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
K

Karen

Hi Tom,
My computer is very odd...I just prepared the query to send the database to
your attention. What do you know, it actually prompts for the information as
I wish. Maybe it just needed a break.

Thanks for your efforts.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Chart on form from selected data in query. 0
Office 365 Calender 0
Changing SQL to include a second building 2
How can I query only records that have multiple associated records 3
Need formulas for searching for a value in a table and returning row/column name 7
Query to return latest dates 5
Starting a new database 8
Carrying a value between 2 tables 3

Top