Union with IIF - More issues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am trying to integrate a union query with either an IF or IIF (I'm not
sure which I should use)

My statement is currently as follows:

SELECT IIF(Not IsNull([strAltLast]) , [strLast] &", "& [strFirst] & "and " &
[strAltLast] & ", " & [strAltFirst], IIF(Not IsNull([strAltFirst]) ,
[strLast] & ", " & [strFirst] & " and "& [strAltFirst] , [strLast] & ", "&
[strfirst])) AS VoterMember
FROM tblResidential
UNION ALL SELECT tblBusiness.strCompany AS VoterMember,
FROM tblBusiness;

I am now getting the error message "....... Reserved word or argument is
misspelled or incorrect punctuation....."

Can somebody please straighten this out for me??

Thanks
 
The problem appears to be in the 3rd line: " and "& [strAltFirst] ,
[strLast] & ", "&

That should probably be " and "& [strAltFirst] & ", " & [strLast] & ", "&
 
There are 2 problems with the SQL you posted:

* You need a space in front of the "and " in the first line. Otherwise the
word "and" will join with last character of [strFirst].

* The comma at the end of the second last line (before the FROM in the last
line) shouldn't be there. I am sure the error is cuased by this comma..

Try:

********
SELECT IIF(Not IsNull([strAltLast]) ,
[strLast] &", " & [strFirst] & " and " & [strAltLast] & ", " &
[strAltFirst],
IIF(Not IsNull([strAltFirst]) , [strLast] & ", " & [strFirst] & " and "
& [strAltFirst] ,
[strLast] & ", " & [strfirst])) AS VoterMember
FROM tblResidential
UNION ALL

SELECT tblBusiness.strCompany AS VoterMember
FROM tblBusiness;
********

However, if I work out your logic correctly, you should be able to use a
much simpler SQL with the correct operator. Provided that strAltLast and
strAltFirst are Null and not empty String (you didn't check for empty String
in your SQL, anyway), try:

********
SELECT [strLast] & ", " & [strFirst] & ( " and " + ( ( [strAltLast] + ", ")
& [strAltFirst] ) )
AS VoterMember
FROM tblResidential

UNION ALL

SELECT tblBusiness.strCompany AS VoterMember
FROM tblBusiness;
********
 
Absolutely, outstanding!! You nailed it perfectly!

Thanks a million!! ^J^

p.s. Honourable mention to Doug Steele, but hey, Van gave me ALL of the
code!!

Van T. Dinh said:
There are 2 problems with the SQL you posted:

* You need a space in front of the "and " in the first line. Otherwise
the
word "and" will join with last character of [strFirst].

* The comma at the end of the second last line (before the FROM in the
last
line) shouldn't be there. I am sure the error is cuased by this comma..

Try:

********
SELECT IIF(Not IsNull([strAltLast]) ,
[strLast] &", " & [strFirst] & " and " & [strAltLast] & ", " &
[strAltFirst],
IIF(Not IsNull([strAltFirst]) , [strLast] & ", " & [strFirst] & " and "
& [strAltFirst] ,
[strLast] & ", " & [strfirst])) AS VoterMember
FROM tblResidential
UNION ALL

SELECT tblBusiness.strCompany AS VoterMember
FROM tblBusiness;
********

However, if I work out your logic correctly, you should be able to use a
much simpler SQL with the correct operator. Provided that strAltLast and
strAltFirst are Null and not empty String (you didn't check for empty
String
in your SQL, anyway), try:

********
SELECT [strLast] & ", " & [strFirst] & ( " and " + ( ( [strAltLast] + ",
")
& [strAltFirst] ) )
AS VoterMember
FROM tblResidential

UNION ALL

SELECT tblBusiness.strCompany AS VoterMember
FROM tblBusiness;
********

--
HTH
Van T. Dinh
MVP (Access)



CJ said:
Hi

I am trying to integrate a union query with either an IF or IIF (I'm not
sure which I should use)

My statement is currently as follows:

SELECT IIF(Not IsNull([strAltLast]) , [strLast] &", "& [strFirst] & "and
" &
[strAltLast] & ", " & [strAltFirst], IIF(Not IsNull([strAltFirst]) ,
[strLast] & ", " & [strFirst] & " and "& [strAltFirst] , [strLast] & ",
"&
[strfirst])) AS VoterMember
FROM tblResidential
UNION ALL SELECT tblBusiness.strCompany AS VoterMember,
FROM tblBusiness;

I am now getting the error message "....... Reserved word or argument is
misspelled or incorrect punctuation....."

Can somebody please straighten this out for me??

Thanks
Click to expand...
Click to expand...
 
Back
Top