How to correctly use Iif stmt on Firstof Lastof fields

K_Deutsch
K_Deutsch used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
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 .

Author

Commented:
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));
Most Valuable Expert 2012
Top Expert 2014
Commented:
I'd suggest you remove that IIF statement, and then save that query in the database (name it something like "qryMailingRoot"). This will create a query that has those "Last_Name1" and "Last_Name2" fields, and you can more easily filter for that.

Then, create another query that actually pulls the data you need, and use this:

SELECT IIF(Last_Name1<>Last_Name2, "", "Yes") AS [SPLIT}, * FROM qryMailingRoot"
@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]

Author

Commented:
Good call. Splitting the query into two did the trick.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial