How do I use the IIF expression

[Guest]

I am trying to use the iif statement to have it either display/print or if
nothing is in the field skip that field. The field is a text field
The way it used to be done is:
if [field] <> "" then display [field]
else
skip
endif

Old Dbase language.

In the Access help screen it shows

=IIF(IsNull([fieldname]), "", "I'm Here: " [fieldname])

It doesn't seem to work when displaying the report.

I hope I've explained my self correctly.

 

[tina]

you're working with two different things here. the IIf() *function* is what
the Access Help screen showed you, used in an expression in the
ControlSource property of an unbound textbox control on a form (or a
report).

the If *statement* is used in VBA code, behind a form or report, to
conditionally do something. if you're wanting to display or hide a control
in a *report*, you can probably use either - depending on the circumstances.
suggest you try the one that suits you better; if it doesn't work, try the
other way. to use the VBA If statement, try running it from the OnPrint
event procedure of the section of the report that the control is in, as

If Not Me!ControlName = "" Then
Me!ControlName.Visible = True
Else
Me!ControlName.Visible = False
End If

you can actually shorten that to one line of code, as

Me!ControlName.Visible = Not (Me!ControlName = "")

and btw, if you prefer to use the IIf() function in a textbox control's
RecordSource property, try the following syntax, as

=IIf([fieldname] Is Null, "", "I'm Here: " & [fieldname])

hth

 

[Guest]

Hi Tina,
I've tried all the suggestions you've made. Like I said I may not be
explaining my self correctly. As it stands in the designed form when there is
nothing in the field name, it displays a space and of course when there is
something there it displays the information. What I would like it to do is if
there is something there is have it display something like: "Item name:
Wigets" The wigets would be the fieldname and the item name would be text
pluse the field name.

My ideal thing would be able to design a "Free style" report form. In other
words not use the design form at all and just code where I want the items to
be displayed or printed. But I don't have enough knowledge of Access to do
this.

Any suggestions on a good Access book where it gives the statement and
examples?

Thanks

you're working with two different things here. the IIf() *function* is what
the Access Help screen showed you, used in an expression in the
ControlSource property of an unbound textbox control on a form (or a
report).

the If *statement* is used in VBA code, behind a form or report, to
conditionally do something. if you're wanting to display or hide a control
in a *report*, you can probably use either - depending on the circumstances.
suggest you try the one that suits you better; if it doesn't work, try the
other way. to use the VBA If statement, try running it from the OnPrint
event procedure of the section of the report that the control is in, as

If Not Me!ControlName = "" Then
Me!ControlName.Visible = True
Else
Me!ControlName.Visible = False
End If

you can actually shorten that to one line of code, as

Me!ControlName.Visible = Not (Me!ControlName = "")

and btw, if you prefer to use the IIf() function in a textbox control's
RecordSource property, try the following syntax, as

=IIf([fieldname] Is Null, "", "I'm Here: " & [fieldname])

hth

I am trying to use the iif statement to have it either display/print or if
nothing is in the field skip that field. The field is a text field
The way it used to be done is:
if [field] <> "" then display [field]
else
skip
endif

Old Dbase language.

In the Access help screen it shows

=IIF(IsNull([fieldname]), "", "I'm Here: " [fieldname])

It doesn't seem to work when displaying the report.

I hope I've explained my self correctly.

 

[tina]

comments inline.

Hi Tina,
I've tried all the suggestions you've made. Like I said I may not be
explaining my self correctly. As it stands in the designed form when there is
nothing in the field name, it displays a space and of course when there is
something there it displays the information. What I would like it to do is if
there is something there is have it display something like: "Item name:
Wigets" The wigets would be the fieldname and the item name would be text
pluse the field name.

suggest you use the IIf() expression that i posted previously, in a textbox
control's ControlSource property. make sure that the control is not named
the same as the field in the expression.

My ideal thing would be able to design a "Free style" report form.

a report and a form are two different things. you haven't been clear on
which type of object you're working with; though for your particular
question, the answer would pretty much be the same for either a form or
report.

In other
words not use the design form at all and just code where I want the items to
be displayed or printed. But I don't have enough knowledge of Access to do
this.

Any suggestions on a good Access book where it gives the statement and
examples?

it's hard to judge what your skill level in Access is, or what other
programming experience you may have. you might go to a local bookstore with
a good "Computers" section, and browse the MS Access shelves. most Access
books try to guide the buyer on whether they're appropriate, with
information on the back cover, and introduction pages, overviews etc. if
nothing else, you can probably purchase a book and thumb through it at home
for a few days - if it's way too easy or hard, take it back for store credit
and choose another.

hth

 

[Larry Daugherty]

Hi Tina,

My apologies both to the Original Poster and to Tina for this Off
Topic intervention but I want to establish a dialogue with Tina and am
not clever enough to have worked out another way.

Tina, I'm "up the hill in Victorville". About 110 miles from you.
I'm impressed with your obvious competence in Access and with the
aptness of your responses, also with your apparent energy in the
process.

Within the next year I will need some Access help.

For my bona fides you can google these newsgroups and find my name
here and there with the same address attribution. I actually had my
valid email address listed at the time of the SWEN attacks. That
address is now defunct. Over the longer haul my ISP has changed but
my moniker has remained the same Of course you can just google on my
name. Most, but not all, of the hits will be me.

Regards,

 

[tina]

of course i know your name, Larry; i've seen your posts many, many times and
your obvious skill in Access. i'm actually right down the hill from you -
Inland Empire. years ago, i used to drive up to Hesperia to go to the
In-N-Out right off the 15, long before franchises opened down here.
you can get my email address at
http://home.att.net/~california.db/tips.html#aTip11 by following the
Example, and also changing the two to a one. by googling my posts, you can
see that i've referred to this website fairly often since i set it up last
November. tina