#name error on dlookup field in MS Access report

Posted on 2012-09-13
Last Modified: 2012-09-13
Hi there

I have a DLOOKUP function on a field in a MS Access report that is returning a #NAME? error.  The syntax for the function is:


The "Change" value is calculated as: ([FY1213]-[FY1112])/[FY1112] and is noted as an expressions whereas the FY1112 and FY1213 values are Summed.  So, in the query, you have:

FY112 - Sum
FY1213 - Sum
Change - Expression

Can someone explain to me why this is not working?  I have DSUM functions in the same report and they are working quite well.

Question by:staceymoore
    LVL 61

    Accepted Solution

    Try this:

    =DLookUp("[Change]","[qry_PYTD_vs_CYTD_Performance_BBLS_Pct]","[ProductLongName]= '" & [txtProductLongName] & "'")

    Open in new window

    LVL 61

    Expert Comment

    The above is for ProductLongName as a text field.  If it is numeric, try this:

    =DLookUp("[Change]","[qry_PYTD_vs_CYTD_Performance_BBLS_Pct]","[ProductLongName]= " & [txtProductLongName] )

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now