Calculate sum of column

[zyper]

Hello I have a database of spare parts and I want to calculate the price of the whole warehouse.
I have managed to do [quantity]*[price] in a subform but now I want to sum all and show the result in textbox.




Thanks for help

 

[pr2-eugin]

Have you looked up on DSum?

 

[zyper]

If I'm correct Dsum only works on tables or querries. My total is calculated in subform so my Dsum would look like this =Dsum(txtTotal;WarehouseSubform; )
It does not work error says #Name?

 

[pr2-eugin]

How about Create a Query to set the SubForm's Recordsource, then you can use DSum on that Query?

 

[zyper]

Ok now I have created a querry with calculated field "total" which multiplies price and qty. This works fine.
Can you tell me the exact sintax for dsum.
querry name: "Totalquerry"
calculated field name: "total"

 

[pr2-eugin]

Try setting the control Source of the TextBox as..

=DSum("[total]", "[Totalquerry]")

Are you sure it is Totalquerry and not Totalquery?

 

[zyper]

When I put in this code I get back an error saying that I have inserted invalid sintax.
I'm not using that names that i wrote because my database is in slovenian so it would be confusing for you to read slovenian.

 

[pr2-eugin]

I'm not using that names that i wrote because my database is in slovenian so it would be confusing for you to read slovenian.

Make sense.. Try..

= Dsum("[totals]"[COLOR=Red][B];[/B][/COLOR] "[totalsQuery]")

 

[zyper]

pr2-eugin thank you!!!! my headaches are gone
Works like a charm

 

[pr2-eugin]

Glad to have helped.. Good Luck..

 

[zyper]

Hello
I have another question. Now that Dsum is working the only thing that is bothering me is that when I make changes to the database the total amount does not change until I reopen Form. How can I make the price refresh every time I make a change?

 

[pr2-eugin]

You have to put the code in the After Update events of the fields that will be affect this Sum..