Coditional formatting

  • Thread starter Thread starter Russ Kroeger
  • Start date Start date
R

Russ Kroeger

I have an A/P report that I want to display open payables. In the Table
this report gets it's data from I have a field named P/UCarrPdChk and
another named P/UCarrChg. I am trying to set up the report using
conditional formatting where if P/UCarrPdChk is empty in the table,
P/uCarrChg will report the amount. The expression I have been trying to use
is =IIf(IsNull([P/UCarrPdChk]), " " , [P/UCarrChk]). Access tells me the
syntax is incorrect. I have tried various combinations of space between the
commas to no avail. Can anyone help me?
 
Russ said:
I have an A/P report that I want to display open payables. In the Table
this report gets it's data from I have a field named P/UCarrPdChk and
another named P/UCarrChg. I am trying to set up the report using
conditional formatting where if P/UCarrPdChk is empty in the table,
P/uCarrChg will report the amount. The expression I have been trying to use
is =IIf(IsNull([P/UCarrPdChk]), " " , [P/UCarrChk]). Access tells me the
syntax is incorrect. I have tried various combinations of space between the
commas to no avail.
Click to expand...


If that's the control source expression fot the [P/UCarrChk]
text box, I don't see a syntax error. In this case, the
complaint is possibly about trying to use a string value for
a numeric field? Try using:
=IIf(IsNull([P/UCarrPdChk]), Null, [P/UCarrChk])

If the result displayed in the text box is #Error (instead
of a Syntax Error message box, then the most likely problem
is that the text box is named [P/UCarrChk]. In this case,
just change the name to something else such as txtCarrChk.

If, as I think you stated, that is a Conditional Formatting
expression, then the expression must return True or False,
which is used to determine if the special format should be
applied. In this case, you are on the wrong track to
achieve your goal.
 
Thank you for your help. I tried your suggestion and the report does report
back. However it functions in the opposite way I want it to. I want the
report to display a value (amount owed) if another value (Check No. Paid) is
empty. Instead this report is displaying if the other value(Check No Paid)
is there and does nothing if the value(Check No Paid) is empty. Is there an
opposite to IsNull?

Marshall Barton said:
Russ said:
I have an A/P report that I want to display open payables. In the Table
this report gets it's data from I have a field named P/UCarrPdChk and
another named P/UCarrChg. I am trying to set up the report using
conditional formatting where if P/UCarrPdChk is empty in the table,
P/uCarrChg will report the amount. The expression I have been trying to
use
is =IIf(IsNull([P/UCarrPdChk]), " " , [P/UCarrChk]). Access tells me the
syntax is incorrect. I have tried various combinations of space between
the
commas to no avail.
Click to expand...


If that's the control source expression fot the [P/UCarrChk]
text box, I don't see a syntax error. In this case, the
complaint is possibly about trying to use a string value for
a numeric field? Try using:
=IIf(IsNull([P/UCarrPdChk]), Null, [P/UCarrChk])

If the result displayed in the text box is #Error (instead
of a Syntax Error message box, then the most likely problem
is that the text box is named [P/UCarrChk]. In this case,
just change the name to something else such as txtCarrChk.

If, as I think you stated, that is a Conditional Formatting
expression, then the expression must return True or False,
which is used to determine if the special format should be
applied. In this case, you are on the wrong track to
achieve your goal.
Click to expand...
 
I had three suggestions, which one did you try?

Presuming it was the first one, either reverse the values in
the IIf:
=IIf(IsNull([P/UCarrPdChk]), [P/UCarrChk], Null)
or use any one of these alternatives:
=IIf([P/UCarrPdChk] Is Null, [P/UCarrChk], Null)
=IIf(Not IsNull([P/UCarrPdChk]), Null, [P/UCarrChk])
=IIf([P/UCarrPdChk] Is Not Null, Null, [P/UCarrChk])
--
Marsh
MVP [MS Access]


