Avatar of K_Deutsch
K_Deutsch
 asked on

How to correctly use Iif stmt on Firstof Lastof fields

Attached image shows query detail and the Enter Parameter Value prompts I'd like to go away. If I just enter through them, the results are correct. It's just annoying that I have to do so.
Capture.PNG
Microsoft Access

Avatar of undefined
Last Comment
K_Deutsch

8/22/2022 - Mon
Scott McDaniel (EE MVE )

Your IIF statement is wrong - you're looking for a field named "LAST_NAME: LAST_NAME", and one named "LAST_NAME2: LAST_NAME", and neither of those fields exist.

It also doesn't really make any sense - in essence, you're outputting the exact same value for LAST_NAME1 and LAST_NAME2, and you're then comparing them to each other.

If you could tell us what you're trying to achieve, we might be able to help you rewrite the query .
K_Deutsch

ASKER
I had a typo in the Iif statement in my question.

Has to do with a mailing list. Data is grouped by household. Each household has two individuals and thus a FirstofLastName and LastofLastName. If their last names match, nothing should appear in the SPLIT column, meaning they receive one mailing. If the last names do not match, then YES appears in the SPLIT column and the household gets two separate mailings.

The full query as it stands looks like this.

SELECT [02].CIFM_UD_CODE2_2 AS CUST_TYPE, [02].CIFM_HSHLD_NBR AS HSHLD, IIf([LAST_NAME1]=[LAST_NAME2],"","YES") AS [SPLIT?], First([02].NAME1) AS NAME1, Last([02].NAME1) AS NAME2, CIFHSHLD.CIFH_ADDR2 AS NAME3, CIFHSHLD.CIFH_ADDR1 AS NAME4, CIFHSHLD.CIFH_ADDR3 AS ADDRESS, CIFHSHLD.CIFH_CITY AS CITY, CIFHSHLD.CIFH_STATE AS STATE, Left([CIFH_ZIP],5) AS ZIP, First([02].LAST_NAME) AS LAST_NAME1, Last([02].LAST_NAME) AS LAST_NAME2
FROM 02 INNER JOIN CIFHSHLD ON [02].CIFM_HSHLD_NBR = CIFHSHLD.CIFH_HSHLD_NBR
GROUP BY [02].CIFM_UD_CODE2_2, [02].CIFM_HSHLD_NBR, IIf([LAST_NAME1]=[LAST_NAME2],"","YES"), CIFHSHLD.CIFH_ADDR2, CIFHSHLD.CIFH_ADDR1, CIFHSHLD.CIFH_ADDR3, CIFHSHLD.CIFH_CITY, CIFHSHLD.CIFH_STATE, Left([CIFH_ZIP],5), CIFHSHLD.CIFH_ZIP, CIFHSHLD.CIFH_SEQ
HAVING (((CIFHSHLD.CIFH_SEQ)=0) AND ((Count([02].SSN))=2));
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Graham Mandeno

@K_Deutsch

There are two problems here.  One is that First() and Last() will not be reliable because their results will depend on a sort order which has not been defined.  Therefore, if a household has three records, with last names Smith, Jones, and Smith, First() and Last() will potentially both return "Smith".

Instead, you should use Min() and Max().

The other problem is that you are trying to GROUP BY an expression derived from aggregate functions, and this is not allowed.

In query design view, your Split expression should look like this:

Field:       Split: IIf(Min([LAST_NAME])=Max([LAST_NAME]),"","YES")
Total:      Expression

Best wishes,
Graham Mandeno [Access MVP since 1996]
Your help has saved me hundreds of hours of internet surfing.
fblack61
K_Deutsch

ASKER
Good call. Splitting the query into two did the trick.