Solved

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

Posted on 2010-08-31
5
1,273 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
  • 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 - Access MVP) earned 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

708 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

13 Experts available now in Live!

Get 1:1 Help Now