Help with MS Access Dsum.

Posted on 2007-07-22
Last Modified: 2013-11-28
Dear Experts
Can anybody tell why this methods are not working?
Dim  TuitionAmt, MiscAmt As Double
     Dim tui As String
     tui = "Tuition"
     TuitionAmt = DSum("Amount", "tblAccount", "[GCode] ='" & Parent.CODE & "' And [Category] = " & tui & "'")
     MiscAmt = DSum("Amount", "tblAccount", "[Category] Like '" & "Misc*" & "'")

Question by:JaimeJegonia
    LVL 42

    Accepted Solution

    Missing single quote, try:

    Dim tui As String
         tui = "Tuition"
         TuitionAmt = DSum("Amount", "tblAccount", "[GCode] ='" & Parent.CODE & "' And [Category] = '" & tui & "'")
         MiscAmt = DSum("Amount", "tblAccount", "[Category] Like '" & "Misc*" & "'")


    Author Comment

    Didn't work.
    LVL 12

    Assisted Solution

    There was definitely a missing single quote in your original post, as fixed by dqmq
    The second statement using the DSum function could be simplified to:

     MiscAmt = DSum("Amount", "tblAccount", "[Category] Like 'Misc*'")

    In other words Misc is not used as variable here but as a text string. so if you are not getting the expected results then that might be a reason.
    Do you get any error messages or is it just that the code doesn't work as you would expect?


    Author Comment

    The fixed is:
    "nz(Amount)"  - to handle null amount to zero.
    The single quote of dqmq was a partial fixed. koutny simplified function also works.
    Thank you guys.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    794 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

    18 Experts available now in Live!

    Get 1:1 Help Now