Multiple Option Groups, only reporting when a specific value.

[ckrows]

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.

[nico5038]

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:

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,"")))))))
 

Also some [ and ] are removed as they let Access think there are variables...

Nic;o)

[jaxjagfan]

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.