Help with DSum

[bradhanson]

What do I do if wanting to sum my data in a table if it is text in the field that groups them together. In the examples, the grouping is by productID, but my grouping is by a text field.

DSum("[Amount Aus]","Costs","[Costs.Area]=" & [Costs.Area] And "[Costs.Project Code]=" & [Costs.Project Code]")

The Costs.Project Code is the text field. The Costs.Area part works fine, but not Costs.Project Code

Any help would be great

 

[pbaldy]

Here's a good reference for you:

http://www.mvps.org/access/general/gen0018.htm

 

[bradhanson]

Thanks, I'll give it a try

 

[bradhanson]

Tried but can't get my head around the ' and the " and the &. What needs to be where. I tried as per below but still no good

DSum("[Amount Aus]","Costs","[Project Code]=' " & [Project Code] & " ' ")

 

[Ron_dK]

Have you tried this in a query.
The sql would look something like :

SELECT Costs.Project Code, Sum(Costs.Area) AS total
FROM Costs
GROUP BY Costs.Project Code;


Hth

 

[Rabbie]

& concatenates(joins) to strings together.

" and ' are string delimiters.

I think your code should be

DSum("[Amount Aus]","Costs","[Costs].[Project Code]= '" & [Costs].[Project Code]" & " ' ")

 

[bradhanson]

I must be thick, because I still can't get it working.

What I am ultimately trying to achieve is the adding up of my [Amount Aus] fields in a [Costs] table in groups according to [Area] AND [Project Code]. [Area] is numeric and [Project Code] is text

Project 1, Area 1 total is $1000
Project 1, Area 2 total is $500
Project 2, Area 1 total is $3000
etc

This total then is to be updated to a [AmountAus] field in the [Area] table

I can come up with the values I want by using a query that has a total sum function, but when I try to use it in an update query, it tells me the query is not updateable

I got DSum working for my update query for the [Area], but couldn't get the [Project Code] part working as it is text. This just left me with a total of all [Area] being the same, irrespective of what [Project Code] is used

 

[Jon K]

Tried but can't get my head around the ' and the " and the &. What needs to be where. I tried as per below but still no good

DSum("[Amount Aus]","Costs","[Project Code]=' " & [Project Code] & " ' ")

Remove the embedded spaces surrounding [Project Code]:-

DSum("[Amount Aus]","Costs","[Project Code]='" & [Project Code] & "'")
.

 

[bradhanson]

Thanks Guys for your help

I know can get a DSum to total area;
DSum("[Amount Aus]","Costs","[Costs].[Area]=" & [Costs].[Area])

As well as the Project;
DSum("[Amount Aus]","Costs","[Project Code]='" & [Project Code] & "'")

So I thought putting a combination of the 2 with a AND in there would do it, but just gives me the total of everything in the [Cost] table;

DSum("[Amount Aus]","Costs","[Costs].[Area]=" & [Costs].[Area] And "[Project Code]='" & [Project Code] & "'")

What am I missing here. I thought I had this bugger sorted !!!!

 

[pbaldy]

The link I gave you has examples of multiple criteria. Hint; pay attention to what's inside the quotes vs what's outside.