Results 1 to 7 of 7
  1. #1
    SteveHale is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    10

    DLookup with Like only recognizes the first letter in the Source Table


    Hi all,


    The idea:

    When a user types in a worker code like "AA", it should pull up the First instance of an "AA" from the Source Table and display the corresponding Bureau from the Source Table.


    What it should be doing:

    "AA" is the East Bureau. If the user enters "AA1" or "AA12345", the value for Bureau should always say "EAST".


    What's happening:

    The output is just finding the record in the Source Table but the output goes blank when you enter a value that differs. If you enter "AA12345" or "AAR2D2", the output is blank. The expected output for Bureau should be "EAST".


    What am i missing?



    Code:
    Bureau = DLookup("Bureau", "tbl_WorkerCode", "WorkerCode Like '*" & [Forms]![frm_Main]![WorkerCode] & "*'")
    Any help or ideas would be great!

    -Steve

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Well if "AA12345" or "AAR2D2" do not exist?, what do you want to happen?

    Else, strip off the first two characters from the string and search on that?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Use a combobox to select worker code. Include Bureau field in combobox RowSource.

    SELECT ID, WorkerCode, Bureau FROM tbl_WorkerCode ORDER BY WorkerCode;

    Reference combobox column by index to grab Bureau. Expression in textbox: =cbxWorker.Column(2)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    SteveHale is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    10
    Hi again,

    @June7 - the original field was a combobox. we started with 20 WorkerCodes and since the growth, it has now ballooned to 744 WorkerCodes with new ones being added and/or replaced.


    @Welshgasman - As new WorkerCodes are being introduced, i would like the WorkerCode field in the form recognize the first 2 or 3 characters entered by the user and display the corresponding Bureau.

    I have since revised the code from the original post to this...

    Code:
    Bureau = DLookup("Bureau", "tbl_WorkerCode", "WorkerCode Like '" & WorkerCode & "*'")
    The results are the following:

    "AA" = EAST
    "A" = EAST
    "A1" = BLANK
    "AA1" = BLANK
    "BB" = SOUTH

    I have a sample DB (680k file size) but i am only allowed a 500k sized upload.

    Should i give up on DLOOKUP and look elsewhere?

    -Steve

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Compact and repair, then zip the db. Upload the zip file.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So only need to match on first letter?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    SteveHale is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    10
    Hi all,

    I just figured it out.

    Code:
     Private Sub WorkerCode_AfterUpdate()
    
    Dim BureauValue As String
    Dim WorkerCodeValue As String
    
    
        ' Get the value of WorkerCode field
        WorkerCodeValue = Me.WorkerCode.Value
    
    
        ' Extract the first two characters of WorkerCode field
        WorkerCodeValue = Left(WorkerCodeValue, 2)
    
    
        ' Look up the Bureau value in tbl_WorkerCode based on the first two characters of WorkerCode
        BureauValue = DLookup("Bureau", "tbl_WorkerCode", "WorkerCode Like '" & WorkerCodeValue & "*'")
    
    
        ' Set the Bureau field value to the retrieved value
        Me.Bureau.Value = BureauValue


    Thank you @June7 and @Welshgasman for staying with me and providing your thoughts!!!

    -Steve

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

Similar Threads

  1. DLookup as textbox source
    By Lhoj in forum Forms
    Replies: 13
    Last Post: 07-30-2021, 03:22 AM
  2. Replies: 5
    Last Post: 01-20-2018, 03:40 PM
  3. Replies: 4
    Last Post: 09-18-2012, 05:07 AM
  4. DLookup in Control Source
    By bgephart in forum Forms
    Replies: 2
    Last Post: 08-28-2012, 02:06 PM
  5. Replies: 4
    Last Post: 05-09-2012, 07:20 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
  •  
Other Forums: Microsoft Office Forums