Solved

dsum function in Vb?

Posted on 2002-03-07
8
1,355 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
  • 4
  • 3
8 Comments
 
LVL 51

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 51

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 51

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

838 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