none
Field using Query source generates #Error, but not if Table is the source RRS feed

  • Question

  • I have a field in a form (and a report as well), that works fine when I reference the Table as the source, but generates a “#Error” when I reference the Query as the source . . .not sure if this is related, but when I use that same field (with the error) in a calculated field it displays “#Type!”

    Both original Table Fields are defined exactly the same (Currency)

    The same form uses a similar reference to another query and it works fine . . .

    1) This Control Source works fine:   =DSum("[amount]","tblBuyerPmtSub","[BuyerID] = " & [ID])

    2) And so does this one (referencing a query):  =DSum("[Ttotal]","qrySaleLots","[BuyerID] = " & [ID])

    3) This one Produces the “#Error”: =DSum("[amount]","qryBuyerPmtSub","[BuyerID] = " & [ID])

    I have the exact same results in a Report, using the same DSum functions. . .

    Any ideas?

    Saturday, January 12, 2019 6:02 PM

Answers

  • What happens if you combine both expressions?

    =DSum("Amount", "tblBuyerPmtSub","BuyerID = " & [ID]  & " And MeetingID = " & DLookUp("[CurrentMeeting]","tblDefaultInformation"))

    Ken Sheridan, Stafford, England

    • Marked as answer by Big Itch Tuesday, January 15, 2019 3:18 AM
    Monday, January 14, 2019 11:31 PM

All replies

  • In qryBuyerPmtSub, is the field [amount] a numeric or currency data type?  Or is it perhaps a text field?

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, January 13, 2019 10:20 PM
  • 3) This one Produces the “#Error”: =DSum("[amount]","qryBuyerPmtSub","[BuyerID] = " & [ID])

    Hi Big Itch,

    I assume that in the query qryBuyerPmtSub you have two fields BuyerID, one from table tblBuyerPmtSub, and one from the joined table.

    In that case you must specify which BuyerID you want to use. So you could try:

            =DSum("[amount]","qryBuyerPmtSub","[tblBuyerPmtSub.BuyerID] = " & [ID])

    This works for both the single table and the query.

    Imb.


    • Edited by Imb-hb Monday, January 14, 2019 1:17 PM typo
    Monday, January 14, 2019 10:57 AM
  • 3) This one Produces the “#Error”: =DSum("[amount]","qryBuyerPmtSub","[BuyerID] = " & [ID])

    I have the exact same results in a Report, using the same DSum functions. . .


    What is the query's SQL statement?


    Ken Sheridan, Stafford, England

    Monday, January 14, 2019 12:01 PM
  • It is defined as currency in the table referenced in the query
    Monday, January 14, 2019 8:06 PM
  • That generates the same error . . .the [ID] reference origin is a tblMaster file, and that link to the proper ID seems to be working properly . . .ie. it gets the right data with the table reference, but not the qry
    Monday, January 14, 2019 8:12 PM
  • SELECT tblBuyerPmtSub.MeetingID, tblBuyerPmtSub.BuyerID, tblBuyerPmtSub.PDate, tblBuyerPmtSub.PmtType, tblBuyerPmtSub.Amount, tblBuyerPmtSub.Comment
    FROM tblBuyerPmtSub
    WHERE (((tblBuyerPmtSub.MeetingID)=DLookUp("[CurrentMeeting]","tblDefaultInformation")));

    The query results are accurate (pulling only the [CurrentMeeting] records from the tblBuyerPmtSub. . . 

    Monday, January 14, 2019 8:15 PM
  • The Currency data type is summable, so I think you'd better post the SQL of the query.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, January 14, 2019 8:16 PM
  • SELECT tblBuyerPmtSub.MeetingID, tblBuyerPmtSub.BuyerID, tblBuyerPmtSub.PDate, tblBuyerPmtSub.PmtType, tblBuyerPmtSub.Amount, tblBuyerPmtSub.Comment
    FROM tblBuyerPmtSub
    WHERE (((tblBuyerPmtSub.MeetingID)=DLookUp("[CurrentMeeting]","tblDefaultInformation")));

    And you've said that tblBuyerPmtSub.Amount is of the Currency data type.  How about tblBuyerPmtSub.BuyerID?  What is its data type?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, January 14, 2019 8:20 PM
  • Sorry, I'm a newbie here . . . did you see the SQL code I sent to another person? If not:

    SELECT tblBuyerPmtSub.MeetingID, tblBuyerPmtSub.BuyerID, tblBuyerPmtSub.PDate, tblBuyerPmtSub.PmtType, tblBuyerPmtSub.Amount, tblBuyerPmtSub.Comment
    FROM tblBuyerPmtSub
    WHERE (((tblBuyerPmtSub.MeetingID)=DLookUp("[CurrentMeeting]","tblDefaultInformation")));

    The query results are accurate (pulling only the [CurrentMeeting] records from the tblBuyerPmtSub. . . 

    Monday, January 14, 2019 8:21 PM
  • Sorry, I'm a newbie here . . . did you see the SQL code I sent to another person? 

    Not until after I'd posted that response.  I've followed up in that subthread.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, January 14, 2019 8:24 PM
  • It's a Number (Long Integer)
    Monday, January 14, 2019 8:27 PM
  • What happens if you combine both expressions?

    =DSum("Amount", "tblBuyerPmtSub","BuyerID = " & [ID]  & " And MeetingID = " & DLookUp("[CurrentMeeting]","tblDefaultInformation"))

    Ken Sheridan, Stafford, England

    • Marked as answer by Big Itch Tuesday, January 15, 2019 3:18 AM
    Monday, January 14, 2019 11:31 PM
  • You're a freakin' genius . . . Thanks so much for your help . . .that worked find.
    Tuesday, January 15, 2019 3:18 AM