I have code that outputs information for an arts directory and the book designer wanted to know if it was possible to always have a phone number, any phone number in a specific line. In essence, we will prioritize home, work and cell numbers and one appears in the output beside each of the 3 address lines like so:
address 1 tab phone 1
address 2 tab phone 2
address 3 tab phone 3
Phone 1 is not a specific assignement (work, cell) but rather the first available in a prioritized list so if there is home and work, they will take phone 1 & 2, if there is home and cell, *they* will take phone 1 & 2 and if there is work and cell, *they* will take phone 1 & 2.
What I'm thinking is to simply use a query to grab all the available numbers for an existing record and prioritize them using an IIF statement in a calculated field to assign the number 1, 2 & 3. I would then use that query in the code to decide which number goes in which space in the directory.
I can figure out how to do it with a union query but that would take quite a few queries to produce. Is there a simpler way? Am I smarter to do it all at runtime with a recordset?
All the phone numbers would be in the same record together in separate fields. If I'd known they wanted to do this, I might have used subrecords for phone numbers, then the query would be easy.
Hopefully this makes sense as I'm just thinking on my feet right now and thought I might as well ask some clever people how they would do it. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>