calculating with null values

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

Guest

I'm trying to calculate as below but the Bid Work Orders filed is sometimes
blank and I cannot get it to work.

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(IsNull([Bid
Work Orders Text]),0))

This will only make the BWO field = zero even if it has a value. I just need
it to replace null with zero (w/i the equation, if possible).

Thanks!
 
Blank may not mean Null if the field is a text field. Try this (note that
you're missing the second argument for the IIf statement -- what value is to
be used if the IIf result is False?):

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid Work
Orders Text] & "")=0,0, "you need something here for when the field is not
empty"))
 
I removed the first field for simplicity. What I want is if true give me a
zero value and if false give me the actual value in the field (itself), but
this doesn't work as below.

=[Assignment Cost Estimate Text]+(IIf(Len([Bid Work Orders Text] &
"")=0,[Bid Work Oders Text]))

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid Work
Orders Text] & "")=0,0, "you need something here for when the field is not
empty"))
Click to expand...
In the latter part I wrote =0,1 and that only made the null/empty value =
zero rather than it's own value (when not zero). When I wrote the field in
the " ", it gave me a window as if it did not recognize it.

Ken Snell (MVP) said:
Blank may not mean Null if the field is a text field. Try this (note that
you're missing the second argument for the IIf statement -- what value is to
be used if the IIf result is False?):

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid Work
Orders Text] & "")=0,0, "you need something here for when the field is not
empty"))


--

Ken Snell
<MS ACCESS MVP>



Karina M ;) said:
I'm trying to calculate as below but the Bid Work Orders filed is
sometimes
blank and I cannot get it to work.

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(IsNull([Bid
Work Orders Text]),0))

This will only make the BWO field = zero even if it has a value. I just
need
it to replace null with zero (w/i the equation, if possible).

Thanks!
Click to expand...
Click to expand...
 
What type of data are in those fields? What is it you want to display in the
query/report using those fields? Are those fields actually fields in the
report's Recordsource query, or are they the names of controls on the report
itself?

--

Ken Snell
<MS ACCESS MVP>


Karina M ;) said:
I removed the first field for simplicity. What I want is if true give me a
zero value and if false give me the actual value in the field (itself),
but
this doesn't work as below.

=[Assignment Cost Estimate Text]+(IIf(Len([Bid Work Orders Text] &
"")=0,[Bid Work Oders Text]))

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid
Work
Orders Text] & "")=0,0, "you need something here for when the field is
not
empty"))
Click to expand...
In the latter part I wrote =0,1 and that only made the null/empty value =
zero rather than it's own value (when not zero). When I wrote the field in
the " ", it gave me a window as if it did not recognize it.

Ken Snell (MVP) said:
Blank may not mean Null if the field is a text field. Try this (note that
you're missing the second argument for the IIf statement -- what value is
to
be used if the IIf result is False?):

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid
Work
Orders Text] & "")=0,0, "you need something here for when the field is
not
empty"))


--

Ken Snell
<MS ACCESS MVP>



Karina M ;) said:
I'm trying to calculate as below but the Bid Work Orders filed is
sometimes
blank and I cannot get it to work.

=[Contract Amount Text]-([Assignment Cost Estimate
Text])+(IIf(IsNull([Bid
Work Orders Text]),0))

This will only make the BWO field = zero even if it has a value. I just
need
it to replace null with zero (w/i the equation, if possible).

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
THE FIELDS ARE $ AMOUNTS THAT ARE PULLED FROM A SECOND QUERY (CONTROL
SOURCE=Final Cost Approval 'Bid' Sum Query.SumOfFinal Cost Approval) THAT
SUMS AND PROVIDES TOTALS PER CONTRACTOR. I CAN;T PULL FROM THE ORIGINAL QUERY
AS THIS DOES NOT GIVE TOTALS FOR ALL CONTRACTORS...
WHAT I WANT TO DISPLAY IS THE TOTAL PER CONTRACTOR BUT THEN I NEED TO USE AN
EQUATION TO SHOW ACTUAL $ ENCUMBERED, WHICH IS WHY I WAS USING AN EQUATION.
CAN I USE A SECOND TEXT BOX TO PULL THE AMOUNTS FROM THE OTHER QUERY TO THE
REPORT BUT MAKE IT SHOW BLANK VALUES = ZERO AND THEN USE THAT FIELD TO
CALCULATE? IF SO, WHAT VALUE WOULD I ASK FOR IN THE FALSE STATEMENT IF I WANT
IT TO CARRY OVER THE 'NOT BLANK' VALUE AS WELL AS ZEROS?
THANKS!


