UNION query with WHERE clause (1 Viewer)

GregoireLeGros

Registered User.
Local time
Today, 15:13
Joined
Oct 31, 2007
Messages
13
I have a table including a personkey, emailaddress1 and emailaddress2.
On my form I wish to have a combo box where I can select which email address to use. I would like to build a union query as the row source for the combo box.
So far I have

SELECT tblPeople.PersonalEmail1
FROM tblPeople
UNION SELECT tblPeople.PersonalEmail2
WHERE (((tblPeople.PersonKey)=2226))
FROM tblPeople;

which doesnt work. If I leave out the where clause then the query returns every email address from every record. I want only email addresses belonging to person 2226. Any help appreciated.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:13
Joined
Aug 30, 2003
Messages
36,118
What do you mean by "doesn't work"? Each SELECT statement stands alone, so each needs the WHERE clause.
 

GregoireLeGros

Registered User.
Local time
Today, 15:13
Joined
Oct 31, 2007
Messages
13
Thank you for that, it was a bit vague. Union query is now sorted and all I have to do is remember how to pass back the value of the txtPersonKey in my form to the query. I find I have to work it out from first principles every time . I dont do it often enough for it to become second nature !So I got this far

SELECT tblPeople.PersonalEmail1
FROM tblPeople
WHERE [tblpeople.PersonKey]= " & chr(34) & me.txtPersonKey.value & chr(34) & "
UNION SELECT tblPeople.PersonalEmail2
FROM tblPeople
WHERE [tblPeople.PersonKey]= " & chr(34) & me.txtPersonKey.value & chr(34) & " ';

Greg
 

MSAccessRookie

AWF VIP
Local time
Today, 11:13
Joined
May 2, 2008
Messages
3,428
I have a table including a personkey, emailaddress1 and emailaddress2.
On my form I wish to have a combo box where I can select which email address to use. I would like to build a union query as the row source for the combo box.
So far I have

SELECT tblPeople.PersonalEmail1
FROM tblPeople
UNION SELECT tblPeople.PersonalEmail2
WHERE (((tblPeople.PersonKey)=2226))
FROM tblPeople;

which doesnt work. If I leave out the where clause then the query returns every email address from every record. I want only email addresses belonging to person 2226. Any help appreciated.

I think you might have the WHERE clause in the Wrong place. It needs to be After the UNION has been created. Try this:
Code:
(SELECT tblPeople.PersonalEmail1
FROM tblPeople
UNION SELECT tblPeople.PersonalEmail2
FROM tblPeople)
[COLOR=green][B]WHERE (((tblPeople.PersonKey)=2226))[/B][/COLOR][COLOR=#000000];[/COLOR]

Note that this is "Air Code" and as such is untested.
 
Last edited:

GregoireLeGros

Registered User.
Local time
Today, 15:13
Joined
Oct 31, 2007
Messages
13
Thanks for your interest in this guys. Now the UNION query works fine when I hard code it. Next step is to pass the value of the txtPersonKey on my form into the query and I'm having a whole lot of rigmarole with syntax and quotes and single quotes etc.
This statement is the row source for a combo box on the form

SELECT tblPeople.PersonalEmail1
FROM tblPeople
WHERE [tblpeople.PersonKey]=' " & me.txtPersonKey.value & " ' "
UNION SELECT tblPeople.PersonalEmail2
FROM tblPeople
WHERE [tblPeople.PersonKey]=' " & me.txtPersonKey.value & " ';"

and now I get a data type mismatch but I think that message is hiding a bad syntax.
 

MSAccessRookie

AWF VIP
Local time
Today, 11:13
Joined
May 2, 2008
Messages
3,428
Thanks for your interest in this guys. Now the UNION query works fine when I hard code it. Next step is to pass the value of the txtPersonKey on my form into the query and I'm having a whole lot of rigmarole with syntax and quotes and single quotes etc.
This statement is the row source for a combo box on the form

SELECT tblPeople.PersonalEmail1
FROM tblPeople
WHERE [tblpeople.PersonKey]=' " & me.txtPersonKey.value & " ' "
UNION SELECT tblPeople.PersonalEmail2
FROM tblPeople
WHERE [tblPeople.PersonKey]=' " & me.txtPersonKey.value & " ';"

and now I get a data type mismatch but I think that message is hiding a bad syntax.

The Syntax that you are using here works ONLY when it is in VB Code. If this is in a Query, then an alternate Syntax is required.
Forms!{YourFormName}!{txtPersonKey}

Will Replace

me.txtPersonKey.value
 

Users who are viewing this thread

Top Bottom