Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to sum DLookup values in a form in MS Access 2003

Posted on 2010-08-31
5
Medium Priority
?
1,469 Views
Last Modified: 2012-05-10
In a form I have a column with lookup values: =DLookUp(NZ("Collected",0),"vwClassesContactsDetail","ClassID = " & NZ([ClassID],0))

ColumName: LookupCollected

The values get properly populated.

In the form footer I have a textbox =Sum([LookupCollected])

I have a similar form that works fine for values directly out of a table, but when using Dlookup values the sum shows #Error.

Any ideas how to fix this?

Thanks,

Rolf
0
Comment
Question by:rolfg
[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
  • 3
  • 2
5 Comments
 
LVL 75
ID: 33571968
You can only use SUM() on Field names in the underlying recordsource, not on Control Names.

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 33572018
You option here is to computer that value in the underlying record source using the calculated expression with an alias field name, then refer to that field name in the Sum ...

For example, the SQL for the record source might look like:

SELECT Table1.FIELD1, Table1.FIELD2, DLookUp(NZ("Collected",0),"vwClassesContactsDetail","ClassID = " & NZ([ClassID],0)) AS LookupCollected
FROM Table1;

However, using DLookup() in a query is subject to being VERY slow.

mx

0
 

Author Closing Comment

by:rolfg
ID: 33572213
Works like a charm!  
I tried an Inner Join before as was suggested on another website but that makes the dataset non updatable.
The form  / data set has only 4 to 5 records, so slow Dlookup is not a big deal and the back end database is a SQL2008 quad core machine.
0
 
LVL 75
ID: 33572317
Inner Join ....

See attached screenshot
Try setting the Recordset Type property as shown.  See if the query is now updatable.  If so ... set the same Form property of the same name.

mx
Capture1.gif
0
 

Author Comment

by:rolfg
ID: 33581284
I'll try that, thanks for the follow up.

Since moving to SQL server 2008 (from 2000) I have noticed changes in the ability to do updates especially when the data come via a SQL server view, it has gotten more restrictive. I run a one man IT shop including development and several ASP.NET websites for a small company...  not an easy tasks. Your help is very much appreciated.

 Rolf
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

618 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