Using IIF() in Reports

  • Thread starter Thread starter OldManEd
  • Start date Start date
O

OldManEd

Why doesn't this work in detail section of a report:

=IIF(IsBlank([TagName]), " ", [FullName] & Space(3) & "(" & [TagName] & ")")

I want both:

Richard Smith (TagName field is blank)
James Smith (Jim) (TagName field has Jim)

Where FullName = [First] & " " & [Last]

What is the work around??

Ed
 
As far as I know IsBlank is not a valid condition (or whatever it's called).
You would use:

IIf([TagName] Is Null, " ",[FullName] & Space(3) & "(" & [TagName] & ")")

You don't say if this expression is in a query or in the control source of a
text box, but in any case what you are specifying here is that if TagName Is
Null then you want the result to be a single space, otherwise you want the
full name plus the tag name. One way you could solve this is to have:

IIf([TagName] Is Null,[FullName],[FullName] & Space(3) & "(" & [TagName] &
")")

Another option would be to just have:

= [FullName] & (Space(3) + "(" + [TagName] + ")")

Using + as a concatenation operator results in the entire statement with the
parentheses evaluating to Null if TagName is null.
 
Hi, Ed,

I never heard of IsBlank in VBA. Try Nz(), as in

= [FullName] + IIf(Nz([TagName]," ")=" ","", Space(3) & "(" & [TagName] & ")")


Besides, your usage of IIf() is incorrect, because your statement is telling
Access to retrieve " " (a blank) where tagname is blank, and the full name
PLUS tagname where it isn't blank. What you really meant to say was that you
always wanted the fullname, plus the tagname if it existed. My statement will
give you that.

Hope this helps,

Sam
Why doesn't this work in detail section of a report:

=IIF(IsBlank([TagName]), " ", [FullName] & Space(3) & "(" & [TagName] & ")")

I want both:

Richard Smith (TagName field is blank)
James Smith (Jim) (TagName field has Jim)

Where FullName = [First] & " " & [Last]

What is the work around??

Ed
 
That's another way, but I believe that would be in an expression, not in
VBA, which uses different syntax to accomplish the same thing.

OfficeDev18 via AccessMonster.com said:
Hi, Ed,

I never heard of IsBlank in VBA. Try Nz(), as in

= [FullName] + IIf(Nz([TagName]," ")=" ","", Space(3) & "(" & [TagName] &
")")


Besides, your usage of IIf() is incorrect, because your statement is
telling
Access to retrieve " " (a blank) where tagname is blank, and the full name
PLUS tagname where it isn't blank. What you really meant to say was that
you
always wanted the fullname, plus the tagname if it existed. My statement
will
give you that.

Hope this helps,

Sam
Why doesn't this work in detail section of a report:

=IIF(IsBlank([TagName]), " ", [FullName] & Space(3) & "(" & [TagName] &
")")

I want both:

Richard Smith (TagName field is blank)
James Smith (Jim) (TagName field has Jim)

Where FullName = [First] & " " & [Last]

What is the work around??

Ed
 
Not sure about that, Bruce.

I'd use IIf(IsNull([TagName]), " ",[FullName] & Space(3) & "(" & [TagName] &
")")

The fact that it's in a query doesn't negate the fact that IIf is a VBA
function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BruceM said:
As far as I know IsBlank is not a valid condition (or whatever it's
called). You would use:

IIf([TagName] Is Null, " ",[FullName] & Space(3) & "(" & [TagName] & ")")

You don't say if this expression is in a query or in the control source of
a text box, but in any case what you are specifying here is that if
TagName Is Null then you want the result to be a single space, otherwise
you want the full name plus the tag name. One way you could solve this is
to have:

IIf([TagName] Is Null,[FullName],[FullName] & Space(3) & "(" & [TagName] &
")")

Another option would be to just have:

= [FullName] & (Space(3) + "(" + [TagName] + ")")

Using + as a concatenation operator results in the entire statement with
the parentheses evaluating to Null if TagName is null.

OldManEd said:
Why doesn't this work in detail section of a report:

=IIF(IsBlank([TagName]), " ", [FullName] & Space(3) & "(" & [TagName] &
")")

I want both:

Richard Smith (TagName field is blank)
James Smith (Jim) (TagName field has Jim)

Where FullName = [First] & " " & [Last]

What is the work around??

Ed
 
