asked on

dsum function in Vb?

How can I use the MSAccess aggregated dsum function in a recordset in VB?

When I use it on a recordset, I get a 3085 error
Undefined function 'DSum' in expression.

Example:
SELECT Table1.Id, Table1.Value1, Table1.Value2, DSum("Rest","Query4","N<=" & [Id]) AS Value3 FROM Table1

Please help me.

Ryan Chong

Use:

SELECT Table1.Id, Table1.Value1, Table1.Value2, (SELECT Sum(Rest) FROM Query4 Where N<= Table1.Id) AS Value3 FROM Table1

or

SQL = "SELECT Table1.Id, Table1.Value1, Table1.Value2, (SELECT Sum(Rest) FROM Query4 Where N<=" [Id] ") AS Value3 FROM Table1"

regards

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

seifus

ASKER

Thanks ryancys
Did you prove this query?
It asks me the value of Id, because it belongs to Table1 and in vb6 I get this error: 3061, 1 parameters were expected.

Yes, Id is dynamic, but inside the query, it changes in each record.

For this reason it works fine with DSUM, but I can't use it on VB

Is there any Reference to add in Vb to use DSUM?
or any other solution?

Chandramouli k

Instead of creating the query in msaccess try to create it in Vb using QueryDef object dynamically.

regards
KCM

seifus

ASKER

Id changes on each record not in every Query

seifus

ASKER

Forgive me ryancys, I had made a mistake

The correct answer is: SELECT Table1.Id, Table1.Value1, Table1.Value2, (SELECT Sum(Rest) FROM Query4 Where N<=[Id]) AS Value3 FROM Table1

The Problem was, that I used & with [Id]

Thanks a lot, you save me.

seifus

ASKER

The answer solved my problem
But, why can't I use DSUM in VB?

Ryan Chong

Hi, I think you cannot use it bcos maybe your Database Engine is Not Recognize it which the DSum function only avaialbe in M$ Access.

Cheers,
ryancys