Ken Snell (MVP) said:
What type of data are in those fields? What is it you want to display in the
query/report using those fields? Are those fields actually fields in the
report's Recordsource query, or are they the names of controls on the report
itself?

--

Ken Snell
<MS ACCESS MVP>


Karina M ;) said:
I removed the first field for simplicity. What I want is if true give me a
zero value and if false give me the actual value in the field (itself),
but
this doesn't work as below.

=[Assignment Cost Estimate Text]+(IIf(Len([Bid Work Orders Text] &
"")=0,[Bid Work Oders Text]))

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid
Work
Orders Text] & "")=0,0, "you need something here for when the field is
not
empty"))
Click to expand...
In the latter part I wrote =0,1 and that only made the null/empty value =
zero rather than it's own value (when not zero). When I wrote the field in
the " ", it gave me a window as if it did not recognize it.

Ken Snell (MVP) said:
Blank may not mean Null if the field is a text field. Try this (note that
you're missing the second argument for the IIf statement -- what value is
to
be used if the IIf result is False?):

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid
Work
Orders Text] & "")=0,0, "you need something here for when the field is
not
empty"))


--

Ken Snell
<MS ACCESS MVP>



I'm trying to calculate as below but the Bid Work Orders filed is
sometimes
blank and I cannot get it to work.

=[Contract Amount Text]-([Assignment Cost Estimate
Text])+(IIf(IsNull([Bid
Work Orders Text]),0))

This will only make the BWO field = zero even if it has a value. I just
need
it to replace null with zero (w/i the equation, if possible).

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
(IIf(IsNull([Bid Work Orders Text]),0))

IIF has 3 arguments: IIF(boolean expression, TrueResult, FalseResult).
You've provided the 1st & 2nd but not the 3rd. I'm guessing that without the
3rd argument, zero is being substituted (something has to be. I'm a little
suprised you aren't getting an error):

= (IIf(IsNull([Bid Work Orders Text]),0,[Bid Work Orders Text]))

or, use Access' NZ function: nz(ValueToCheck,
SubstituteForValueToCheckIfNull) Nz returns ValueToCheck if it isn't Null.

= nz([Bid Work Orders Text],0)

HTH,
 
I am headed out of town for a week, so I won't be able to continue this
thread with you until I return. However, the overall situation is that you
cannot use a control name in another control's ControlSource expression when
you're in a report. You need to replace the control name with the expression
from that control's ControlSource; essentially, you need to repeat the
expression again.

So, for the control that has the expression:

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid Work
Orders Text] & "")=0,0, [Bid Work Orders Text]))

You'd need to change it to this (note that I'm using generic reference to
the control source expressions of the other controls; just copy the control
source from the indicated control, without the = sign, and put it in place
of my generic name):

