Results 1 to 5 of 5
  1. #1
    jeran042 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2016
    Posts
    3

    Return a Value on DLookup when answer is an Expression?

    How do you pull an answer from a Dlookup when the answer is a calculated field?

    I have tried to build the formula as if it was looking for a numeric answer:


    Code:
    DLookup("YTD TOTAL" , "qrySalaries" , "DEPARTMENT= 3200")
    And as if it was a text string:
    Code:
    DLookup("YTD TOTAL" , "qrySalaries" , "DEPARTMENT= '3200'")
    I have also tried to use the expression builder to just point to the field in the qry, and I still get an #error message

    I should also mention that when I use this exact formula to look for a particular month, say "JANUARY", it pulls the correct number?


    Thanks in advance for any help. I should also say that I did a good amount of research before I posted this question, and now I am up against a deadline (unfortunately) and any help would be greatly appreciated,

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Dept SHOULD be a string. If the query doesnt stipulate, then you can FORCE it to be string via CSTR function....
    CStr( expression )

    =DLookup("[YTD TOTAL]" , "
    qrySalaries" , "[DEPARTMENT]= '3200'")


    NOTE: field names with spaces could be a problem...ALWAYS put brackets around them.

  3. #3
    jeran042 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2016
    Posts
    3
    Firstly, thank you for the reply,
    Second, and interestingly (or frustratingly) enough, this does not return any value. It just shows a blank text box?
    Department is definitely text, and the YTD TOTAL is formatted as currency. I have tried adding the "_" but an still having the same issue

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    First, test the query to make sure that you are getting the right value. Open the query in design view, add 3200 under department and run the query.

    Next, add a debug to your VBA code:
    - in the place where the value is being shown (is there a filter being applied or an AfterUpdate routine?), add Debug.Print DLookup......... See what is displayed.

  5. #5
    jeran042 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2016
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    Dept SHOULD be a string. If the query doesnt stipulate, then you can FORCE it to be string via CSTR function....
    CStr( expression )

    =DLookup("[YTD TOTAL]" , "
    qrySalaries" , "[DEPARTMENT]= '3200'")


    NOTE: field names with spaces could be a problem...ALWAYS put brackets around them.

    I did manage to get it to work,

    I believe the brackets around "YTD TOTAL" was the answer,

    Thank you all for your help, I worked on this for a while and had no luck, it was GREAT to be able to reach out for some guidance!

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

Similar Threads

  1. DLookup return value
    By faythe1215 in forum Programming
    Replies: 3
    Last Post: 03-15-2015, 06:18 PM
  2. Replies: 12
    Last Post: 06-13-2014, 01:02 AM
  3. Dsum return wrong answer
    By mikichi in forum Access
    Replies: 4
    Last Post: 03-03-2014, 11:17 AM
  4. Return DLookup result as date?
    By kman42 in forum Access
    Replies: 8
    Last Post: 04-22-2011, 11:35 AM
  5. Dlookup to match two criteria and return value
    By randolphoralph in forum Programming
    Replies: 20
    Last Post: 05-20-2010, 12:27 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