Solved

Sum in datasheet subform with dlookup fields

Posted on 2006-11-08
15
1,011 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

738 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