=(ControlSourceExpressionFor'Contract Amount
Text'Control)-(ControlSourceExpressionFor'Assignment Cost Estimate
Text'Control)+(IIf(Len(ControlSourceExpressionFor'Bid Work Orders
Text'Control & "")=0,0, ControlSourceExpressionFor'Bid Work Orders
Text'Control))

--

Ken Snell
<MS ACCESS MVP>




Karina M ;) said:
THE FIELDS ARE $ AMOUNTS THAT ARE PULLED FROM A SECOND QUERY (CONTROL
SOURCE=Final Cost Approval 'Bid' Sum Query.SumOfFinal Cost Approval) THAT
SUMS AND PROVIDES TOTALS PER CONTRACTOR. I CAN;T PULL FROM THE ORIGINAL
QUERY
AS THIS DOES NOT GIVE TOTALS FOR ALL CONTRACTORS...
WHAT I WANT TO DISPLAY IS THE TOTAL PER CONTRACTOR BUT THEN I NEED TO USE
AN
EQUATION TO SHOW ACTUAL $ ENCUMBERED, WHICH IS WHY I WAS USING AN
EQUATION.
CAN I USE A SECOND TEXT BOX TO PULL THE AMOUNTS FROM THE OTHER QUERY TO
THE
REPORT BUT MAKE IT SHOW BLANK VALUES = ZERO AND THEN USE THAT FIELD TO
CALCULATE? IF SO, WHAT VALUE WOULD I ASK FOR IN THE FALSE STATEMENT IF I
WANT
IT TO CARRY OVER THE 'NOT BLANK' VALUE AS WELL AS ZEROS?
THANKS!


Ken Snell (MVP) said:
What type of data are in those fields? What is it you want to display in
the
query/report using those fields? Are those fields actually fields in the
report's Recordsource query, or are they the names of controls on the
report
itself?

--

Ken Snell
<MS ACCESS MVP>


Karina M ;) said:
I removed the first field for simplicity. What I want is if true give me
a
zero value and if false give me the actual value in the field (itself),
but
this doesn't work as below.

=[Assignment Cost Estimate Text]+(IIf(Len([Bid Work Orders Text] &
"")=0,[Bid Work Oders Text]))

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(Len([Bid
Work
Orders Text] & "")=0,0, "you need something here for when the field is
not
empty"))
In the latter part I wrote =0,1 and that only made the null/empty value
=
zero rather than it's own value (when not zero). When I wrote the field
in
the " ", it gave me a window as if it did not recognize it.



:

Blank may not mean Null if the field is a text field. Try this (note
that
you're missing the second argument for the IIf statement -- what value
is
to
be used if the IIf result is False?):

=[Contract Amount Text]-([Assignment Cost Estimate
Text])+(IIf(Len([Bid
Work
Orders Text] & "")=0,0, "you need something here for when the field is
not
empty"))


--

Ken Snell
<MS ACCESS MVP>



I'm trying to calculate as below but the Bid Work Orders filed is
sometimes
blank and I cannot get it to work.

=[Contract Amount Text]-([Assignment Cost Estimate
Text])+(IIf(IsNull([Bid
Work Orders Text]),0))

This will only make the BWO field = zero even if it has a value. I
just
need
it to replace null with zero (w/i the equation, if possible).

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Below is how it worked.
Thanks!

IIf(IsNull([Bid Work Orders Text]),0,[Bid Work Orders Text])))

George Nicholson said:
(IIf(IsNull([Bid Work Orders Text]),0))
Click to expand...

IIF has 3 arguments: IIF(boolean expression, TrueResult, FalseResult).
You've provided the 1st & 2nd but not the 3rd. I'm guessing that without the
3rd argument, zero is being substituted (something has to be. I'm a little
suprised you aren't getting an error):

= (IIf(IsNull([Bid Work Orders Text]),0,[Bid Work Orders Text]))

or, use Access' NZ function: nz(ValueToCheck,
SubstituteForValueToCheckIfNull) Nz returns ValueToCheck if it isn't Null.

= nz([Bid Work Orders Text],0)

HTH,


Karina M ;) said:
I'm trying to calculate as below but the Bid Work Orders filed is
sometimes
blank and I cannot get it to work.

=[Contract Amount Text]-([Assignment Cost Estimate Text])+(IIf(IsNull([Bid
Work Orders Text]),0))

This will only make the BWO field = zero even if it has a value. I just
need
it to replace null with zero (w/i the equation, if possible).

Thanks!
Click to expand...
Click to expand...
 
Back
Top