Using a sum of queried records in a calculation in a form (1 Viewer)

[SDLAW234]

New to Access, not to programming.
I have 2 tables. Call one Contacts, the other Costs
I am able to sum the costs for a single contact in a query (Costs Query)![Sum Of Bill Amount])
I want to use that sum in a field in a form (Bill Reduction Wksheet]!TotalCosts which will then calculate several items including the costs for that contact.
I have tried using Dlookup but that gets only the first item in the query sum line, not the total of all matching records.
I have tried using the following in the control source for [Total Costs] "=Dsum([Costs Query]![Sum Of Bill Amount],[Costs Query])" but that returns a null (#name?)

What am I doing wrong?

 

[plog]

MS Access: DSum Function

This MSAccess tutorial explains how to use the Access DSum function with syntax and examples. The Microsoft Access DSum function returns the sum of a set of numeric values from an Access table (or domain).

www.techonthenet.com

The first argument of the Dsum should only be the field name, the second argument is where you put the datasource. And they are strings, so surround each argument with quotes.

 

[SDLAW234]

Thanks. That got the total of all the records, but now, although my Costs Query properly up the limited records, the Dsum still totals all the records in the costs table.

 

[plog]

The last argument of DSUM is criteria. Use it to filter it in the same manner your form is.

 

[SDLAW234]

How would I do that if the fields I want to compare are CostQuery!Name and [Forms]![Bill Reduction Wksht]![FullName]
I tried this but it gives me an error =DSum("[Sum Of Bill Amount]","[Costs Query]","[Forms]![Bill Reduction Wksht]![Full Name]")

 

[plog]

DSum("[Sum Of Bill Amount]","[Costs Query]", "[Name]='"  & [Forms]![Bill Reduction Wksht]![FullName] & "'")

Some notes about your names:

1. 'Name' is a poor name because it is a reserved word and makes coding/querying a little more difficult. I'd make it FullName as well.
2. Only use alphanumeric characters and underscores in names--for the same reason as #1. Remove the spaces from those table, query and field names you have listed above as well as everywhere else.

 

[SDLAW234]

This is my current command.
DSum("[Sum Of Bill Amount]","[CostsQuery]","[Forms]![Bill Reduction Wksht]![FullName]")
I can't change the sum of bill amount field as that is set by access when you hit sum. The rest are changed but same problem.
My query that works correctly is a joined query between Costs and Contacts tables where ClientName is connected to FullNamelf. The criteria for field ClientName is [Forms]![BillReductionWksht]![FullName].

 

[SDLAW234]

DSum("[Sum Of Bill Amount]","[Costs Query]", "[Name]='"  & [Forms]![Bill Reduction Wksht]![FullName] & "'")

Some notes about your names:

1. 'Name' is a poor name because it is a reserved word and makes coding/querying a little more difficult. I'd make it FullName as well.
2. Only use alphanumeric characters and underscores in names--for the same reason as #1. Remove the spaces from those table, query and field names you have listed above as well as everywhere else.

This is my current command.
DSum("[Sum Of Bill Amount]","[CostsQuery]","[Forms]![Bill Reduction Wksht]![FullName]")
I can't change the sum of bill amount field as that is set by access when you hit sum. The rest are changed but same problem.
My query that works correctly is a joined query between Costs and Contacts tables where ClientName is connected to FullNamelf. The criteria for field ClientName is [Forms]![BillReductionWksht]![FullName].

 

[plog]

You most certainly can change the name of a field no matter what Access initially sets it to. In the query designer it would be something like so:

BillAmountSum: SUM(BillAmount)

The rest of your post makes it seem like you didn't see the code I gave you for your DSum, although you quoted it.

 

[SDLAW234]

You most certainly can change the name of a field no matter what Access initially sets it to. In the query designer it would be something like so:

BillAmountSum: SUM(BillAmount)

The rest of your post makes it seem like you didn't see the post I made giving you the correct code to use for your DSum.

Sorry, my brain assumed that was my prior email as if you replied to it. Trying it now!