Correct expression for sum of one field depending on another?

[Garindan]

Hi all, I'm trying to get my expression for this right but I can't figure it out. On my form I have a Yes/No field called 'PurchasedForDelivery'. The form only shows records with this field set to 'No' (unchecked). I have another field called 'OrderWeight (Kg)'.

I have a text box at the bottom of the form and I would like to sum the 'OrderWeight (Kg)' field when the user selects the check boxes for 'PurchasedForDelivery' (to yes).

Can I do this with an expression or do I need some code to update the text box as the user clicks on check boxes?

Here's what I have so far:-

=DSum("[OrderWeight (Kg)]","fsubUnprocessedOrders","[ProcessedForDelivery] = 'Yes'")

It just gives me an #Error message

 

[boblarson]

I believe you want:

=DSum("[OrderWeight (Kg)]","fsubUnprocessedOrders","[ProcessedForDelivery] = TRUE")

 

[John Big Booty]

Try;

=DSum("[OrderWeight (Kg)]","fsubUnprocessedOrders","[ProcessedForDelivery] = -1")

Remember a check check box will return a value of -1 or True whilst uncheck will return a value of 0 or False.

 

[Garindan]

I have tried both, and it doesn't appear to do anything I have checked and double checked field names and form names etc, and they're fine I think. Is is something to do with having it on the current subform/form? Is the middle part 'fsubUnprocessedOrders' definitely right?

I have now also tried

=DSum("[OrderWeight (Kg)]","[Forms]![fsubUnprocessedOrders]","[ProcessedForDelivery] = -1")

but I don't know if that was correct or not lol. It didn't work anyway

 

[boblarson]

Ah, SUBFORM - requires different syntax. I would suggest using this method to get the correct form reference for your query.

 

[Garindan]

The checkboxes field and dsum text box are on the same subform though. Do I still need to use the different syntax?

I have tried

=DSum("[fsubUnprocessedOrders].[Form]![OrderWeight]","[ProcessedForDelivery] = -1")

but no joy. I don't even know if i'm doing it right

Oh yeah and I changed 'OrderWeight (Kg)' to just 'OrderWeight' because it's less confusing

 

[missinglinq]

fsubUnprocessedOrders sounds like a form or subform name. Is it? This argument has to be a Domain name, i.e. the name of a Table or Query, I believe.

Linq ;0)>

 

[Garindan]

Yes that's right, fsubUnprocessedOrders is a subform, so I guess that's wrong.

So I've now tried

=DSum("[OrderWeight]","qselUnprocessedOrders","[ProcessedForDelivery] = TRUE")

which is the query the subform is based on. It no longer has #Error but doesn't display anything.

Will this work? Because qselUnprocessedOrders has a criteria of 'No' in the ProcessedForDelivery field because I only wanted it to show unprocessed orders, and therefore the subform displays unprocessed orders.

The 'OrderWeight' field is not present in any table, it is calculated in a query, 'qselOrderWeights'.

I only want to add up the OrderWeight field when the boxes are ticked lol. Can I just use Sum instead of DSum?

 

[boblarson]

If the control to sum is on the subform too, you can use

=Sum(IIf([ProcessedForDelivery]<>0, Nz([OrderWeight],0), 0))

I would highly suggest not using parens or other special characters or spaces in your field or object names.

 

[Garindan]

Brilliant! I'm getting closer, thanks bob. This works

=Sum(IIf([ProcessedForDelivery]<>0, Nz([OrderWeight],0), 0))

but it behaves strange. It doesn't update results on the first click, only the second?? And when unclicking/deselecting it doesn't respond with the first click or two either?

Oh and thanks for your advice about not using parens or special characters/spaces in field names, I thought that when I changed it.

 

[boblarson]

Remember your record isn't updated until you move to another record or call an explicit save and this formula is based on saved data.

 

[Garindan]

Ok thanks again bob. So I could call an explicit save on mouseup from the checkbox and that should work? I'll try

 

[boblarson]

Don't use mouse up, use AFTER UPDATE.

 

[Garindan]

Ok thankyou, and thanks for your help!