calculating with null values

[Karina M ;)]

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!

 

[Ken Snell \(MVP\)]

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"))

 

[Karina M ;)]

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!

 

[Ken Snell \(MVP\)]

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>

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!

 

[Karina M ;)]

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!

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>

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!

 

[George Nicholson]

(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,

 

[Ken Snell \(MVP\)]

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>

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!

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>

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!

 

[Karina M ;)]

Below is how it worked.
Thanks!

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

(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'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!