Syntax of IF ELSE statement in control source

[M B]

Hello:

I'm working on a library database to hold books, both print and digital.
I'd like the report to print either the title of the book in plain text if
the book is a hard copy or the URL of the book if the book is electronic,
both in the same column (title) arranged in ascending order.

I've designed the report and placed the text boxes so they overlap and typed
the following in the control source of the selected multiple fields:

=IFF(Books!TitlePlain=Null,Books!TitleURL,Books!TitlePlain)

When I go to the form view, I get a box that says "enter parameter value"
and IFF (with a box to enter text). If I click OK, I get the report with
the URLs printed, but not the plain text titles. Plus, the books in hard
copy are at the top and the electronic books are at the bottom.

Obviously, my syntax is incorrect. And how do I get the "enter parameter
value" box to go away?

Any guidance would be appreciated!

Margaret

 

[Marshall Barton]

I'm working on a library database to hold books, both print and digital.
I'd like the report to print either the title of the book in plain text if
the book is a hard copy or the URL of the book if the book is electronic,
both in the same column (title) arranged in ascending order.

I've designed the report and placed the text boxes so they overlap and typed
the following in the control source of the selected multiple fields:

=IFF(Books!TitlePlain=Null,Books!TitleURL,Books!TitlePlain)

When I go to the form view, I get a box that says "enter parameter value"
and IFF (with a box to enter text). If I click OK, I get the report with
the URLs printed, but not the plain text titles. Plus, the books in hard
copy are at the top and the electronic books are at the bottom.

Obviously, my syntax is incorrect. And how do I get the "enter parameter
value" box to go away?

The problem is that the function is spelled IIF.

Another issue is that you can not compare anything to Null
(not even another Null) because a Null value means something
like Unknown.

In a query or a control expression, you can use this syntax:
=IIF(TitlePlain Is Null, TitleURL,TitlePlain)

In VBA, you have to use the IsNull() function to check for a
Null value.

 

[Andy]

MB;

Try:
=IIF(Books!TitlePlain=Null,Books!TitleURL,Books!TitlePlain)

Instead of:
=IFF(Books!TitlePlain=Null,Books!TitleURL,Books!TitlePlain)

It's IIF not IFF.

Andy

 

[Andy]

MB;

Marshall's a pro. Go with his answer.

Andy

 

[Fons Ponsioen]

You typed twice IFF instead of IIF, is this the problem?
Hope this helps.
Fons

 

[M B]

Thank you so much for the help. I have replaced the control source
with:

=IIF(TitlePlain Is Null, TitleURL,TitlePlain)

But now "Error#" prints on the report (x2, since the two fields
overlap). I have confirmed that the field names are correct.

What causes this error?

Thank you!

Margaret

 

[Fons Ponsioen]

I suspect the name of the textbox is the same as the name
of one of the data in your table or query.
Make sure the the textbox is named something different.
Hope this helps.
Fons

 

[M B]

Thanks for sharing your idea!

Yes, the name of the textbox and the field names were the same. I changed
them. The current statement reads:

=IIf(Books!TitleURL Is Null,Books!TitlePlain,Books!TitleURL)

(I changed the logic around a bit)

and the textboxes are called Plain and URL.

Unfortunately, I am still experiencing the #Error message. And, the Enter
Parameter Value box is coming up again...

Margaret

 

[Fons Ponsioen]

Check the name and than check the =IIF statement, if you
have autocorrect on, it may have changed your IIF
statement.
The autocorrect can be tricky at times, if you wish you
can turn it off by going to Tools - Options - General tab
There are three autocorrect options you may want to turn
off.
Hope this helps.
Fons

 

[Marshall Barton]

I think Fons has nailed it, but you changed way too much.

Again it's quite likely that Fons also pointed the finger at
Name Autocorrect as at least part of the culprit. This
feature is ill explained in Help and should be completely
disabled in Options. Don't enable it until you understand
exactly what it does, if ever.

You should end up with the same expression you had earlier:
=IIF(TitlePlain Is Null, TitleURL,TitlePlain)
with only the names of the text boxes changed. Here
TitlePlain and TitleURL are the names of fields in the forms
record source table/query, not the name of any controls.
--
Marsh
MVP [MS Access]

 

[M B]

Thanks to all of you, especially for warning me about autocorrect!

Margaret

I think Fons has nailed it, but you changed way too much.

Again it's quite likely that Fons also pointed the finger at
Name Autocorrect as at least part of the culprit. This
feature is ill explained in Help and should be completely
disabled in Options. Don't enable it until you understand
exactly what it does, if ever.

You should end up with the same expression you had earlier:
=IIF(TitlePlain Is Null, TitleURL,TitlePlain)
with only the names of the text boxes changed. Here
TitlePlain and TitleURL are the names of fields in the forms
record source table/query, not the name of any controls.
--
Marsh
MVP [MS Access]

Yes, the name of the textbox and the field names were the same. I changed
them. The current statement reads:

=IIf(Books!TitleURL Is Null,Books!TitlePlain,Books!TitleURL)

(I changed the logic around a bit)

and the textboxes are called Plain and URL.

Unfortunately, I am still experiencing the #Error message. And, the Enter
Parameter Value box is coming up again...

"Fons Ponsioen" wrote in message