UNION query with WHERE clause

[GregoireLeGros]

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]

What do you mean by "doesn't work"? Each SELECT statement stands alone, so each needs the WHERE clause.

 

[GregoireLeGros]

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]

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:

(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.

 

[GregoireLeGros]

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]

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
​