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