rolfg
asked on
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) ,"vwClasse sContactsD etail","Cl assID = " & 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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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
ASKER
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
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
mx