uNION QUERY

G

Guest

I have a UNION QUERY where I search for three table with a button control to
activate the query. When I click on the button, parameter pop-up screen
appears and ask for Part #, description and country one at a time. I only
want those three to pop-up one at a time and it work until I added Expiration
Date. Why is there a pop-up parameter screen for expiration date and not
status or registration #. I don't want expiration date parameter to pop-up
from the pop-up screen. Also, is there a way I can put more than one part #
in the parameter pop-up scree (Enter Part #: (35000, 44670, 23233, etc). I
want to be able to enter more than one part # in the pop-up screen using
either comma, or, and to search for more than one part # at once. Thanks.


SELECT [PART_#], DESCRIPTION, [Registration_#], [Expiration Date], COUNTRY,
Status
FROM [Database Table]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description] Like "*"
& [Enter Description:] & "*" and [Country] Like "*" & [Enter Country:] & "*"

UNION ALL

SELECT [PART_#], [Description], [Registration_#], [Expiration Date],
[Country], [Status]
From [US Registration]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description] Like "*"
& [Enter Description:] & "*" and [Country] Like "*" & [Enter Country:] & "*"

UNION ALL SELECT [PART_#], [Description], [Registration_#], [Expiration
Date], [Country], [Status]
From [tblcanada]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description] Like "*"
& [Enter Description:] & "*" and [Country] Like "*" & [Enter Country:] & "*"
ORDER BY country;
 
G

Guest

Hi,

If you part_# field is numeric, you could search using the IN operator like
this:

