Results 1 to 10 of 10

DLookup to display date in a text box on a tab control where criteria is a text field

  1. #1
    zdjbel is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2012
    Posts
    25

    DLookup to display date in a text box on a tab control where criteria is a text field

    Hello,

    I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:



    =DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)

    Any ideas whats missing in this expression? I've tried every criteria variant I could find but to no avail.

    Thanks very much,

    Zoran

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,792
    Does this help?

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    399
    I always forget the correct format for dlookup's
    I have a basic form with 1 text box on it, and in that text box I have the dlookup variants listed.
    here ya go...

    Dlookup Basics...
    For numbers DLookup("FieldName" , "TableName" , "field = 123456")
    For text DLookup("FieldName" , "TableName" , "field= 'my_string'")
    For dates DLookup("FieldName" , "TableName" , "field= #My_date#")

    Form Reference...
    For numbers DLookup("FieldName", "TableName", "field = " & forms!FormName!Control)
    For text DLookup("FieldName", "TableName", "field = '" & forms!FormName!Control & "'")
    for dates DLookup("FieldName", "TableName", "field = #" & forms!FormName!Control & "#")

    combination of the above...
    DLookup("FieldName", "TableName", "field_a = " & Forms!FormName!Control_a _
    & " AND field_b = '" & Forms!FormName!Control_b & "'" _
    & " AND field_c =#" & Forms!FormName!Control_c & "#")

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,792
    Was my link invisible, or it was just worth repeating?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    399
    ppl come here for help not to be redirected someware else. goodby!

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,792
    So your opinion has changed since yesterday?

    Quote Originally Posted by trevor40 View Post
    use a browser to search for dlookup, select the top answer from microsoft, there should be examples of dlookup options.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    zdjbel is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2012
    Posts
    25
    Quote Originally Posted by pbaldy View Post
    Does this help?

    DLookup Usage Samples
    Hi pbaldy,

    Thanks for the suggestion. Been there and many place else, tried them all but no luck. I'm thinking that I'm making a simple mistake somewhere that is so obvious that I can't see it. I was hoping that another pair of eyes looking at it would spot it. I'll continue tonight after work.

    Thanks very much, Z.

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,792
    You said the criteria field is text, so try

    =DLookUp("DateOrdered","tDateOrdered","PrNumber='" & [PrNumber] & "'")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    zdjbel is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2012
    Posts
    25
    Yes, that worked! I had tried the same expression but without the last ampersend but of course, without result. Thank you, I learned something today which makes it a good day.

    Thanks,

    Z

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,792
    Happy to help!
    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. Replies: 8
    Last Post: 10-30-2013, 02:28 PM
  2. Display value in a text box after scheduled date
    By venu_resoju in forum Forms
    Replies: 5
    Last Post: 01-06-2013, 01:13 AM
  3. DLookup() for Text Box Control Source
    By tylerg11 in forum Forms
    Replies: 3
    Last Post: 02-06-2012, 02:21 PM
  4. Display field as text box
    By thekruser in forum Forms
    Replies: 5
    Last Post: 08-31-2010, 10:37 AM
  5. Replies: 1
    Last Post: 03-02-2006, 06:17 AM

Posting Permissions

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