Solved

Sum in datasheet subform with dlookup fields

Posted on 2006-11-08
15
1,001 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now