Thanks for steering me in the right direction. I was in error about IIf not
being VBA. The notion must have somehow go lodged in my head early on, and
I didn't give it much further thought. As regards syntax, the original post
had an = sign in front of the expression, which was described as being in
the detail section of a report. I assumed it was the control source for a
text box, but of course I now realize it could have been VBA, in which case
the = sign in front of the expression would have been a problem had it been
at the beginning of a line of VBA code. It didn't help that I omitted the =
sign from my examples.
This brings me to a terminology question. When IIf is used as the control
source for a text box or in a query, I refer to it as an expression. I am
beginning to suspect that it is also an expression when used in VBA. If it
is in the the control source for a text box or in a query, Is Null is the
syntax. In VBA it is IsNull. Is that correct? What succinct and standard
terminology can I use to distinguish between the two? For example, "In VBA
the syntax is IsNull before the name of the field. In ________ the syntax
is Is Null after the name of the field."
I read in one of these Access newsgroups about using the + operator as I did
in my example. Before posting it I tested it. Although the test was not
extensive, it worked, as it did in tests I conducted right after I learned
about it. If there are limitations to its use I would like to know about
them, but from what I have seen it is an efficient and workable bit of code.
Finally, the intent as I understand it is to have FullName appear whether or
not TagName is null, so [FullName] is needed instead of " ".

Douglas J. Steele said:
Not sure about that, Bruce.

I'd use IIf(IsNull([TagName]), " ",[FullName] & Space(3) & "(" & [TagName]
& ")")

The fact that it's in a query doesn't negate the fact that IIf is a VBA
function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BruceM said:
As far as I know IsBlank is not a valid condition (or whatever it's
called). You would use:

IIf([TagName] Is Null, " ",[FullName] & Space(3) & "(" & [TagName] & ")")

You don't say if this expression is in a query or in the control source
of a text box, but in any case what you are specifying here is that if
TagName Is Null then you want the result to be a single space, otherwise
you want the full name plus the tag name. One way you could solve this
is to have:

IIf([TagName] Is Null,[FullName],[FullName] & Space(3) & "(" & [TagName]
& ")")

Another option would be to just have:

= [FullName] & (Space(3) + "(" + [TagName] + ")")

Using + as a concatenation operator results in the entire statement with
the parentheses evaluating to Null if TagName is null.

OldManEd said:
Why doesn't this work in detail section of a report:

=IIF(IsBlank([TagName]), " ", [FullName] & Space(3) & "(" & [TagName] &
")")

I want both:

Richard Smith (TagName field is blank)
James Smith (Jim) (TagName field has Jim)

Where FullName = [First] & " " & [Last]

What is the work around??

Ed
 
I only use Is Null in SQL statements and no where else.

It's possible it'll work elsewhere, but I always use IsNull in VBA.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BruceM said:
Thanks for steering me in the right direction. I was in error about IIf
not being VBA. The notion must have somehow go lodged in my head early
on, and I didn't give it much further thought. As regards syntax, the
original post had an = sign in front of the expression, which was
described as being in the detail section of a report. I assumed it was
the control source for a text box, but of course I now realize it could
have been VBA, in which case the = sign in front of the expression would
have been a problem had it been at the beginning of a line of VBA code.
It didn't help that I omitted the = sign from my examples.
This brings me to a terminology question. When IIf is used as the control
source for a text box or in a query, I refer to it as an expression. I am
beginning to suspect that it is also an expression when used in VBA. If
it is in the the control source for a text box or in a query, Is Null is
the syntax. In VBA it is IsNull. Is that correct? What succinct and
standard terminology can I use to distinguish between the two? For
example, "In VBA the syntax is IsNull before the name of the field. In
________ the syntax is Is Null after the name of the field."
I read in one of these Access newsgroups about using the + operator as I
did in my example. Before posting it I tested it. Although the test was
not extensive, it worked, as it did in tests I conducted right after I
learned about it. If there are limitations to its use I would like to
know about them, but from what I have seen it is an efficient and workable
bit of code.
Finally, the intent as I understand it is to have FullName appear whether
or not TagName is null, so [FullName] is needed instead of " ".

Douglas J. Steele said:
Not sure about that, Bruce.

I'd use IIf(IsNull([TagName]), " ",[FullName] & Space(3) & "(" &
[TagName] & ")")

