First Post : Resolve #error when summing text box values that result from an IIF

[stuitn]

Hello all, my first ever post on here though I have read from the site for many years, so thanks for all the previous replies this far. I posted this question on another well know site however nobody could help, so here it is - I will be very grateful for assistance.

I have a continuous form where, in one 'column' there is a text box called "txtFinished" which is populated with either a zero or another integer. The figure is populated by an IIF statement, as follows:

=IIf(IsNull([DateStampCleaning]),0,IIf(([DateStampCleaning]>=[Forms]![frmIndividualBuildActivity]![txtFrom]) And ([DateStampCleaning]<=[Forms]![frmIndividualBuildActivity]![txtTo]+1),[quantity],0))

The IIF statement seems to work fine (all boxes contain a zero or another number) , however when I try to SUM the column, I simply get #Error in the text box I am using to SUM , that Control Source is

=Sum([txtFinished])

I can confirm the txtFinished and quantity are both Format : General Number

Please help!

 

[pbaldy]

You can't sum a calculated control. You can sum the calculation, or move the calculation to the report's source query, which gives you a field you can sum.

 

[pbaldy]

Just to clarify,

=Sum(=IIf(IsNull([DateStampCleaning]),0,IIf(([DateStampCleaning]>=[Forms]![frmIndividualBuildActivity]![txtFrom]) And ([DateStampCleaning]<=[Forms]![frmIndividualBuildActivity]![txtTo]+1),[quantity],0)))

There's your fish, but to clarify the clarification, the = before the first IIf() will cause a an error.

 

[stuitn]

Many thanks for your responses, I am grateful and yes I can see the plan to sum a calculated control was a bit dense of me I should have remembered that. Unfortunatly I still have the same issue even with

=Sum(IIf(IsNull([DateStampCleaning]),0,IIf(([DateStampCleaning]>=[Forms]![frmIndividualBuildActivity]![txtFrom]) And ([DateStampCleaning]<=[Forms]![frmIndividualBuildActivity]![txtTo]+1),[quantity],0)))

I have no idea where to go from here.

 

[Mark_]

Many thanks for your responses, I am grateful and yes I can see the plan to sum a calculated control was a bit dense of me I should have remembered that. Unfortunatly I still have the same issue even with

=Sum(IIf(IsNull([DateStampCleaning]),0,IIf(([DateStampCleaning]>=[Forms]![frmIndividualBuildActivity]![txtFrom]) And ([DateStampCleaning]<=[Forms]![frmIndividualBuildActivity]![txtTo]+1),[quantity],0)))

I have no idea where to go from here.

In your underlying query, make an expression to generate your initial value

YourExpressionNameHere: IIf(IsNull([DateStampCleaning]),0,IIf(([DateStampCleaning]>=[Forms]![frmIndividualBuildActivity]![txtFrom]) And ([DateStampCleaning]<=[Forms]![frmIndividualBuildActivity]![txtTo]+1),[quantity],0))

This would then allow you to SUM off of YourExpressionNameHere, maybe even just use DSum to get your result.

You are trying to do two different things at the same time, such as making coffee while swimming. You need to do them one at a time.

 

[stuitn]

That's it, many many thanks!