Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164

    VLookup Excel - move to a table and query in Access?

    Hi,

    My place of work uses an Excel Code cracker, the date ticket code is entered in from the product and the spreadsheet reveals the following information;

    1. Machine ID
    2. Time produced
    3. date produced
    4. Bakery the product was made.


    I now would like to move this into and part of an Access form/DB for quality.

    What's the best approach for Programming this in, code, table design and query design?

    Any help would be appreciated



    Thanks

    Attachment 27964
    Attachment 27965

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a query that looks at the box on the form, button opens the query.
    qry looks like: select * from table where [part]=forms!myForm!txtBox

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Your code seems limited - what happens when you want to go beyond 2040? (although appreciate application may be long gone by then) or you have more than 10 locations or 26 companies?

    Also not clear if this is only to be used in a single form like the one you have shown or in a query, form or report returning many codes. If the latter, you would require a different more efficient solution.

    For example, many of the values are numeric and fixed in range so for these you could use a calculation based on the ascii value of character in the code, offset by the type of value (hour, month, etc) you want returned.

    But to mimic what you have, you can have a table structured as you have and use dlookups in your form to derive the decoded values in much the same way as your lookups work at the moment.

    But to answer what is the best approach needs answers to the questions in the first two sentences above. And also clarify the meaning of the data - for example you have a production date of 10 Jan 2015 and a best before date some 1 1/2 months earlier. This may be sample data, but is it something that should be either prevented from being entered in the first place, highlighted as an error or just ignored?

  4. #4
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Hi,

    Just to clarify, the user types in the code in the traceability textbox.

    Ideally the user then clicks decode and in a text window below, all the production trace information is revealed.

    The best before date is an error so please ignore.

    the code will always be 10 or 11 characters long:

    IE: 9hyvw dlaa8 , the W is always the 5 the character and the 10th and sometimes 11th will always be a numerical digit. (Some sites have 2 digits)

    8 is Eastwood, 3 is Burnley, 12 is Variety, 15 is Tuscany etc. etc.

    The code will only be needed to be utilised from 1 form as below.

    How would I structure the table?
    Would I have all the Lookup table elements in one table as different fields and use a dlookup from the traceability textbox?
    What method would be best for each grabbing each letter for the dlookup from the textbox?
    How would I program this from pressing 1 button?

    thanks both for the support




    Attachment 27971

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you might as well structure the table as you already have it - then use the access dlookup function in much the same way as the excel vlookup.

    You can either have a long single formula as a controlsouce to determine the date for example, or (presumably as you have in your excel example) - individual lookups then combined to form the date. Performance wise it probably won't make much difference.

  6. #6
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Thanks for the advice.

    As I am still relatively new to programming, would I break down the 10 characters one by one by a Mid function?
    Drop each result into a hidden textbox and use dlookup to get the result of each?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    would I break down the 10 characters one by one by a Mid function?
    yes. In vba it might be something like


    Code:
    dim I as integer
    dim lookupvalue as string
    for I=1 to len(code)
        ...decide which column you want    
        lookupvalue=dlookup("columntolookup","lookuptable","code ='" & mid(code,i,1) & "'")
        ...do something with lookupvalue
    next I

  8. #8
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Hi Ajax,

    Thanks for the code above, I have spent time understanding how it works.

    for I=1 to len(code) counts the letters one by one in a loop via the mid function, moving across one letter at a time.

    I get that and the logic behind it.

    As you can see from the screenshot, I have debugged print and it loops through nicely and pulls all the characters in one by one.

    I struggling to work out the next step and how to pass the values around, match it to the tblCodeCracker table, produce the correct value and return the value back in a text box in the form.

    I have gone down the thought path of replicating the VLookup Excel cell boxes and including that into the form as hidden temp text boxes to pass the values around, is this the best approach?

    I have also gone down a query result but I'm getting a little muddled with the correct coding structure and steps order.

    Attachment 27991Attachment 27992

    Attachment 27993

    Attachment 27994

  9. #9
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    To add further, on for loop i number 1
    I extract 1 Character each time.
    How do I place ..... do something in the middle before the Next I.
    I'm presuming if I drop each letter in a separate textbox, would I then use a query for each textbox?

    Dlookup form textbox result against a query of the tblCodeCracker.

    I'm confused ����
    Do I somehow make loop1 result Variable 1, variable2 to 10 or 11?

    Thanks for the help so far.

    Thanks

    Darren

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I would think so - or you can either have some algorithm to build the date based on the value of I so you don't need the variables

  11. #11
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    I'm also confused on the how I place the Next I in a different place each time.
    Ie textbox 1, box 2 , box 3 etc.
    Or I am getting it all wrong?
    Get the letter from the string, next step Dlookup and anaylize it against the table, place result in form textbox.
    Next I

    The end result for the form user needs to be displayed as:

    Attachment 27996


    Ignore the Best Before

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    if you name your controls var1, var2 etc you can use


    Code:
    dim I as integer
    dim coltolookup as string
    for I=1 to len(code)
        select case I
          case 1
              coltolookup=""Hourc"
          case 2
              coltolookup="Min1"
          case 3
          etc
        end select
        me("var" & I)=dlookup(columntolookup,"lookuptable","code ='" & mid(code,i,1) & "'")
        
    next I
    note the changes in the dlookup line

    for the future rather than providing screenshots of your code, copy and paste it to the thread, surround with the code tags (the # button). Makes it easier to adapt at my end

  13. #13
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Hi Ajax,

    The code worked a lot better, thanks for that, I'm nearly there.

    For demo purposes only, I have made a decoding form so I can understand it better as the code works the magic, in the project and for the end user this will be hidden away in the background of the main form.

    I have one more problem though, the code needs tweaking on the end slightly but I am not sure how or what to add.

    Basically, I need the last 2 letters in the string to count as 1 item (Double Integer), the last 2 (10,11) will always number digits or sometimes 1 digit .

    IE: 8 = Eastwood, 7 = Bellshill, 15 = Tuscany, 14 = Enfield

    I need to lose the Var11 some how and the last 2 added together for the lookup, Mid Char number 10 =1 and Mid Char number 11 = 5 Var10 will then equal = 15

    Is this achievable?

    Code:
    Private Sub Decode_Click()
    Dim I As Integer
    Dim coltolookup As String
    For I = 1 To Len(Code)
        Select Case I
        
          Case 1
               coltolookup = "Code"
          Case 2
              coltolookup = "HourC"
          Case 3
              coltolookup = "Date1"
           Case 4
               coltolookup = "Date2"
           Case 5
               coltolookup = "Company"
           Case 6
               coltolookup = "Min1"
           Case 7
               coltolookup = "Min2"
           Case 8
               coltolookup = "MonthC"
           Case 9
               coltolookup = "YearC"
           Case 10
               coltolookup = "SiteName"
           Case 11
               coltolookup = "Site2"     '''Need to lose this and combine 10 and 11 together as 1 digit
          
          
        End Select
        Me("Var" & I) = DLookup(coltolookup, "tblCodeCracker", "Code ='" & Mid(Code, I, 1) & "'")
        Debug.Print coltolookup
    Next I
    End Sub
    Attachment 27998

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    perhaps use an if statement based on the length of the code in the dlookup not tested but something like

    DLookup(coltolookup, "tblCodeCracker", "Code ='" & Mid(Code, I, iif(I<11 or len(code)=10,1,2))) & "'")

  15. #15
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Tried a few times but suffered a few errors, I get what you mean I think, If the Len is 11 add 10 and 11 together?

    I had to remove the parentheses at the end as the code was red for error and amend IIF to capitals as it did not like it, the code now runs but throws up an error as 3075

    Code:
    Dim I As Integer
    Dim coltolookup As String
    For I = 1 To Len(Code)
        Select Case I
        
          Case 1
               coltolookup = "Code"
          Case 2
              coltolookup = "HourC"
          Case 3
              coltolookup = "Date1"
           Case 4
               coltolookup = "Date2"
           Case 5
               coltolookup = "Company"
           Case 6
               coltolookup = "Min1"
           Case 7
               coltolookup = "Min2"
           Case 8
               coltolookup = "MonthC"
           Case 9
               coltolookup = "YearC"
           Case 10
               coltolookup = "SiteName"
           Case 11
               coltolookup = "Site2"     '''Need to lose this and combine 10 and 11 together as 1 digit
          
          
        End Select
        Me("Var" & I) = DLookup(coltolookup, "tblCodeCracker", "Code ='" & Mid(Code, I, IIf(I < 11 Or Len(Code) = 10, 1, 2))) & "'"
        Debug.Print coltolookup
    Next I
    Attachment 27999

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-14-2015, 02:19 AM
  2. Replies: 1
    Last Post: 02-21-2014, 04:23 AM
  3. Replies: 2
    Last Post: 10-09-2012, 03:20 PM
  4. Access and the concept VLOOKUP in Excel
    By Bob Blooms in forum Access
    Replies: 1
    Last Post: 08-27-2012, 12:28 PM
  5. Excel VLookup vs Access IIF statement
    By Huddle in forum Queries
    Replies: 9
    Last Post: 02-02-2012, 11:00 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