Changing option group values

  • Thread starter Thread starter Anna
  • Start date Start date
A

Anna

Hi,

I want to create a report based on fields from a form that
are option groups, but want the vaules to read as strings
rather than 1,2,3 etc.

Having searched the previous posts, I found this can be
done by creating an unbound control with an expression:

=IIf ([OptionGroupName]=1,"Active","Obsolete")

However, this only works for two options, and being
relatively new to Access, I'm not sure how to go about
expanding it.

My question is what is the expression for option groups
with more than two options (eg. 1=top 2=middle 3=bottom)?

Many thanks,

Anna
 
Anna

If you create a query, you can "read" (refer to) the fields on the form (or
on an underlying table/query). Base your report on that new query.

Add a field to the query for your option choice -- the IIF() function can be
nested, up to 7 deep, I believe. Something like (actual syntax may vary):

IIF([grpOption]=1,
"Top",
IIF([grpOption]=2,
"Middle",
IIF([grpOption]=3,
"Bottom", "Error")))

Put this all on one line -- I spread it out for emphasis.
 
Thanks Jeff, that works great.

As an addition: I'm basing my report on a count query - do
you know how I can count any null values in the field?
At the moment I use an update query that changes the nulls
to "0"s and then refer to "if...=0" in the IIf expression.
Is there a way to get the same result without changing the
nulls?

Thanks again,

Anna
 
Gary

Depends on where you're trying to use it. If I had that many choices, and
needed an "ALL" selection, I'd probably create a SQL statement in code
behind the <Do it> command button, and dynamically construct the "WHERE"
clause based on what was/was not selected.
 
Gary

I haven't used that approach. Instead, I create a dialog form that I use
BEFORE a report initiates. In that form, I "collect" criteria. I add a
command button that generates a SQL expression (i.e., a query) "on the fly",
based on the criteria on the form, and only THEN opens the report, based on
the generated SQL statement.

I don't believe you can use an IIF() function as a criterion... but ?!
 
Back
Top