The fact that it's in a query doesn't negate the fact that IIf is a VBA
function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BruceM said:
As far as I know IsBlank is not a valid condition (or whatever it's
called). You would use:

IIf([TagName] Is Null, " ",[FullName] & Space(3) & "(" & [TagName] &
")")

You don't say if this expression is in a query or in the control source
of a text box, but in any case what you are specifying here is that if
TagName Is Null then you want the result to be a single space, otherwise
you want the full name plus the tag name. One way you could solve this
is to have:

IIf([TagName] Is Null,[FullName],[FullName] & Space(3) & "(" & [TagName]
& ")")

Another option would be to just have:

= [FullName] & (Space(3) + "(" + [TagName] + ")")

Using + as a concatenation operator results in the entire statement with
the parentheses evaluating to Null if TagName is null.

Why doesn't this work in detail section of a report:

=IIF(IsBlank([TagName]), " ", [FullName] & Space(3) & "(" & [TagName] &
")")

I want both:

Richard Smith (TagName field is blank)
James Smith (Jim) (TagName field has Jim)

Where FullName = [First] & " " & [Last]

What is the work around??

Ed
 
Thanks for the reply. I have discovered that either works when used as the
control source of an unbound combo box. I had thought that it had to be Is
Null, same as in a query. I probably experienced a syntax error (such as
not enclosing the field name following IsNull in parentheses) early on, and
took it to be a rule.
Rather than worrying about terminology, I will simply say something like
"Place this in the control source of the combo box: = IIf(etc. ...)".

Douglas J. Steele said:
I only use Is Null in SQL statements and no where else.

It's possible it'll work elsewhere, but I always use IsNull in VBA.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BruceM said:
Thanks for steering me in the right direction. I was in error about IIf
not being VBA. The notion must have somehow go lodged in my head early
on, and I didn't give it much further thought. As regards syntax, the
original post had an = sign in front of the expression, which was
described as being in the detail section of a report. I assumed it was
the control source for a text box, but of course I now realize it could
have been VBA, in which case the = sign in front of the expression would
have been a problem had it been at the beginning of a line of VBA code.
It didn't help that I omitted the = sign from my examples.
This brings me to a terminology question. When IIf is used as the
control source for a text box or in a query, I refer to it as an
expression. I am beginning to suspect that it is also an expression when
used in VBA. If it is in the the control source for a text box or in a
query, Is Null is the syntax. In VBA it is IsNull. Is that correct?
What succinct and standard terminology can I use to distinguish between
the two? For example, "In VBA the syntax is IsNull before the name of
the field. In ________ the syntax is Is Null after the name of the
field."
I read in one of these Access newsgroups about using the + operator as I
did in my example. Before posting it I tested it. Although the test was
not extensive, it worked, as it did in tests I conducted right after I
learned about it. If there are limitations to its use I would like to
know about them, but from what I have seen it is an efficient and
workable bit of code.
Finally, the intent as I understand it is to have FullName appear whether
or not TagName is null, so [FullName] is needed instead of " ".

Douglas J. Steele said:
Not sure about that, Bruce.

I'd use IIf(IsNull([TagName]), " ",[FullName] & Space(3) & "(" &
[TagName] & ")")

The fact that it's in a query doesn't negate the fact that IIf is a VBA
function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


As far as I know IsBlank is not a valid condition (or whatever it's
called). You would use:

IIf([TagName] Is Null, " ",[FullName] & Space(3) & "(" & [TagName] &
")")

You don't say if this expression is in a query or in the control source
of a text box, but in any case what you are specifying here is that if
TagName Is Null then you want the result to be a single space,
otherwise you want the full name plus the tag name. One way you could
solve this is to have:

IIf([TagName] Is Null,[FullName],[FullName] & Space(3) & "(" &
[TagName] & ")")

Another option would be to just have:

= [FullName] & (Space(3) + "(" + [TagName] + ")")

Using + as a concatenation operator results in the entire statement
with the parentheses evaluating to Null if TagName is null.

Why doesn't this work in detail section of a report:

=IIF(IsBlank([TagName]), " ", [FullName] & Space(3) & "(" & [TagName]
& ")")

I want both:

Richard Smith (TagName field is blank)
James Smith (Jim) (TagName field has Jim)

Where FullName = [First] & " " & [Last]

What is the work around??

Ed
 
Back
Top