IIf error in report

[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!

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

 

[dhookom]

Can we assume A_Location is a numeric field with values like Null, 1, or 2. I would actually store these in a table so that you are prepared when a third location is added or a location is again split. Then use a combo box. Build it right and you don't have more work in the future.

If you continue to use the option group, you might want to try:

=Choose(Nz([A_Location],0)," - 1st Ave"," - Diamond")

Make sure the name of the control is not A_Location.

Duane
Hook'D on Access
MS Access MVP

 

[JackieEVSC]

Perfect! I doubt we'll be splitting a school like this again, as this was a very unique cirumstance. Generally if we add a new school we do pay to have a location added in our library database ... this particular school was already very small before it was split (it services a group of students with specific needs). It was split to separate the ciriculum needs between two locations! Thank you so much for your help!