Forum Discussion

RJF61's avatar
RJF61
Copper Contributor
Feb 20, 2023

Dsum

I am trying to develop the following query/report in Access...

 

I believe what I want is to Dsum EMESCst values from tblEMES and Dsum AMESCst values for tblAMES where tblWO WONo = WOOrder in each of the tables.

 

I have tried the following expressions...

SumofAMES: DSum("AMESCst","tblAMES","WOOrder" = WONo)

SumofEMES: DSum("EMESCst","tblEMES","WOOrder" = WONo)

 

Can someone assist with what I'm missing?  

2013
2016
access
developer

4 Replies

Sort By
  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    MVP
    Feb 20, 2023

    Hi,

     

    I can't judge if it's correct in logic, field types and some names due to lack of details, but in syntax you need 2 changes. The equals sign must be inside the quotes, and the parameters must be concatenated with ampersand:

     

    SumofAMES: DSum("AMESCst","tblAMES","WOOrder=" & WONo)

    SumofEMES: DSum("EMESCst","tblEMES","WOOrder=" & WONo)

     

    If you get stuck, share more details and post the SQL text of the query you tried.

     

    Servus
    Karl
    ****************
    Access Bug Trackers
    Access News
    Access DevCon

    • RJF61's avatar
      RJF61
      Copper Contributor
      Feb 20, 2023

      Hello Karl_Donaubauer 

      Thanks for the response.  Following is the SQL and field support info...

       

      SELECT tblProj.CNoWBSE, tblWO.WOID, DSum("AMESCst","tblAMES","WOOrder=" & [WONo]) AS SumofAMES, DSum("EMESCst","tblEMES","WOOrder=" & [WONo]) AS SumofEMES
      FROM ((tblProj LEFT JOIN tblWO ON tblProj.ProjID = tblWO.WOID) LEFT JOIN tblEMES ON tblWO.WONo = tblEMES.WOOrder) LEFT JOIN tblAMES ON tblWO.WONo = tblAMES.WOOrder;

       

      For each table below, the WONo and WOOrder fields are Short text - Field Size 10

       

       

       

      • Karl_Donaubauer's avatar
        Karl_Donaubauer
        MVP
        Feb 20, 2023

        Hi,

         

        You have to surround text parameters with single quotes, i.e.

        DSum("AMESCst","tblAMES","WOOrder='" & [WONo] & "'")

        etc.

         

        The other question is, if you need DSum() at all. You could try to instead activate the Totals button in the ribbon and use the Sum aggregat function for the two fields, which is faster than DSum(). Have a look if you get the desired results with it.

         

        Servus
        Karl
        ****************
        Access Bug Trackers
        Access News
        Access DevCon

         

Resources