Sum in datasheet subform with dlookup fields

I have a datasheet subform with some controls that have a calculated value utilizing the dlookup function.  However, as I discovered, I can not simply sum this column up by placing a sum function in the form footer with the appropriate dlookup formulas in it.  Therefore, I need to find a solution as to how to add up such fields so that I can stop banging my head against my keyboard.

bjmatushAsked:
Who is Participating?
 
mbizupCommented:
Since ingredient is the name of a field, you need to seperate it out of the quotes, so that the value contained in the field, (not the literal text "[Ingredient]") is used.  Try revising your DLookup like this (assuming Ingredient is text -- let me know if it is numeric):

Cost: DLookUp("ProductCost","tblProducts","tblProducts!ProductName='" & [Ingredient] & "'")
0
 
mbizupCommented:
How about using DSum?

Something = DSum("YourField","YourTable","YourCriteria")
0
 
bjmatushAuthor Commented:
I tried that...but no go.  I checked that each line is performing the calculations correctly, but the dlookup in the sum field interferes with things.  From what I can tell in the example that I am working with (three lines of data in the datasheet), the dlookup in the sum is simply adding the last line of the datasheet three times, rather than adding each line.  

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
Post your DLookup
0
 
bjmatushAuthor Commented:
I was mistaken...I am only using SUM...not DSum...I have not used DSum before and not too sure what the difference is.  I did just try it now to put it in and it gave me an error stating, "The expression you entered has a function containing the wrong number of arguments".

Here is my original SUM formula:

=Sum((DLookUp("ProductCost","tblProducts","tblProducts!ProductName=Ingredient")/DLookUp("ProductKg","tblProducts","tblProducts!ProductName=Ingredient"))*DLookUp("Conversion","tblUnits","Unit = '" & [Unit] & "'")*[Amount])

It is taking the cost of a product (ingredient), divided by the total weight (to give $ per kg)...
Then multiplying the number amount (any weight unit), multiplied by conversion factor (to get everything into kilograms).


0
 
mbizupCommented:
DSum is kind of like Dlookup, in that it finds values in a given table meeting a specified creiteria.  The difference is that DSum adds them up.  Seeing your original Sum, lets try this.

= (DSum("ProductCost","tblProducts","tblProducts!ProductName=Ingredient")/DLookUp("ProductKg","tblProducts","tblProducts!ProductName=Ingredient")) * DLookUp("Conversion","tblUnits","Unit = '" & [Unit] & "'")*[Amount])
0
 
mbizupCommented:
My attempt at math there is factoring out your ProductKg so that you have:

(Total ProductCost)/ProductKg   * Conversion

If you need to divide by the Sum of  ProductKG, change that dlookup to a DSum as well.
0
 
bjmatushAuthor Commented:
I gave that a try and what it does is do the "sum" on the current record I click on in the datasheet.  In other words, it does not run through all rows - just "adds" the current row and ignores the others.  It will change as I click on each row.

I am wondering if I might need to approach this by opening the recordset and adding things up manually.  I was hoping on not using that approach since I thought if I can do it directly, that it would be better.

0
 
mbizupCommented:
I'm running out of Ideas.  One more thing I can think of to try is to modify your datasheet's record source so that it includes your original DLookup statement.

Create a query with SQL something like this:

Select Field1, Field2,etc.., Dlookup(your original dlookup statement) as SomeFieldName
From YourTableOrQuery
            ^---- This is the table or query that your subform was based on

Then set the recorssourceof your datasheet subform to this new query.  If you do that, you *should* be able to just use a Sum calculation on that DLookup, referring to it by the fieldname you used in your new query:

= Sum([SomeFieldName])
0
 
bjmatushAuthor Commented:
That sounds like a great idea...however, I am now having some difficulty with the syntax to get the dlookup working in the query.  Here is what I have:

Cost: DLookUp("ProductCost","tblProducts","tblProducts!ProductName=[Ingredient]")

I am just starting with a simple dlookup just to retrieve the product cost - not yet actually cost per kg which is what I ultimately need.  But once I get the syntax for this, I should be able to get the whole thing working.


In the query, I have the following fields:
Ingredient
Unit
Amount

0
 
mbizupCommented:
Actually, leave out the table prefix as well:

Cost: DLookUp("ProductCost","tblProducts","ProductName='" & [Ingredient] & "'")

0
 
mbizupCommented:
That was very fast!  Did it work?
0
 
mbizupCommented:
Anyhow, If you need additional help with this let me know.
0
 
bjmatushAuthor Commented:

Oh yeaaaaahhhhh!  It works perfectly.  I really did not want to go through manually calculating things from the recordset since I knew it would involve a lot of code to make sure you didn't get division by 0 errors or failures to update when something in the datasheet change...only to have to go through all that code again to calculate it.  I never thought of putting the dlookup in the query in the first place - and I could not create all my links in the query because it involved 3 tables and then you were not able to edit the datasheet (i.e. add any data into it).  

Eloquent solution - thank you!.

0
 
mbizupCommented:
Glad to help out ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.