IIF error/question

[JackieEVSC]

I have a database that pulls from a SQL database, an IBM mainframe database, and the access database. I work for a large school corporation that separated one school into two separate locations this year. The information we for school location use is pulled from the SQL database which does not distinguish between the two different locations. For financial reasons, we cannot add another location to the SQL database (third party app where our price is by the number of locations).

We have netbooks for all students grades six through twelve which are checked out to them using the SQL (library system) database. The database I wrote creates barcoded repair tickets when they are sent in for repair, and we have no way of knowing which location to return units to if they are sent from those two schools.

I created an option group to select which location they are at so that it will print on the repair ticket, but I cannot get it to work correctly. I need it to say " – 1st Ave" for option 1, " – Diamond" for option 2, or nothing if an option is not selected (it will only be selected on those two schools, not the other 21 schools that check out technology to kids). The option selection is stored in the "A_Location" field in the tbl_RepairInfo table.

Below is the code I have on the form, but it returns "Error#". It's obviously a syntax error, but I have no idea what I'm doing wrong. Can someone please point out my error? Thanks in advance for your help!



=IIf([A_Location]=1," - 1st Ave",IIf([A_Location]=2," - Diamond",""))

[pbaldy]

I don't see any obvious errors. The field name has an underscore rather than a space? That field is part of the record source of the form?

[JackieEVSC]

I figured it out by using =Choose(Nz([A_Location],0)," - 1st Ave"," - Diamond")

[Missinglinq]

Glad you got it fixed, although, with the Option Group named A_Location, the code worked correctly, for me, with the Control Source of the Textbox set to

=IIf([A_Location]=1," - 1st Ave",IIf([A_Location]=2," - Diamond",""))

At any rate, need to add a third Option for 'other.' The problem is that you cannot 'reset' an Option back to Null once a selection has been made. If the user selects one of the two current Options and then realize that neither is correct, they cannot simply 'untick' it! A third Option, with a corresponding Null or "" being assigned to the Textbox would cover that possibility.

Linq ;0)>