Using IIF

[akcharlie]

This forum is awesome! For the past year now I have been able to find answers to all my Access problems by using the search engine. The issue I'm having now, though, is just not coming up in a way that my limited Access experience is understanding.

I have a query which contains client addresses and phone numbers. Client's can have multiple addresses or phone numbers, but some may be checked as being "primary" via a checkbox.

However, not all entries will have made use of the "primary" checkbox. So, in my query I need to specify that I want the results to show the "primary" address and phone number, but if there is no "primary", show the address that is listed.

I have tried IIF statements that I have found in this forum, but I don't completely understand what it is that I'm typing, so when I'm replacing parts of the statement with my field names, I think I'm screwing it all up and it's just not working.

Can someone please help?

 

[WayneRyan]

Charlie,

IIf([PrimaryCheck], [PrimaryAddress], [SecondaryAddress])

Wayne

 

[KenHigg]

Maybe this will help:

Link

Ken

 

[flixx]

To akcharlie:
You might want to take a look at this mdb sample I built to resolve your
issue. In short what I did was create 2 tables - client table with names
only, address table with multiple addresses and phone and the primary
address box. Then I moved to query editor and created qry_clnt01 just
to see how many records and such and to check everything out. Next I
created the qry_clnt00 to hold just the clients with no duplicates. And
finally I created the query qry_priph which contains the clients, primary
phone list. I have attached the zip mdb file so you can see it in action.
I know there are better ways to do this, so feel free to chirp in.
By the way this was done in msaccess 2000, Hope this helps, Flixx.

 

[akcharlie]

I've got it!

Thank you thank you everyone!

Wayne and Ken--your answers have helped me use the IIF statement in other queries already, but I think flixx hit it on the head with this particular query.

Because my primary_flg field is a checkbox, I was trying to figure in an "IsNull" somewhere in there, but just couldn't get it to work. I'm going to try the flixx fix and will let you all know if I'm still in trouble.

Thanks again, especially for your quick responses!