access form - expression

[mpmason14]

hi guys -

I have this formula that I want to use in a form to calculate the "amount due" for a text box. i have tried the text box as both an unbound text box and a text box with the expression below:

=Sum(([dvdqty]*8)+([cdqty]*5)+([dvdcdqty]*11))

the result is a blank text box. there are no 0s in the other text boxes and the types are set to number.

if anyone could help me with this id appreciate it. thanks a lot

[nico5038]

The formula looks OK, but will fail when one of the fields contains Null. To prevent that trouble use the NZ() function around every field like:

=Sum((NZ([dvdqty])*8)+(NZ([cdqty])*5)+(NZ([dvdcdqty])*11))

Nic;o)

[mpmason14]

thanks, that does something...but it totals for the entire database.

how do you set it for just one record at a time? (ie. amount due for one customer)

i have done this before, but don't have access to that database right now and cant look it up.

[missinglinq]

That's what the function Sum() does! You simply need to use a formula with the NZ but with the word Sum!

[CODE=vb]=((NZ([dvdqty])*8)+(NZ([cdqty])*5)+(NZ([dvdcdqty])*11))[/CODE]

Linq ;0)>

[nico5038]

For that you can use the DSUM() function like:

=DSUM("<your calculation>","<the table or query>","CustomerID=" & [CustomerID])

The where part (third parm) will allow you to limit the rows to be processed.
It's however also a slow way. When your form is already for a specific customer, then another option is to do the calculation in the form's recordsource...

Nic;o)

[mpmason14]

That's what the function Sum() does! You simply need to use a formula with the NZ but with the word Sum!

[CODE=vb]=((NZ([dvdqty])*8)+(NZ([cdqty])*5)+(NZ([dvdcdqty])*11))[/CODE]

Linq ;0)>

thanks...but just to clarify what you just said...you DONT use the sum function if you want it totaled for individual records, just write an expression without any functions...

thanks for all your help. i got it set!

[NeoPa]

Indeed. That was a typo. MissingLinq meant withOUT rather than with.
Sum() is an aggregate function. It processes across records. What you need is a calculation WITHIN an individual record - therefore Sum() is not required.