locked
DSum Function RRS feed

  • Question

  • Hello,

    I have written the following expression in a query and continue to receive an error.  Is there something obvious I am doing wrong?

    Exp1: DSum("[TotalQty]","tblAData","[WOOrder]=" & [WONoPri])

    TotalQty is numeric in Table (tblAData).  Both WONoPri and WOOrder are ten character "Short Text" fields in tblProj and tblAData respectively.  I believe the error is from the criteria in the expression.

    Thanks for any assistance.


    Thanks PU_RJF

    Friday, February 28, 2020 5:26 PM

Answers

  • A value of text data type needs to be wrapped in literal quotes characters:

       DSum("TotalQty","tblAData","WOOrder=""" & [WONoPri] & """")

    Each literal quotes character is represented in a string expression by a contiguous pair of quotes characters.

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Friday, February 28, 2020 6:12 PM
    Friday, February 28, 2020 5:44 PM
  • DSum("[TotalQty]","tblAData","[WOOrder] = """ & [WONoPri] & """ AND [Cost Element] > ""800000"" AND [Cost Element] < ""890000""")

    But that would assume that the Cost Element column is a text data type.  I'd have expected it to be a number data type, in which case its values don't need to be wrapped in literal quotes characters:

    DSum("[TotalQty]","tblAData","[WOOrder] = """ & [WONoPri] & """ AND [Cost Element] > 800000 AND [Cost Element] < 890000")

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Friday, February 28, 2020 6:53 PM
    Friday, February 28, 2020 6:37 PM

All replies

  • You need to include the WONoPri field in tblADATA if you are going to use it in your  DSum criteria. Otherwise, create a query using both tables and then base your DSum on that query. You can't do a DSum where the criteria fields are not in the table criteria parameter.

    So if your new query includes TotalQty, WOOrder and WONoPri, and your query name is DATAQUERY, then you can use:

    DSum("[TotalQty]","DATAQUERY","[WOOrder]=[WONoPri]")

    Friday, February 28, 2020 5:42 PM
  • A value of text data type needs to be wrapped in literal quotes characters:

       DSum("TotalQty","tblAData","WOOrder=""" & [WONoPri] & """")

    Each literal quotes character is represented in a string expression by a contiguous pair of quotes characters.

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Friday, February 28, 2020 6:12 PM
    Friday, February 28, 2020 5:44 PM
  • Ken,

    Thanks!  That worked perfectly.

    PU_RJF


    Thanks PU_RJF

    Friday, February 28, 2020 6:13 PM
  • Just a quick followup...

    What if I wanted the following additional criteria?

    Exp1: DSum("[TotalQty]","tblAData","[WOOrder]=""" & [WONoPri] & AND &[Cost Element] > '800000' AND < '890000'"""")

    Thanks


    Thanks PU_RJF

    Friday, February 28, 2020 6:16 PM
  • DSum("[TotalQty]","tblAData","[WOOrder] = """ & [WONoPri] & """ AND [Cost Element] > ""800000"" AND [Cost Element] < ""890000""")

    But that would assume that the Cost Element column is a text data type.  I'd have expected it to be a number data type, in which case its values don't need to be wrapped in literal quotes characters:

    DSum("[TotalQty]","tblAData","[WOOrder] = """ & [WONoPri] & """ AND [Cost Element] > 800000 AND [Cost Element] < 890000")

    Ken Sheridan, Stafford, England

    • Marked as answer by PU_RJF Friday, February 28, 2020 6:53 PM
    Friday, February 28, 2020 6:37 PM
  • Perfect!  Thanks again.  Believe I'll do some reading on this subject.

    PU_RJF


    Thanks PU_RJF

    Friday, February 28, 2020 6:53 PM