Union with IIF - More issues

[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

 

[Douglas J. Steele]

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

That should probably be " and "& [strAltFirst] & ", " & [strLast] & ", "&

 

[Van T. Dinh]

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;
********

 

[CJ]

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!!

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)

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

 

[Douglas J. Steele]

Yeah, he always was a show-off! <g>

 

[Van T. Dinh]

Hey...

I am sure I learnt the + operator in one of your earlier show-offs. <bg>