Value missing, why. HELP???

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

Guest

I have this calculation in an Access 97 application on a report detail
section. The calculation is as follows:
=====
=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))-(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0))
==================
The second part:
IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))
is either a null or 0. Actually any part of the calculation can be null or 0,
never an empty "", because it is all numeric data. Because of this null or 0
the rest of the calculated result are falling out. Why? Can one of you MS
Access guru's assist here? I really need to understand by example why this is
happening. I look forward to your input's on this one. Thanks so very much
before hand!
 
The explanation is that NULLS propagate in math calculations.

1 * Null = Null

1*2*3*Null = Null

1+3 +4 *3 + Null = Null

So as Ofer said, if you are returning Null anyplace in the calculation, then you
must replace that with 0. You've taken care of the case where there is no
records in the recordset, but you've not taken care of the possibility that the
control has a null value. You can either nest another IIF inside the current IIF
or you can use the NZ function.

=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],
NZ([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0),0)
+...
You need to change each null value with 0, use the NZ function in each iif
statement

=Nz(iif(condition,then,else),0) + Nz(iif(condition,then,else),0)

this function will replace the Null, if returned with 0
--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Jay said:
I have this calculation in an Access 97 application on a report detail
section. The calculation is as follows:
=====
=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))-(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0))
==================
The second part:
IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData], [rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))
is either a null or 0. Actually any part of the calculation can be null or 0,
never an empty "", because it is all numeric data. Because of this null or 0
the rest of the calculated result are falling out. Why? Can one of you MS
Access guru's assist here? I really need to understand by example why this is
happening. I look forward to your input's on this one. Thanks so very much
before hand!
Click to expand...
Click to expand...
 
Ofer:

Thanks, for the reply, however, I've used the example you suggested (I
think!), but, I keep getting an error. Can you use the sample data I supplied
earlier and set it up so that I can better see what you meant? Here it is
again:
==============
=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))-(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0))
==================

Thanks,..........


Ofer said:
You need to change each null value with 0, use the NZ function in each iif
statement

=Nz(iif(condition,then,else),0) + Nz(iif(condition,then,else),0)

this function will replace the Null, if returned with 0
--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Jay said:
I have this calculation in an Access 97 application on a report detail
section. The calculation is as follows:
=====
=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))-(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0))
==================
The second part:
IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))
is either a null or 0. Actually any part of the calculation can be null or 0,
never an empty "", because it is all numeric data. Because of this null or 0
the rest of the calculated result are falling out. Why? Can one of you MS
Access guru's assist here? I really need to understand by example why this is
happening. I look forward to your input's on this one. Thanks so very much
before hand!
Click to expand...
Click to expand...
 
Try this

=NZ(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0),0)+
NZ(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0),0)-NZ(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0),0)+NZ(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0),0)

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Jay said:
Ofer:

Thanks, for the reply, however, I've used the example you suggested (I
think!), but, I keep getting an error. Can you use the sample data I supplied
earlier and set it up so that I can better see what you meant? Here it is
again:
==============
=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))-(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0))
==================

Thanks,..........


Ofer said:
You need to change each null value with 0, use the NZ function in each iif
statement

=Nz(iif(condition,then,else),0) + Nz(iif(condition,then,else),0)

this function will replace the Null, if returned with 0
--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Jay said:
I have this calculation in an Access 97 application on a report detail
section. The calculation is as follows:
=====
=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))-(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0))
==================
The second part:
IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))
is either a null or 0. Actually any part of the calculation can be null or 0,
never an empty "", because it is all numeric data. Because of this null or 0
the rest of the calculated result are falling out. Why? Can one of you MS
Access guru's assist here? I really need to understand by example why this is
happening. I look forward to your input's on this one. Thanks so very much
before hand!
Click to expand...
Click to expand...
Click to expand...
 
To Ofer and John Spencer this worked wonderfully! Thank you, thank you, thank
you!

John Spencer said:
The explanation is that NULLS propagate in math calculations.

1 * Null = Null

1*2*3*Null = Null

1+3 +4 *3 + Null = Null

So as Ofer said, if you are returning Null anyplace in the calculation, then you
must replace that with 0. You've taken care of the case where there is no
records in the recordset, but you've not taken care of the possibility that the
control has a null value. You can either nest another IIF inside the current IIF
or you can use the NZ function.

=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],
NZ([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0),0)
+...
You need to change each null value with 0, use the NZ function in each iif
statement

=Nz(iif(condition,then,else),0) + Nz(iif(condition,then,else),0)

this function will replace the Null, if returned with 0
--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Jay said:
I have this calculation in an Access 97 application on a report detail
section. The calculation is as follows:
=====
=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))-(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0))
==================
The second part:
IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData], [rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))
is either a null or 0. Actually any part of the calculation can be null or 0,
never an empty "", because it is all numeric data. Because of this null or 0
the rest of the calculated result are falling out. Why? Can one of you MS
Access guru's assist here? I really need to understand by example why this is
happening. I look forward to your input's on this one. Thanks so very much
before hand!
Click to expand...
Click to expand...
Click to expand...
 
I'm glad you got it working.
Good luck with your project


Jay said:
To Ofer and John Spencer this worked wonderfully! Thank you, thank you, thank
you!

John Spencer said:
The explanation is that NULLS propagate in math calculations.

1 * Null = Null

1*2*3*Null = Null

1+3 +4 *3 + Null = Null

So as Ofer said, if you are returning Null anyplace in the calculation, then you
must replace that with 0. You've taken care of the case where there is no
records in the recordset, but you've not taken care of the possibility that the
control has a null value. You can either nest another IIF inside the current IIF
or you can use the NZ function.

=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],
NZ([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0),0)
+...
You need to change each null value with 0, use the NZ function in each iif
statement

=Nz(iif(condition,then,else),0) + Nz(iif(condition,then,else),0)

this function will replace the Null, if returned with 0
--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

:

I have this calculation in an Access 97 application on a report detail
section. The calculation is as follows:
=====
=(IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))-(IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_ActualOnHndSales],0)+IIf([rpt_Product_TequilaInfoData_Collection_Daily1].[Report].[HasData],[rpt_Product_TequilaInfoData_Collection_Daily1].[Report]![txt_OpnBtlSales],0))
==================
The second part:
IIf([rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report].[HasData], [rpt_Product_TequilaInfoData_Collection_PrevDay1].[Report]![txt_OpenBtlSales],0))
is either a null or 0. Actually any part of the calculation can be null or 0,
never an empty "", because it is all numeric data. Because of this null or 0
the rest of the calculated result are falling out. Why? Can one of you MS
Access guru's assist here? I really need to understand by example why this is
happening. I look forward to your input's on this one. Thanks so very much
before hand!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top