Russ said:
Thank you for your help. I tried your suggestion and the report does report
back. However it functions in the opposite way I want it to. I want the
report to display a value (amount owed) if another value (Check No. Paid) is
empty. Instead this report is displaying if the other value(Check No Paid)
is there and does nothing if the value(Check No Paid) is empty. Is there an
opposite to IsNull?
Russ said:
I have an A/P report that I want to display open payables. In the Table
this report gets it's data from I have a field named P/UCarrPdChk and
another named P/UCarrChg. I am trying to set up the report using
conditional formatting where if P/UCarrPdChk is empty in the table,
P/uCarrChg will report the amount. The expression I have been trying to
use
is =IIf(IsNull([P/UCarrPdChk]), " " , [P/UCarrChk]). Access tells me the
syntax is incorrect. I have tried various combinations of space between
the
commas to no avail.
Click to expand...

"Marshall Barton" wrote
If that's the control source expression fot the [P/UCarrChk]
text box, I don't see a syntax error. In this case, the
complaint is possibly about trying to use a string value for
a numeric field? Try using:
=IIf(IsNull([P/UCarrPdChk]), Null, [P/UCarrChk])

If the result displayed in the text box is #Error (instead
of a Syntax Error message box, then the most likely problem
is that the text box is named [P/UCarrChk]. In this case,
just change the name to something else such as txtCarrChk.

If, as I think you stated, that is a Conditional Formatting
expression, then the expression must return True or False,
which is used to determine if the special format should be
applied. In this case, you are on the wrong track to
achieve your goal.
Click to expand...
Click to expand...
 
It was actually the first two suggestions that got it to work also I moved
the Null to the end of the expression and that did the trick. Now if I may
ask another question.
Now that I have the report working I am trying to get it to give me a total
of all the reported values. When I create a text box and use the expression
=sum([txtcarrchg]) the report produces a dialog box that asks for a
parameter. If I enter 0 I get ths sum 0 on the report if I enter 2 it will
report 2 times the number of records etc. Is it possible to get a sum of
the values when using a conditional expression or am I trying to do
something that is not possible.
Marshall Barton said:
I had three suggestions, which one did you try?

Presuming it was the first one, either reverse the values in
the IIf:
=IIf(IsNull([P/UCarrPdChk]), [P/UCarrChk], Null)
or use any one of these alternatives:
=IIf([P/UCarrPdChk] Is Null, [P/UCarrChk], Null)
=IIf(Not IsNull([P/UCarrPdChk]), Null, [P/UCarrChk])
=IIf([P/UCarrPdChk] Is Not Null, Null, [P/UCarrChk])
--
Marsh
MVP [MS Access]


Russ said:
Thank you for your help. I tried your suggestion and the report does
report
back. However it functions in the opposite way I want it to. I want the
report to display a value (amount owed) if another value (Check No. Paid)
is
empty. Instead this report is displaying if the other value(Check No
Paid)
is there and does nothing if the value(Check No Paid) is empty. Is there
an
opposite to IsNull?
Russ Kroeger wrote:
I have an A/P report that I want to display open payables. In the Table
this report gets it's data from I have a field named P/UCarrPdChk and
another named P/UCarrChg. I am trying to set up the report using
conditional formatting where if P/UCarrPdChk is empty in the table,
P/uCarrChg will report the amount. The expression I have been trying to
use
is =IIf(IsNull([P/UCarrPdChk]), " " , [P/UCarrChk]). Access tells me the
syntax is incorrect. I have tried various combinations of space between
the
commas to no avail.

"Marshall Barton" wrote
If that's the control source expression fot the [P/UCarrChk]
text box, I don't see a syntax error. In this case, the
complaint is possibly about trying to use a string value for
a numeric field? Try using:
=IIf(IsNull([P/UCarrPdChk]), Null, [P/UCarrChk])

If the result displayed in the text box is #Error (instead
of a Syntax Error message box, then the most likely problem
is that the text box is named [P/UCarrChk]. In this case,
just change the name to something else such as txtCarrChk.

