Need help with IIF statement in query

[Guest]

I'm not sure what the best way to complete this task...I hope someone could
help me out.
On a Residency form I have 5 checkbox fields...
ProvNL, ProvNB, ProvPEI, OtherCan, OtherCountry.
....if the applicant picked OtherCan a textbox called OtherCanSpec needs to
be filled in with the name of the other Province...or if OtherCountry is
checked likewise a textbox called OtherCounSpec needs to be filled in with
the name of the Country.

They can only pick 1 of these 5 checkboxes.

On a Worksheet report for the applicant I need to display what their
residency status is:
IE: If ProvNB is checked...I would need the report 'resident of' textbox to
show New Brunswick...likewise for ProvNL and ProvPEI.
If OtherCan or OtherCountry is checked...I would need what was entered in
either of the 2 textboxes to be shown on the report in that Resident status
field.

What am I looking at for an IIF statement to accomplish this??

Thanks!

 

[Guest]

Comments in-line

I'm not sure what the best way to complete this task...I hope someone could
help me out.
On a Residency form I have 5 checkbox fields...
ProvNL, ProvNB, ProvPEI, OtherCan, OtherCountry.
...if the applicant picked OtherCan a textbox called OtherCanSpec needs to
be filled in with the name of the other Province...or if OtherCountry is
checked likewise a textbox called OtherCounSpec needs to be filled in with
the name of the Country.

Why are there two textboxes? Are there two fields in the table? Wouldn't one
field (and textbox) work, differentiated by the check box?

They can only pick 1 of these 5 checkboxes.

How are you limiting the choice to one of the five? By code or an option
group?

On a Worksheet report for the applicant I need to display what their
residency status is:

By worksheet, do you mean an (Access) form?

IE: If ProvNB is checked...I would need the report 'resident of' textbox to
show New Brunswick...likewise for ProvNL and ProvPEI.
If OtherCan or OtherCountry is checked...I would need what was entered in
either of the 2 textboxes to be shown on the report in that Resident status
field.

What am I looking at for an IIF statement to accomplish this??

Something pretty ugly.... for five check boxes (Yes/No field types) and two
text boxes (Text fields) is a query, the "Field:" line in a column of the
grid would look like (should be on one line):

Resident_Of: IIF([ProvNB] =true,"New
Brunswick",IIF([ProvNL]=true,"Newfoundland",IIF([ProvPEI]=true,"Prince Edward
Island",IIF([OtherCan]=true,[OtherCanSpec],IIF([OtherCountry]=
true,[OtherCounSpec],"ERROR!!")))))


if the fields are named ProvNB, ProvNL, ...etc


HTH

Steve