IIf statement

[John B]

I have a report that I am trying to put four fields, from one table, into an
IIf statement. My example is below:

IIf[Otesaga]=Yes,"Pick up credentials at the Otesaga Hotel."
IIf[CooperInn]=Yes,"Pick up credentials at the Copper Inn."
IIf[HOF]=Yes,"Pick up credentials at the Hall of Fame."
IIf[Mailed]=Yes,"Your credentials have been mailed."

This report is pulling from a query. Is there a length limit for a text box?
This is where I have the IIf statement currently. Any help on putting this
together would be greatly appreciated. Thanks.

John

 

[Jeff Boyce]

John

From your description, it sounds like you have four separate fields
([Otesaga], [CooperInn], [HOF], and [Mailed]) in your table. But it also
seems like these are describing how credentials are being processed.

I don't know your business, so I need to ask ... could someone have all
four, or are these mutually exclusive outcomes? If the latter, you don't
need four fields (in Access) to handle this. If you were limited to Excel,
you might use four fields, but not in a relational database like Access.

Before you proceed any further, consider taking another look at
"normalization" and "relational database design". Access "expects"
well-normalized data, so you may be facing a "pay now or pay later"
situation.

Or maybe I just don't understand enough about your particular situation...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

Hi John,

Two ways (at least) to do this.

1) Quick and dirty way: Use the Switch() function:

=Switch([Otesaga],"Pick up credentials at the Otesaga
Hotel.",[CooperInn],"Pick up credentials at the Copper Inn.",[HOF],"Pick up
credentials at the Hall of Fame.",[Mailed],"Your credentials have been
mailed.",True,"[Unable to determine how you are to receive your
credentials.]")

Do a search in you online help for the Switch() function for more
information about it. Note that since the fields you used are Yes/No
(boolean) fields, you can eliminate the =Yes part as it is redundant.

2) The better way: Create a child table that holds the various
credentials delivery options and link to it in your report's query:

tblWhateverItIsNamed
RecordID
...
CredentialsDeliveryLocation
...

tblCredentialsDeliveryOptions
CredentialsDeliveryLocation
CredentialsDeliveryText

In the table you would have:

Otesaga Pick up credentials at the Otesaga Hotel.
CooperInn Pick up credentials at the Copper Inn.
HOF Pick up credentials at the Hall of Fame.
Mailed Your credentials have been mailed.

The advantage of this method is that is easy to add new location rows
as needed without having to change your forms, queries and reports.

Clifford Bass

 

[John B]

Jeff,

Thanks for the reply. The reason I asked this is, I inherited this db
from someone who is no longer employed here and was trying to make this an
easy change. I do not know how many reports this info is on. I think I will
be making these options into one field, which in the long run I believe will
be easier for everyone. I will just have to go through all reports and find
where these check boxes were and change the report to reflect the new field.
I do have one last question. I would like to have a date filled in for the
mailed option. What would be the syntax for this? My example is below:

=IIf([CredentialPickUp]="Otesaga"," Pick up credentials at the Otesaga
Hotel.",IIf([CredentialPickUp]="Cooper Inn"," Pick up credentials at the
Copper Inn.",IIf([CredentialPickUp]="HOF"," Pick up credentials at the Hall
of Fame.",IIf([CredentialPickUp]="Mailed","Your credentials were mailed on
[Ceremony Mail Date]."))))

The very last IIf statement (mailed), where it says [Ceremony Mail Date],
how do I put that in here so it pulls the date? Thanks.

John

John

From your description, it sounds like you have four separate fields
([Otesaga], [CooperInn], [HOF], and [Mailed]) in your table. But it also
seems like these are describing how credentials are being processed.

I don't know your business, so I need to ask ... could someone have all
four, or are these mutually exclusive outcomes? If the latter, you don't
need four fields (in Access) to handle this. If you were limited to Excel,
you might use four fields, but not in a relational database like Access.

Before you proceed any further, consider taking another look at
"normalization" and "relational database design". Access "expects"
well-normalized data, so you may be facing a "pay now or pay later"
situation.

Or maybe I just don't understand enough about your particular situation...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that I am trying to put four fields, from one table, into
an
IIf statement. My example is below:

IIf[Otesaga]=Yes,"Pick up credentials at the Otesaga Hotel."
IIf[CooperInn]=Yes,"Pick up credentials at the Copper Inn."
IIf[HOF]=Yes,"Pick up credentials at the Hall of Fame."
IIf[Mailed]=Yes,"Your credentials have been mailed."

This report is pulling from a query. Is there a length limit for a text
box?
This is where I have the IIf statement currently. Any help on putting this
together would be greatly appreciated. Thanks.

John

 

[Clifford Bass]

Hi John,

Well, you are taking a step in the right direction, but not the
appropriate next step. To normalize you need an additional table, such as I
suggested. Since you want to add a date to your text in one instance I would
suggest a slight change to the tblCredentialsDeliveryOptions table. In the
CredentialsDeliveryText field you would have "Your credentials were mailed on
[DATE]." in the Mailed row. For your report, you need to set up your query
like this:

select RecordID, [Ceremony Mail Date], [other fields], CredentialsDeliveryText
from tblWhateverItIsNamed inner join tblCredentialsDeliveryOptions
on tblCredentialsDeliveryOptions.tblCredentialsDeliveryOptions =
tblWhateverItIsNamed.tblCredentialsDeliveryOptions;

Then in your report you would use this for the control source:

=Replace([CredentialsDeliveryText], "[DATE]", [Ceremony Mail Date])

This will produce the approprate text regardless of whether or not the
text is to include a date. If you ever want to add the date to one of the
other delivery texts, you simply add [DATE] into the appropriate location in
the appropriate row in the table.

Hope that helps,

Clifford Bass