Sum fields with no data, full total not displaying

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

Guest

Hi,

I am having a problem with this: I am trying to add sums. The report was
working fine, until I realised that if 1 of the sum fields do not contain
data, which is a possibility, the full total is not calculated.
I know and have successfully used this formula before:
IIf ([HasData], Count([FieldName]),0).

Now I have these calculated fields, created in queries:
SumOfConsultFee
SumOfProfFeePrice
SumOfDrugPrice

The last one FullTotal was also designed in a query as an expression:
FullTotal: [SumOfConsultFee]+[SumOfProfFeePrice]+[SumOfDrugPrice]

I have tried to apply the following:

IIf([HasData], Count([SumOfConsultFee]),0)
Also: IIf([HasData], Sum([SumOfConsultFee]),0)
Without success, any help will be much appreciated as this is the final
issue on my report!
Thanks
 
Chris

Take a look at the Nz() function (Null-to-Zero). It sounds like you have a
"propagating Null" in your data. If any value is Null (rather than 0), it
is considered unknown and unknowable, affecting any subsequent calculation
using that 'value'.
 
Hi Jeff,
Thank you for your reply.
Where and how would I insert the Nz function in my expression?
Would it be something like this?:
IIf ([HasData], Nz([SumOfConsultFee]),0).
Thank you,
Chris.

Jeff Boyce said:
Chris

Take a look at the Nz() function (Null-to-Zero). It sounds like you have a
"propagating Null" in your data. If any value is Null (rather than 0), it
is considered unknown and unknowable, affecting any subsequent calculation
using that 'value'.
IIf ([HasData], Count([FieldName]),0).
--
Regards

Jeff Boyce
<Office/Access MVP>

Chris said:
Hi,

I am having a problem with this: I am trying to add sums. The report was
working fine, until I realised that if 1 of the sum fields do not contain
data, which is a possibility, the full total is not calculated.
I know and have successfully used this formula before:
IIf ([HasData], Count([FieldName]),0).

Now I have these calculated fields, created in queries:
SumOfConsultFee
SumOfProfFeePrice
SumOfDrugPrice

The last one FullTotal was also designed in a query as an expression:
FullTotal: [SumOfConsultFee]+[SumOfProfFeePrice]+[SumOfDrugPrice]

I have tried to apply the following:

IIf([HasData], Count([SumOfConsultFee]),0)
Also: IIf([HasData], Sum([SumOfConsultFee]),0)
Without success, any help will be much appreciated as this is the final
issue on my report!
Thanks
Click to expand...
Click to expand...
 
Chris

Take a look at Access HELP on the Nz() function.

One place you can stick it (oops, I mean insert it) is in a query, wherein
you are building the summed value -- no IIF() required?!

--
Regards

Jeff Boyce
<Office/Access MVP>

Chris said:
Hi Jeff,
Thank you for your reply.
Where and how would I insert the Nz function in my expression?
Would it be something like this?:
IIf ([HasData], Nz([SumOfConsultFee]),0).
Thank you,
Chris.

Jeff Boyce said:
Chris

Take a look at the Nz() function (Null-to-Zero). It sounds like you have a
"propagating Null" in your data. If any value is Null (rather than 0), it
is considered unknown and unknowable, affecting any subsequent calculation
using that 'value'.
IIf ([HasData], Count([FieldName]),0).
--
Regards

Jeff Boyce
<Office/Access MVP>

Chris said:
Hi,

I am having a problem with this: I am trying to add sums. The report was
working fine, until I realised that if 1 of the sum fields do not contain
data, which is a possibility, the full total is not calculated.
I know and have successfully used this formula before:
IIf ([HasData], Count([FieldName]),0).

Now I have these calculated fields, created in queries:
SumOfConsultFee
SumOfProfFeePrice
SumOfDrugPrice

The last one FullTotal was also designed in a query as an expression:
FullTotal: [SumOfConsultFee]+[SumOfProfFeePrice]+[SumOfDrugPrice]

I have tried to apply the following:

IIf([HasData], Count([SumOfConsultFee]),0)
Also: IIf([HasData], Sum([SumOfConsultFee]),0)
Without success, any help will be much appreciated as this is the final
issue on my report!
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff,
Thanks a lot for your time, I had look at past posts on the Nz Function and
found out how to do it properly: in the Total Control source
=(Nz([SumOfConsultFee],0) + Nz([SumOfProfFeePrice],0)+ Nz([SumOfDrugPrice],0))
And it worked!
Thanks again for your advice!
Chris.
Jeff Boyce said:
Chris

Take a look at Access HELP on the Nz() function.

One place you can stick it (oops, I mean insert it) is in a query, wherein
you are building the summed value -- no IIF() required?!

--
Regards

Jeff Boyce
<Office/Access MVP>

Chris said:
Hi Jeff,
Thank you for your reply.
Where and how would I insert the Nz function in my expression?
Would it be something like this?:
IIf ([HasData], Nz([SumOfConsultFee]),0).
Thank you,
Chris.

Jeff Boyce said:
Chris

Take a look at the Nz() function (Null-to-Zero). It sounds like you have a
"propagating Null" in your data. If any value is Null (rather than 0), it
is considered unknown and unknowable, affecting any subsequent calculation
using that 'value'.
IIf ([HasData], Count([FieldName]),0).
--
Regards

Jeff Boyce
<Office/Access MVP>

Hi,

I am having a problem with this: I am trying to add sums. The report was
working fine, until I realised that if 1 of the sum fields do not contain
data, which is a possibility, the full total is not calculated.
I know and have successfully used this formula before:
IIf ([HasData], Count([FieldName]),0).

Now I have these calculated fields, created in queries:
SumOfConsultFee
SumOfProfFeePrice
SumOfDrugPrice

The last one FullTotal was also designed in a query as an expression:
FullTotal: [SumOfConsultFee]+[SumOfProfFeePrice]+[SumOfDrugPrice]

I have tried to apply the following:

IIf([HasData], Count([SumOfConsultFee]),0)
Also: IIf([HasData], Sum([SumOfConsultFee]),0)
Without success, any help will be much appreciated as this is the final
issue on my report!
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top