SELECT [PART_#] FROM [Database Table]
WHERE [PART_#] IN ([Enter Part #:])

Hope this helps.

Damian.
 
D

Dirk Goldgar

"E-mail report using Lotus Notes rather t"
I have a UNION QUERY where I search for three table with a button
control to activate the query. When I click on the button, parameter
pop-up screen appears and ask for Part #, description and country one
at a time. I only want those three to pop-up one at a time and it
work until I added Expiration Date. Why is there a pop-up parameter
screen for expiration date and not status or registration #. I don't
want expiration date parameter to pop-up from the pop-up screen.
Also, is there a way I can put more than one part # in the parameter
pop-up scree (Enter Part #: (35000, 44670, 23233, etc). I want to be
able to enter more than one part # in the pop-up screen using either
comma, or, and to search for more than one part # at once. Thanks.


SELECT [PART_#], DESCRIPTION, [Registration_#], [Expiration Date],
COUNTRY, Status
FROM [Database Table]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description]
Like "*" & [Enter Description:] & "*" and [Country] Like "*" &
[Enter Country:] & "*"

UNION ALL

SELECT [PART_#], [Description], [Registration_#], [Expiration Date],
[Country], [Status]
From [US Registration]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description]
Like "*" & [Enter Description:] & "*" and [Country] Like "*" &
[Enter Country:] & "*"

UNION ALL SELECT [PART_#], [Description], [Registration_#],
[Expiration Date], [Country], [Status]
From [tblcanada]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description]
Like "*" & [Enter Description:] & "*" and [Country] Like "*" &
[Enter Country:] & "*" ORDER BY country;
Click to expand...

The parameter prompt for [Expiration Date] probably means that one or
both of those tables doesn't have a field named "Expiration Date".
Maybe the field is misspelled in the tables, or maybe it exists in one
table or not the other.

If you are entering complete part numbers, not partial ones, in response
to the [Enter Part#:] prompt, then you shouldn't use the Like operator
in the WHERE clause for that field. A simple WHERE [PART_#] = [Enter
Part#:] will do. But if you want to be able to enter multiple part
numbers in the parameter prompt and search for them all, you can use a
trick to accomplish it:

WHERE ("," & Replace([Enter Part#:], " ", "") & ",")
Like ("*," & [PART_#] & ",*")

It's not terribly efficient, but it lets you enter multiple part numbers
at the prompt, separated by commas or by commas and spaces.
 
G

Guest

Damian,

Thanks, but that is not I wanted and my part_# is not numeric because my
company use alphabets a well so I made it "text" rather than numeric for the
column.

Damian S said:
Hi,

If you part_# field is numeric, you could search using the IN operator like
this:

SELECT [PART_#] FROM [Database Table]
WHERE [PART_#] IN ([Enter Part #:])

Hope this helps.

Damian.
E-mail report using Lotus Notes rather t said:
I have a UNION QUERY where I search for three table with a button control to
activate the query. When I click on the button, parameter pop-up screen
appears and ask for Part #, description and country one at a time. I only
want those three to pop-up one at a time and it work until I added Expiration
Date. Why is there a pop-up parameter screen for expiration date and not
status or registration #. I don't want expiration date parameter to pop-up
from the pop-up screen. Also, is there a way I can put more than one part #
in the parameter pop-up scree (Enter Part #: (35000, 44670, 23233, etc). I
want to be able to enter more than one part # in the pop-up screen using
either comma, or, and to search for more than one part # at once. Thanks.


SELECT [PART_#], DESCRIPTION, [Registration_#], [Expiration Date], COUNTRY,
Status
FROM [Database Table]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description] Like "*"
& [Enter Description:] & "*" and [Country] Like "*" & [Enter Country:] & "*"

UNION ALL

SELECT [PART_#], [Description], [Registration_#], [Expiration Date],
[Country], [Status]
From [US Registration]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description] Like "*"
& [Enter Description:] & "*" and [Country] Like "*" & [Enter Country:] & "*"

UNION ALL SELECT [PART_#], [Description], [Registration_#], [Expiration
Date], [Country], [Status]
From [tblcanada]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description] Like "*"
& [Enter Description:] & "*" and [Country] Like "*" & [Enter Country:] & "*"
ORDER BY country;
Click to expand...
Click to expand...
 
G

Guest

Dirk,

I don't think I am doing this right can you give me some pointers as to what
I am doing wrong with your code? When I incorporate your code into my code I
don't think I am doing it right because I am getting "Undefined function
'replace' in expression." As for the "Expiration Date" you are correct that
not all of the tables has "Expiration Date" as a column heading. Is there a
way to rectify this? Thanks a bunch.

SELECT [PART_#], DESCRIPTION, [Registration_#], [Registratoin Date],
COUNTRY, Status
FROM [Database Table]
WHERE ("," & Replace([Enter Part#:], " ", "") & ",") Like ("*," & [PART_#] &
",*") and [Description] Like "*" & [Enter Description:] & "*" and [Country]
Like "*" & [Enter Country:] & "*"
union ALL

SELECT [PART_#], [Description], [Registration_#], [Registratoin Date],
[Country], [Status]
From [US Registration]
WHERE ("," & Replace([Enter Part#:], " ", "") & ",") Like ("*," & [PART_#] &
",*") and [Description] Like "*" & [Enter Description:] & "*" and [Country]
Like "*" & [Enter Country:] & "*"

UNION ALL

SELECT [PART_#], [Description], [Registration_#], [Registratoin Date],
[Country], [Status]
From [tblcanada]
WHERE ("," & Replace([Enter Part#:], " ", "") & ",") Like ("*," & [PART_#] &
",*") and [Description] Like "*" & [Enter Description:] & "*" and [Country]
Like "*" & [Enter Country:] & "*"
ORDER BY country;



Dirk Goldgar said:
"E-mail report using Lotus Notes rather t"
I have a UNION QUERY where I search for three table with a button
control to activate the query. When I click on the button, parameter
pop-up screen appears and ask for Part #, description and country one
at a time. I only want those three to pop-up one at a time and it
work until I added Expiration Date. Why is there a pop-up parameter
screen for expiration date and not status or registration #. I don't
want expiration date parameter to pop-up from the pop-up screen.
Also, is there a way I can put more than one part # in the parameter
pop-up scree (Enter Part #: (35000, 44670, 23233, etc). I want to be
able to enter more than one part # in the pop-up screen using either
comma, or, and to search for more than one part # at once. Thanks.


SELECT [PART_#], DESCRIPTION, [Registration_#], [Expiration Date],
COUNTRY, Status
FROM [Database Table]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description]
Like "*" & [Enter Description:] & "*" and [Country] Like "*" &
[Enter Country:] & "*"

UNION ALL

SELECT [PART_#], [Description], [Registration_#], [Expiration Date],
[Country], [Status]
From [US Registration]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description]
Like "*" & [Enter Description:] & "*" and [Country] Like "*" &
[Enter Country:] & "*"

UNION ALL SELECT [PART_#], [Description], [Registration_#],
[Expiration Date], [Country], [Status]
From [tblcanada]
WHERE [PART_#] Like "*" & [Enter Part #:] & "*" and [Description]
Like "*" & [Enter Description:] & "*" and [Country] Like "*" &
[Enter Country:] & "*" ORDER BY country;
Click to expand...

The parameter prompt for [Expiration Date] probably means that one or
both of those tables doesn't have a field named "Expiration Date".
Maybe the field is misspelled in the tables, or maybe it exists in one
table or not the other.

If you are entering complete part numbers, not partial ones, in response
to the [Enter Part#:] prompt, then you shouldn't use the Like operator
in the WHERE clause for that field. A simple WHERE [PART_#] = [Enter
Part#:] will do. But if you want to be able to enter multiple part
numbers in the parameter prompt and search for them all, you can use a
trick to accomplish it:

WHERE ("," & Replace([Enter Part#:], " ", "") & ",")
Like ("*," & [PART_#] & ",*")

It's not terribly efficient, but it lets you enter multiple part numbers
at the prompt, separated by commas or by commas and spaces.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

"E-mail report using Lotus Notes rather t"
Dirk,

I don't think I am doing this right can you give me some pointers as
to what I am doing wrong with your code? When I incorporate your
code into my code I don't think I am doing it right because I am
getting "Undefined function 'replace' in expression." As for the
"Expiration Date" you are correct that not all of the tables has
"Expiration Date" as a column heading. Is there a way to rectify
this? Thanks a bunch.

SELECT [PART_#], DESCRIPTION, [Registration_#], [Registratoin Date],
COUNTRY, Status
FROM [Database Table]
WHERE ("," & Replace([Enter Part#:], " ", "") & ",") Like ("*," &
[PART_#] & ",*") and [Description] Like "*" & [Enter Description:] &
"*" and [Country] Like "*" & [Enter Country:] & "*"
union ALL

SELECT [PART_#], [Description], [Registration_#], [Registratoin Date],
[Country], [Status]
From [US Registration]
WHERE ("," & Replace([Enter Part#:], " ", "") & ",") Like ("*," &
[PART_#] & ",*") and [Description] Like "*" & [Enter Description:] &
"*" and [Country] Like "*" & [Enter Country:] & "*"

UNION ALL

SELECT [PART_#], [Description], [Registration_#], [Registratoin Date],
[Country], [Status]
From [tblcanada]
WHERE ("," & Replace([Enter Part#:], " ", "") & ",") Like ("*," &
[PART_#] & ",*") and [Description] Like "*" & [Enter Description:] &
"*" and [Country] Like "*" & [Enter Country:] & "*"
ORDER BY country;
Click to expand...

What version of Access are you using? The Replace function wasn't
introduced until Access 2000, and was initially not available in
queries. A later service pack fixed that, but I believe that Jet
sandbox mode, if enabled, still keeps the function from being
recognized. See this Microsoft KnowledgeBase article:

http://support.microsoft.com/kb/294698
How to configure Jet 4.0 to prevent unsafe
functions from running in Access 2003

The article includes instructions for how to disable sandbox mode for
Access applications.

For the tables that don't include a field [Expiration Date], you need to
modify your SELECT statements to define it as a calculated field; e.g.,

SELECT [PART_#], [Description], [Registration_#],
Null As [Expiration Date],
[Country], [Status]
 
D

Dirk Goldgar

"E-mail report using Lotus Notes rather t"
Dirk,

I am not sure if you have problems with this codes but as I test it
more and more I found there is a bug with your code. when I try to
input data into the description or Country it doesn't work and after
that corrpution I can no long use Union Search. The database don't
like it.
Click to expand...

I don't follow you, really, but I don't think union query itself can be
causing any corruption. I don't know what you mean by "when I try to
input data into the description or Country it doesn't work". Where are
you trying to input data into these fields? On a form? In a table or
query datasheet? It can't be in the union query itself, since a union
query is never updatable. "Doesn't work" is not very informative --
doesn't work how? Is there an error message?

You'll need to make the problem a lot clearer before I can offer any
advice.
 

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

Duplicate data SOS. 1
Design and layout for Order Analysis? 5
Sub Query - Easy How to ?? 4
Multiple parameters in a union query 14
Validate Text field in a form. 3
Union Queries 4
Country Drop Down 1
query problem 2

Top