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
K_DeutschAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 .
0
K_DeutschAuthor 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));
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrahamMandenoCommented:
@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]
0
K_DeutschAuthor Commented:
Good call. Splitting the query into two did the trick.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.