DSum problem, any help appreciated!

delph

Registered User.
Local time
Today, 21:57
Joined
Oct 28, 2009
Messages
25
Hi, I want to do some calculated fields in a main form to show the total payments, adjustments and outstanding balance. The main form data comes from my customers table and the payments and payment types come from a seperate transactions table.
I thought this would be simple but I'm finding it's not. I thought I could use DSum but am having problems with it.

I've just used the toolbox to add a text box to the main form to calculate the sum of all payment amounts from the all trans table where the category is = pay. I've set the properties, control source as =DSum("[amt]","[all trans]","[cat = pay]") but I keep getting error.

I was then going to do the same for cat = adj and another text box to take one from the other.

Can anyone let me know where I'm going wrong?

Many thanks
 
People put square brackets around names, such as field names and table names, because those names may contain characters (including spaces) that would otherwise cause problems.

So, is cat = pay the name of something or is it something else?
 
=DSum("[amt]","[all trans]","[cat]=[pay]")
 
Just a guess but I think it would be: -

=DSum("amt","[all trans]","cat = 'pay' ")
 
I didn't actually read the question.
My posting is for a [cat] field value being equal to a [pay] field value.

Use the quotes like ChrisO said if the value of the [cat] field is text and you are looking for the "pay" in the field. Assume this is what you want.

I presume cat is category and you are using "pay" to indicate a payment.
Better to use a number as the code to indicate the category.
Access likes numbers better. I would use a Byte subtype for the number.
Also avoids having to use quotes all the time.

I also imagine you will need to limit the results to a particular customer or date if you are reading directly from the table.

Personally I would avoid the D function and split the cat type values into fields in the Record Source query. Then sum the fields in the header.
 
Thanks for all yor replies.

ChrisO, thanks for your response. cat is a column in the [all trans] table & pay is the text input into that field (among other text values).

I tried using =DSum("amt","[all trans]","cat = 'pay' ") but it still shows on the form as error.

I think I may cut my losses & put an extra field in the all trans table for the other payment type and just do sum for the different fields, as I need to get this done pretty quick.

Many thanks for your help though.
 
I tested

=DSum("amt","[all trans]","cat = 'pay' ")

Works for me
 
I'm not going into the DSum aissue now but I strongly suggest to avoid any spaces in any name - table, fields, forms objects.... anything.
this will save you a lot of troubles creating querys.

Just imagine how much troubles I have fixing things pople write here, In Israel, and name the objects in Hebrew (right to left) with spaces :D
 

Users who are viewing this thread

Back
Top Bottom