If, as I think you stated, that is a Conditional Formatting
expression, then the expression must return True or False,
which is used to determine if the special format should be
applied. In this case, you are on the wrong track to
achieve your goal.
Click to expand...
Click to expand...
Click to expand...
 
Russ said:
It was actually the first two suggestions that got it to work also I moved
the Null to the end of the expression and that did the trick. Now if I may
ask another question.
Now that I have the report working I am trying to get it to give me a total
of all the reported values. When I create a text box and use the expression
=sum([txtcarrchg]) the report produces a dialog box that asks for a
parameter. If I enter 0 I get ths sum 0 on the report if I enter 2 it will
report 2 times the number of records etc. Is it possible to get a sum of
the values when using a conditional expression or am I trying to do
something that is not possible.
Click to expand...


The aggregate functions are not aware of controls on a
form/report, they only operate on fields in the record
source table/query. You can use them on an expression as as
long as all the terms are record source fields. Also note
that they ignore Null values.

Therefore, your problem is simply solved by using the
expression:

=Sum(IIf(IsNull([P/UCarrPdChk]), [P/UCarrChk], Null))
or
=Sum(SameExpressionAsTheTextbox)
 
Yes! That did it. I appreciate your help. As you can undoubtedly tell I am
not at all well schooled with Access. I have taken a beginner &
intermediate class but they didn't get close to covering the things I wanted
to do. Thanks again.
Marshall Barton said:
Russ said:
It was actually the first two suggestions that got it to work also I
moved
the Null to the end of the expression and that did the trick. Now if I
may
ask another question.
Now that I have the report working I am trying to get it to give me a
total
of all the reported values. When I create a text box and use the
expression
=sum([txtcarrchg]) the report produces a dialog box that asks for a
parameter. If I enter 0 I get ths sum 0 on the report if I enter 2 it
will
report 2 times the number of records etc. Is it possible to get a sum of
the values when using a conditional expression or am I trying to do
something that is not possible.
Click to expand...


The aggregate functions are not aware of controls on a
form/report, they only operate on fields in the record
source table/query. You can use them on an expression as as
long as all the terms are record source fields. Also note
that they ignore Null values.

Therefore, your problem is simply solved by using the
expression:

=Sum(IIf(IsNull([P/UCarrPdChk]), [P/UCarrChk], Null))
or
=Sum(SameExpressionAsTheTextbox)
Click to expand...
 
Glad to hear you got it to work.

Taking courses is a good way to cover a broad number of the
basics, but they are almost always deficient in practical
matters of putting together a significant program. Spending
your spare time reading through previous threads in these
newsgroups is very spotty, but can often solve specific
problems you may be having. The trial and error activities
involved in creating an application is where I think the
real learning occurs.

Bottom line - Keep on truck'n ;-)
--
Marsh
MVP [MS Access]


Russ said:
Yes! That did it. I appreciate your help. As you can undoubtedly tell I am
not at all well schooled with Access. I have taken a beginner &
intermediate class but they didn't get close to covering the things I wanted
to do.
Russ said:
It was actually the first two suggestions that got it to work also I
moved
the Null to the end of the expression and that did the trick. Now if I
may
ask another question.
Now that I have the report working I am trying to get it to give me a
total
of all the reported values. When I create a text box and use the
expression
=sum([txtcarrchg]) the report produces a dialog box that asks for a
parameter. If I enter 0 I get ths sum 0 on the report if I enter 2 it
will
report 2 times the number of records etc. Is it possible to get a sum of
the values when using a conditional expression or am I trying to do
something that is not possible.
Click to expand...

"Marshall Barton" wrote
The aggregate functions are not aware of controls on a
form/report, they only operate on fields in the record
source table/query. You can use them on an expression as as
long as all the terms are record source fields. Also note
that they ignore Null values.

Therefore, your problem is simply solved by using the
expression:

=Sum(IIf(IsNull([P/UCarrPdChk]), [P/UCarrChk], Null))
or
=Sum(SameExpressionAsTheTextbox)
Click to expand...
Click to expand...
 
Back
Top