I have a Access 2007 database I am testing that has a series of states and check box items that are concatenated to get an overall snapshot response for the state. This concatenates the info as a series of -1 and 0 seperated by commas.
I am trying to set up a form with a check box that generates a report based on a query to filter by the check box response. In this case I have it so that if the check box is checked, it shows all states that have at least one check (contains -1), and if it is not checked it shows all nonblank fields.
- =IIF([Forms]![Filter]![Check0]=Yes, Like "*-1*", Not "")
When I generate the report based on the query, if the check box is yes it gives only the states where there is only one record (shows a -1 or a 0) and otherwise it only shows records showing -1. I have ran the sort without the IIF statement, using Like "*-1*" and again for Not "". Both populate correctly. To me this implies the problem is with the IIF statement (or possibly the form), but I cannot find the error. I have even tried reworking using =No, <>No, =-1, =0, <>0 and they react similarly. Any help would be appreciated, thank you.
The attached file is just a basic example with a couple of the columns included and generalized.