Adodc Access Query

Posted on 2006-05-26
Last Modified: 2010-04-30
What is the correct syntax to query a access database that will sum one field and sum the same field if a certain date is is contained in that field
my code is

Dim mannasum As Currency
Dim mannaredeemed As Currency
Dim ddate As Date
ddate = "1/1/2000"

With Adodc1
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            "\test\Database\Manna Gold.mdb;Persist Security Info=False"
      .RecordSource = "select sum([Amount]) as TotalMannaSum from [Manna Gold] & WHERE [exchanged date]>#" & ddate & "# "
End With

This query works but I want to also want to get the sum of all [Amount]. Can this be in the same query or do I need a second query?

Question by:whiwex
    LVL 52

    Expert Comment

    by:Carl Tawn
    You need to do it as a second query.
    LVL 13

    Accepted Solution

    You can do it in two ways:

    This query will return a one column two rows recordset. The first is the conditional sum and the second is the full sum:
    "select sum([Amount]) as TotalMannaSum from [Manna Gold]  WHERE [exchanged date]>#" & ddate & "# " & "UNION select sum([Amount]) as TotalMannaSum from [Manna Gold]"

    This query will return a two column one row recordset. the first column is the condtional sum and the second is the full sum:
    "SELECT T1.TotalMannaSum as ConSum, T2.TotalMannaSum as AllSum FROM (select sum([Amount]) as TotalMannaSum from [Manna Gold]  WHERE [exchanged date]>#" & ddate & "# ) AS T1, (select sum([Amount]) as TotalMannaSum from [Manna Gold])"

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now