Results 1 to 4 of 4
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Report not showing Sum from a separate qry

    Trying to get a "Beginning Balance" for a financial report that is generated by selecting a date range.

    So if my report shows transactions from the date range 1/1/21 through 3/1/21



    then I need to Sum the amount off all previous transactions. I have done this in a separate query and the value is correct but I can not get the form to display it in the box

    it askes for a parameter and then shows an error
    Click image for larger version. 

Name:	Capture.PNG 
Views:	10 
Size:	160.7 KB 
ID:	44627

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Use a dLookup:
    =dlookup("[SumOfTransAmount]","[qry_FinRptGenSUMBefore]"). Glad to see you are replacing the hard-coded values!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    ugh I still don't understand why that works

    I tried
    =DLookUp("SumOfTransAmount","qry_FinRptGenSUMBefor e")
    Which works to bring up a total from a separate query in a form.

    what is it that the [brackets] add to the equation that makes it work in a report?

    Thanks

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think it is because of how Access (domain aggregate functions) "understands" the fields of a totals query. The field name is not hard-coded, Access is adding whatever you choose (+of) in the Total row to the field name you are " totaling" (i.e. SumOfTransAmount, CountOfID, etc.), but those would need to be enclosed in square brackets just like when you have a space in the field name "[Trans Amount]".

    In another query using the first query it would work as you already have the field name in the query designer where you choose it.

    Try this and see if it works:
    In your qry_FinRptGenSUMBefore query rename the first field: BeginingBalance:Sum([TransAmount]) and change the Sum to Expression in the Total row. Now use DLookUp("BeginingBalance","qry_FinRptGenSUMBefore") and see if it works.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-04-2018, 08:08 AM
  2. Replies: 9
    Last Post: 08-08-2016, 02:57 PM
  3. Replies: 5
    Last Post: 07-18-2014, 02:04 AM
  4. Replies: 15
    Last Post: 04-16-2014, 01:15 PM
  5. run a report from 2 separate tables
    By Kajinga in forum Reports
    Replies: 2
    Last Post: 11-23-2011, 05:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums