Solved

Sum in datasheet subform with dlookup fields

Posted on 2006-11-08
15
1,008 Views
Last Modified: 2006-11-18
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.

0
Comment
Question by:bjmatush
  • 10
  • 5
15 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 17903557
How about using DSum?

Something = DSum("YourField","YourTable","YourCriteria")
0
 

Author Comment

by:bjmatush
ID: 17903576
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
 
LVL 61

Expert Comment

by:mbizup
ID: 17903585
Post your DLookup
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:bjmatush
ID: 17903637
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
 
LVL 61

Expert Comment

by:mbizup
ID: 17903684
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
 
LVL 61

Expert Comment

by:mbizup
ID: 17903714
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
 

Author Comment

by:bjmatush
ID: 17903808
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
 
LVL 61

Expert Comment

by:mbizup
ID: 17903925
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
 

Author Comment

by:bjmatush
ID: 17905956
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
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 17906112
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
 
LVL 61

Expert Comment

by:mbizup
ID: 17906129
Actually, leave out the table prefix as well:

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

0
 
LVL 61

Expert Comment

by:mbizup
ID: 17906141
That was very fast!  Did it work?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17906157
Anyhow, If you need additional help with this let me know.
0
 

Author Comment

by:bjmatush
ID: 17906248

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
 
LVL 61

Expert Comment

by:mbizup
ID: 17906261
Glad to help out ;-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question