By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

Multiple Option Groups, only reporting when a specific value.

P: 17
I am attempting to create a report off of 6 option groups. Each option group is a Y or N response, populating a 1 or 2 in the tables.
i want to report the number a word on the report, I chose the nested IIF statement below and placed this in my query, but now it opens a dialog box for all except the last choice, is there a way to bypass the dialog boxes or at least not prompt the user with them? There is no values in the table for the ones that it is prompting for, which is correct in this case.


Returns: IIf([Returns_media_bulk_9_trk]=1,"Reel",""IIf([Returns_media_bulk_3480]=1,"3480",IIf([Returns_media_bulk_3490]=1,"3490",IIf([Returns_media_bulk_disk]=1,[“Disk”],IIf([Returns_media_bulk_ftp]=1,[“FTP”],IIf([Returns_media_bulk_EDI]=1,[“EDI”],IIf([Returns_media_bulk_cdr]=1,"")))))))

Thanks.
Jan 24 '08 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
I am attempting to create a report off of 6 option groups. Each option group is a Y or N response, populating a 1 or 2 in the tables.
i want to report the number a word on the report, I chose the nested IIF statement below and placed this in my query, but now it opens a dialog box for all except the last choice, is there a way to bypass the dialog boxes or at least not prompt the user with them? There is no values in the table for the ones that it is prompting for, which is correct in this case.


Returns: IIf([Returns_media_bulk_9_trk]=1,"Reel",""IIf([Returns_media_bulk_3480]=1,"3480",IIf([Returns_media_bulk_3490]=1,"3490",IIf([Returns_media_bulk_disk]=1,[“Disk”],IIf([Returns_media_bulk_ftp]=1,[“FTP”],IIf([Returns_media_bulk_EDI]=1,[“EDI”],IIf([Returns_media_bulk_cdr]=1,"")))))))

Thanks.
Loks like your table isn't properly "normalized", but to solve the problem you could use the NZ function to neutralize the Nulls like:
Expand|Select|Wrap|Line Numbers
  1. Returns: IIf(NZ([Returns_media_bulk_9_trk])=1,"Reel",""IIf(NZ([Returns_media_bulk_3480])=1,"3480",IIf(NZ([Returns_media_bulk_3490])=1,"3490",IIf(NZ([Returns_media_bulk_disk])=1,“Disk”,IIf(NZ([Returns_media_bulk_ftp])=1,“FTP”,IIf(NZ([Returns_media_bulk_EDI])=1,“EDI”,IIf(NZ([Returns_media_bulk_cdr])=1,"")))))))
  2.  
Also some [ and ] are removed as they let Access think there are variables...

Nic;o)
Jan 28 '08 #2

jaxjagfan
Expert 100+
P: 254
I am attempting to create a report off of 6 option groups. Each option group is a Y or N response, populating a 1 or 2 in the tables.
i want to report the number a word on the report, I chose the nested IIF statement below and placed this in my query, but now it opens a dialog box for all except the last choice, is there a way to bypass the dialog boxes or at least not prompt the user with them? There is no values in the table for the ones that it is prompting for, which is correct in this case.


Returns: IIf([Returns_media_bulk_9_trk]=1,"Reel",""IIf([Returns_media_bulk_3480]=1,"3480",IIf([Returns_media_bulk_3490]=1,"3490",IIf([Returns_media_bulk_disk]=1,[“Disk”],IIf([Returns_media_bulk_ftp]=1,[“FTP”],IIf([Returns_media_bulk_EDI]=1,[“EDI”],IIf([Returns_media_bulk_cdr]=1,"")))))))

Thanks.
This gets back to the normalizing thing mentioned.

Would it be possible to have a "MediaType" table within your database?
MediaID, Media, Qty
1, 9_trk, Bulk
2, 3490, Bulk
3, Disk, Bulk
4, FTP, Bulk
5, EDI, Bulk
6, CDR, Bulk

You could have a combobox to drop down and select MediaID.

For a customer order, you would need an order table and order Items table.

It may seem like a lot of work initially (it's really not) but take a look at the data and what you are doing.

FYI - Yes/No, True/False are known as Boolean values. In their numeric form they are stored in tables as -1 for yes/true and 0 as no/false unless you have customized the field for other values.
Jan 28 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.