Sum fields with no data, full total not displaying

[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

 

[Jeff Boyce]

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'.

 

[Guest]

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.

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

 

[Jeff Boyce]

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>

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.

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

 

[Guest]

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.

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>

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.

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