Link to home
Create AccountLog in
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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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 .
Avatar of K_Deutsch
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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
@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]
Good call. Splitting the query into two did the trick.