Results 1 to 13 of 13
  1. #1
    craigger01 is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2018
    Posts
    7

    DLookUp Function in Report Footer

    I'm using a text box in an Access report footer. I would like it to return a value from a select query I've made. The following is what I'm using in the control source in the text box:



    =Nz(DLookUp("[Total Sports Revenue]","Sports Revenue Aggregate Final","Sports = 'CLEVELAND INDIANS'"),0)

    I want the function to go into the query called 'Sports Revenue Aggregate Final' where it will look in the column called 'Total Sports Revenue' and return a currency value if it finds 'CLEVELAND INDIANS' in the 'Sports' column. All the text box keeps returning is #ERROR.

    Is there something wrong with my syntax? Please help. Thanks so much!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You may need to bracket the query name due to the inadvisable spaces.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    craigger01 is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2018
    Posts
    7
    I tried that. Didn't work: =Nz(DLookUp("[Total Sports Revenue]","[Sports Revenue Aggregate Final]","Sports = 'CLEVELAND INDIANS'"),0)

    Does this have anything to do with me putting this in a report footer? I thought about the spaces too. It doesn't seem to mind the brackets, but still returns #ERROR. Does it have to do with the criteria?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, just tested and it worked fine. I could make it return #ERROR if something was misspelled. If that doesn't work, can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, make sure Sports isn't a lookup field that's actually expecting a numeric value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    craigger01 is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2018
    Posts
    7
    Can you elaborate on a "lookup field"? The 'Sports' column contains names of teams (i.e. text strings).

  7. #7
    craigger01 is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2018
    Posts
    7
    Here is a screenshot of what I'm doing. You can see where I've implanted the function code into the Control Source field in Properties. The text box highlighted in yellow is the actual text box. The code is the same: =Nz(DLookUp("[Total Sports Revenue]","[Sports Revenue Aggregate Final]","Sports = 'CLEVELAND INDIANS'"),0)

    It just keeps returning #ERROR and I don't know why. Seems like it should be working. The query (Sports Revenue Aggregate Final) is just a simple aggregate select query that adds up totals from another query. It has 2 columns...one is a text field with the sports teams listed and the other is a numeric field, formatted to currency, with the dollar totals.

    Any other ideas?

    Click image for larger version. 

Name:	Database Screenshot.jpg 
Views:	7 
Size:	143.1 KB 
ID:	35637

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It sounds like it should work. Can you attach the db here, after compacting and zipping?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    craigger01 is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2018
    Posts
    7
    I'm not real keen on sharing the information contained in it. Is there another way?

  10. #10
    craigger01 is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2018
    Posts
    7
    Let me ask this. Could I solve this with VBA code? If so, where would I put it? 'Event'>>'On Click'?

  11. #11
    craigger01 is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2018
    Posts
    7
    Ok, sorry. I've made progress. I turned the query into a basic table and it worked. I think what's happening is that 'Sports Revenue Aggregate Final' depends on 'Sports Revenue Aggregate', which depends on the query called 'Sports Revenue'. The first query, 'Sports Revenue', asks for the user to input a year so that it can return only data from that year. The whole report utilizes that query as its record source. When I'm asking the text box to pull from the 3rd query, it is probably doesn't know what to do and so it's erroring out. If I find this is the solution, I'll close this thread. Thanks for the help!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You certainly shouldn't post real data. I suspect you'd run into a similar problem with VBA, as you'd still have to grab the data from the query. Can you post a picture of the query results? I'm just trying to get a feel for the data, and all the names. Is the Sports field a lookup? In the table you're pulling data from, is the Lookup tab filled in? What's the data type of that field?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ah, we were typing at the same time. Most of us use forms to gather user input, which gives you more control. That may solve this problem if the query uses bracketed criteria like [Enter blah].
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Force Report Footer to before Page Footer
    By Charles CL in forum Reports
    Replies: 15
    Last Post: 01-21-2016, 12:06 PM
  2. IIf Function in Report Footer
    By Ron H. in forum Access
    Replies: 3
    Last Post: 12-21-2013, 04:05 AM
  3. Replies: 7
    Last Post: 10-04-2013, 11:45 AM
  4. Replies: 5
    Last Post: 06-27-2013, 02:49 PM
  5. Using "Count" function in a report footer
    By GraeagleBill in forum Reports
    Replies: 5
    Last Post: 05-11-2013, 03:42 PM

Tags for this Thread

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