Nested IIf nightmare

[nemo700]

Poor old Access. I think I've made it wave a white flag, but based on the following expression I am trying to get it to evaluate, I'm not too surprised...

The situation is that I have a large number of tick boxes on a form, which nicely filter through to a series of fields in a table whose values are either -1 or 0 (according to if the box is ticked or not). Unfortunately, I now need to generate a small piece of text for a report based on which boxes have been ticked. Deep breath...

institution:
IIf([people].[memctf]=-1,"CTF",(
IIf([people].[memwt]=-1,"Westcott",(
Iif([people].[memwy]=-1,"Wesley",(
Iif([people].[memry]=-1,"Ridley",(
Iif([people].[memwm]=-1,"Westminster",(
Iif([people].[memiocs]=-1,"IOCS",(
Iif([people].[memermc]=-1,"ERMC",(
Iif([people].[memmbi]=-1,"Margaret Beaufort Institute",(
Iif([people].[memcjcr]=-1,"CJCR",(
Iif([people].[memcym]=-1,"CYM",(
Iif([people].[memindepba]=-1,"Independent BA",(
Iif([people].[memhmc]=-1,"Henry Martyn Centre",(
Iif([people].[memregchelm]=-1,"Regional (Chelmsford)",(
Iif([people].[memregnor]=-1,"Regional (Norwich)",(
Iif([people].[memregpboro]=-1,"Regional (Chelmsford)",(
Iif([people].[memregalbans]=-1,"Regional (St Albans)",(
Iif([people].[memdiocedsips]=-1, "Diocesan (Eds and Ips)",(
Iif([people].[memdiocnorwich]=-1, "Diocesan (Norwich)",(
Iif([people].[memreg]=-1, "Regional", "Unknown"
)))))))))))))))))))))))))))))))))))))

The logic seemed to work nicely when there were just 3 options in the list, but when I try to enter the above, Access says "the expression you entered is too complex". Poor baby!

Does any one have any idea how I can get it to evaluate the data I need?

 

[edtab]

Try If..Else If clause. Much easier to read and follow as well.

 

[raskew]

Hi -

The Switch() function is a lot less messy and doesn't involve multiple parenthesis. Here's a tested (A97) example using 5 checkboxes (Check0 - Check4), a textbox (txtInstitution), and a command button (Command12):

Private Sub Command12_Click()

Me.txtInstution = Switch([Check0], "CYM", [Check1], "Independent BA", _
                         [Check2], "Henry Martyn Centre", [Check3], "Regional (Chelmsford)", _
                         [Check4], "Regional (Norwich)", True, "Unknown")

End Sub

HTH - Bob

 

[nemo700]

Hi -

The Switch() function is a lot less messy and doesn't involve multiple parenthesis. Here's a tested (A97) example using 5 checkboxes (Check0 - Check4), a textbox (txtInstitution), and a command button (Command12):

HTH - Bob

Excellent, that's just what I needed. Thanks a lot!

I wouldn't say poor old Access, I would say that you have created a spreadsheet and called it a table. If adding a new "thing" which in your case looks like a region will cause you to add a new column, then you have a spreadsheet, NOT a relational table. You need to do some reading on normalization and database design.

I see your point. I used tick boxes because it was easier for me to visualise it (this is my first big database project), and the list of tickboxes is unlikely to change frequently. Now that I am (a little bit) older and wiser, I would probably do it differently, but there's no time to revamp it at the moment. But thanks for the advice, all is very useful when trying to learn the best way to do things