IIf in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am an Access novice who needs some help. My report derives its data from a
query which returns a number of variables including [C_P] (value either "C"
or"P") and Centres.name.

If I use Centres.name in a text box, the value of Centres.name is printed as
expected. However, I need to suppress the printing of Centres.name if [C_P]
= "P". So, I am trying to use the following expression in the text box:

=IIF([C_P]="C", Centres.name,"")

Running the report then asks me for the value of the parameter Centres.

What do I need to do

Any help would be much appreciated.

Jim Jones

PS I know that Name is a reserved work and so I shouldn't be using it as a
variable name - I intend doing something about this when I find the time.
 
Jim said:
I am an Access novice who needs some help. My report derives its data from a
query which returns a number of variables including [C_P] (value either "C"
or"P") and Centres.name.

If I use Centres.name in a text box, the value of Centres.name is printed as
expected. However, I need to suppress the printing of Centres.name if [C_P]
= "P". So, I am trying to use the following expression in the text box:

=IIF([C_P]="C", Centres.name,"")

Running the report then asks me for the value of the parameter Centres.

What do I need to do

Any help would be much appreciated.

Jim Jones

PS I know that Name is a reserved work and so I shouldn't be using it as a
variable name - I intend doing something about this when I find the time.
Click to expand...


Is Centres the name of your table/query? If so, don't use
it in the expression. Hopefully, you don't have multiple
fields named name, so there should be no ambiquity beyond
the reserved word issue.

Try this:

=IIF([C_P]="C", [name],"")

If that doesn't take care of it (for now), then you'd best
not put off until tomorrow what you should have done
yesterday and change the name of the field named name ;-)
 
a question for you, Marsh: say i have the same situation - a field named
using an Access reserved word, in a query that's bound to a form or report.
say i use an alias (correct term?) in the query, as

XName: Name
(this is design grid, of course, not the SQL syntax)

in the form or report code, expressions, etc, would i refer to the field as
XName instead of Name? if so, is that an acceptable "quick fix" to the
reserved word conflict issue? i'm not advocating sloppy naming conventions,
but there are those times when you step in to work on somebody else's db...

thx for your advice! :)


Marshall Barton said:
Jim said:
I am an Access novice who needs some help. My report derives its data from a
query which returns a number of variables including [C_P] (value either "C"
or"P") and Centres.name.

If I use Centres.name in a text box, the value of Centres.name is printed as
expected. However, I need to suppress the printing of Centres.name if [C_P]
= "P". So, I am trying to use the following expression in the text box:

=IIF([C_P]="C", Centres.name,"")

Running the report then asks me for the value of the parameter Centres.

What do I need to do

Any help would be much appreciated.

Jim Jones

PS I know that Name is a reserved work and so I shouldn't be using it as a
variable name - I intend doing something about this when I find the time.
Click to expand...


Is Centres the name of your table/query? If so, don't use
it in the expression. Hopefully, you don't have multiple
fields named name, so there should be no ambiquity beyond
the reserved word issue.

Try this:

=IIF([C_P]="C", [name],"")

If that doesn't take care of it (for now), then you'd best
not put off until tomorrow what you should have done
yesterday and change the name of the field named name ;-)
Click to expand...
 
tina said:
a question for you, Marsh: say i have the same situation - a field named
using an Access reserved word, in a query that's bound to a form or report.
say i use an alias (correct term?) in the query, as

XName: Name
(this is design grid, of course, not the SQL syntax)

in the form or report code, expressions, etc, would i refer to the field as
XName instead of Name? if so, is that an acceptable "quick fix" to the
reserved word conflict issue? i'm not advocating sloppy naming conventions,
but there are those times when you step in to work on somebody else's db...
Click to expand...


Yes, Tina, you would then refer to the field by its alias.
The original field name is not even available in the form or
report.

And, yes, that's certainly far more acceptable than crossing
your fingers that Access will resolve a name conflict the
way you want it to ;-) It's just not as quick/sloppy as
using square brackets.
 
ok, thanks! :)


Marshall Barton said:
db...


Yes, Tina, you would then refer to the field by its alias.
The original field name is not even available in the form or
report.

And, yes, that's certainly far more acceptable than crossing
your fingers that Access will resolve a name conflict the
way you want it to ;-) It's just not as quick/sloppy as
using square brackets.
Click to expand...
 
Dear Marshall

Thanks for the good advice. This is a cautionary tale for those who are new
to Access - use reserved words as field names at your peril. I have used
[name] in at least 5 different tables - apparently you can get away with it
for a while but not for long. In view of yesterday's experience, I have
resolved to keep all my field names unique within the database, and I have
started the tedious (and quite difficult) job of changing the names and
making sure that everything still works as before. I started close to the
problem I raised with you, and the IIf statement was soon working exactly as
expected.

Thanks again

Jim Jones

Marshall Barton said:
Jim said:
I am an Access novice who needs some help. My report derives its data from a
query which returns a number of variables including [C_P] (value either "C"
or"P") and Centres.name.

If I use Centres.name in a text box, the value of Centres.name is printed as
expected. However, I need to suppress the printing of Centres.name if [C_P]
= "P". So, I am trying to use the following expression in the text box:

=IIF([C_P]="C", Centres.name,"")

Running the report then asks me for the value of the parameter Centres.

What do I need to do

Any help would be much appreciated.

Jim Jones

PS I know that Name is a reserved work and so I shouldn't be using it as a
variable name - I intend doing something about this when I find the time.
Click to expand...


Is Centres the name of your table/query? If so, don't use
it in the expression. Hopefully, you don't have multiple
fields named name, so there should be no ambiquity beyond
the reserved word issue.

Try this:

=IIF([C_P]="C", [name],"")

If that doesn't take care of it (for now), then you'd best
not put off until tomorrow what you should have done
yesterday and change the name of the field named name ;-)
Click to expand...
 
Glad to hear you're making progress.

Avoiding reserved words can be a painful lesson. It is best
learned early, before the pain becomes intolerable.
--
Marsh
MVP [MS Access]


Jim said:
Dear Marshall

Thanks for the good advice. This is a cautionary tale for those who are new
to Access - use reserved words as field names at your peril. I have used
[name] in at least 5 different tables - apparently you can get away with it
for a while but not for long. In view of yesterday's experience, I have
resolved to keep all my field names unique within the database, and I have
started the tedious (and quite difficult) job of changing the names and
making sure that everything still works as before. I started close to the
problem I raised with you, and the IIf statement was soon working exactly as
expected.

Jim said:
I am an Access novice who needs some help. My report derives its data from a
query which returns a number of variables including [C_P] (value either "C"
or"P") and Centres.name.

If I use Centres.name in a text box, the value of Centres.name is printed as
expected. However, I need to suppress the printing of Centres.name if [C_P]
= "P". So, I am trying to use the following expression in the text box:

=IIF([C_P]="C", Centres.name,"")

Running the report then asks me for the value of the parameter Centres.

PS I know that Name is a reserved work and so I shouldn't be using it as a
variable name - I intend doing something about this when I find the time.
Click to expand...
Click to expand...
Marshall Barton said:
Is Centres the name of your table/query? If so, don't use
it in the expression. Hopefully, you don't have multiple
fields named name, so there should be no ambiquity beyond
the reserved word issue.

Try this:

=IIF([C_P]="C", [name],"")

If that doesn't take care of it (for now), then you'd best
not put off until tomorrow what you should have done
yesterday and change the name of the field named name ;-)
Click to expand...
Click to expand...
 
Back
Top