• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1594
  • Last Modified:

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

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
rolfg
Asked:
rolfg
  • 3
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can only use SUM() on Field names in the underlying recordsource, not on Control Names.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
rolfgAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
rolfgAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now