Solved

dsum function in Vb?

Posted on 2002-03-07
8
1,376 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:seifus
[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
  • 4
  • 3
8 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 6849029
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
0
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 200 total points
ID: 6849034
SQL = "SELECT Table1.Id, Table1.Value1, Table1.Value2, (SELECT Sum(Rest) FROM Query4 Where N<=" [Id]
") AS Value3 FROM Table1"

should be

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

if [id] is dynamic value.
0
 
LVL 1

Author Comment

by:seifus
ID: 6851183
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?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 5

Expert Comment

by:kcm76
ID: 6851450
Instead of creating the query in msaccess try to create it in Vb using QueryDef object dynamically.

regards
KCM
0
 
LVL 1

Author Comment

by:seifus
ID: 6852902
Id changes on each record not in every Query
0
 
LVL 1

Author Comment

by:seifus
ID: 6852984
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.


0
 
LVL 1

Author Comment

by:seifus
ID: 6853002
The answer solved my problem
But, why can't I use DSUM in VB?
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 6855236
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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