Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Hi Ajax,

    Thanks for the recent help, by messing about myself with Mid, Len, Trim and Case functions on this project I have learned quite a lot more about VBA - Many Thanks!


    Since the last update, I have got the code to run again without generating an error.

    Removed the extra Parentheses after 10,1,2 and added to the end of the code (As below)

    Although the code runs and fills out the boxes of the frmCodeVariables Var Textboxes (See screenshot)

    A single digit is fine but a double integer on the end of the string it ignores the second digit, IE 14 = Enfield, It throws up Bolton (Taking the 1 only)

    Sorry to be a pain, can you advise what is going wrong please?

    Is it my Lookup table structure maybe? Should I lose the Case 11 Var maybe?

    Tried changing a few things around but still no joy

    Once again thanks for the help so far which has been brilliant!


    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, IIf(I < 11 Or Len(Code) = 10, 1, 2)) & "'")
        Debug.Print coltolookup
    Next I
    End Sub

    Attachment 28005
    Code Machine HourC Date1 Date2 Company Min1 Min2 MonthC YearC Site1 Site2 SiteName
    1





    W



    1
    Bolton
    10 2


    W



    1 0
    11 3


    W



    1 1
    12 4


    W



    1 2 Variety
    13 5


    W



    1 3 Wednesbury
    14 6


    W



    1 4 Enfield
    15 7


    W



    1 5 Tuscany
    16 8


    W



    1 6 Stockton
    17 9


    W



    1 7
    18



    W



    1 8 Bristol
    19



    W



    1 9 Gluten Free
    2



    W






    20



    W






    3



    W



    3
    Burnley
    4



    W






    5



    W






    6



    W



    6
    Newburn
    7



    W



    7
    Bellshill
    8



    W



    8
    Eastwood
    9



    W






    A
    0


    0
    1 2015


    B
    1


    1
    2 2016


    C
    2


    2
    3 2017


    D
    3


    3
    4 2018


    E
    4


    4
    5 2019


    F
    5


    5
    6 2020


    G
    6



    0 7 2021


    H
    7



    1 8 2022


    I
    8



    2 9 2023


    J
    9



    3 10 2024


    K
    10



    4 11 2025


    L
    11



    5 12 2026


    M
    12
    9

    6
    2027


    N
    13
    8

    7
    2028


    O
    14
    7

    8
    2029


    P
    15
    6

    9
    2030


    Q
    16
    5



    2031


    R
    17
    4



    2032


    S
    18
    3



    2033


    T
    19
    2



    2034


    U
    20
    1



    2035


    V
    21
    0



    2036


    W
    22 3
    W


    2037


    X
    23 2




    2038


    Y
    24 1




    2039


    Z

    0




    2040



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


    Using my new found Knowledge :-)

    I have now gone down this thought path.

    Take the first 9 String items only (Code)

    and at the end after Next I, Use Mid to get the last 2 Characters (1 digit or the last 2 digits) Place in a Textbox and use Dlookup on the returned value against the SiteName column.

    What's your thoughts as a Programmer?

    Can I make that work or will it come unstuck later down the line, Is there a better quicker option available?

    Thanks Darren



    Code:
    Dim I As Integer
    Dim coltolookup As String
    For I = 1 To 9
    
        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 = "SiteNumber"
           Case 11
               coltolookup = "SiteName"     '''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) = 11, 1, 2)) & "'")
        Me.Var10 = Mid(Code, 10)
        Me.Var11 = DLookup(Var10, "tblCodeCracker", SiteName)
        
        
        
        
        Debug.Print coltolookup
    Next I

  3. #18
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    looks like I'vbe included an extra bracket - there should only be 2.

  4. #19
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Thanks for your help on this project.

    Quite chuffed as I have managed to sort it but I would really value you opinion as a programmer.

    Is my way okay or was their a quicker route by another piece of code/method?

    I have made the loop 1 to 9 and used the Mid(Code, 10) to capture the last few Character's of the string.


    Then a Dlookup on Var10 to match the number to the site.

    Code:
    Dim I As Integer
    Dim coltolookup As String
    For I = 1 To 9
    
        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 = "SiteNumber"
           Case 11
               coltolookup = "SiteName"     '''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) = 11, 1, 2)) & "'")
        Me.Var10 = Mid(Code, 10)
        Me.Var11 = DLookup("[SiteName]", "qryProdSite")
        
        
        
        
        Debug.Print coltolookup

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Is my way okay or was their a quicker route by another piece of code/method?
    You have basically used the excel way - domain functions such as dlookup are the slowest way of getting data, but also the easiest.

    there are often many ways of achieving any requirement - read the table into an array, normalise the table as one table, have separate tables for each column, as I think I suggested in an earlier post all the number values can be calculated with an algorithm. And you could create the date in a single calculation rather than assigning to individual vars which you then combine, as you do in your excel example.

    I did ask where you were using this and you said a single form. If you wanted it in a continuous form or datasheet I would have suggested an alternative method, but this was closest to your current basis. For a single form this is as good a way as any and not noticeable slower than any other basis.

    Remember Access is not a bigger Excel, it works in a completely different way and most importantly, data is stored in tables and presented in forms and reports whereas Excel data storage and presentation are the same thing. Because of this Excel stores data 'short and wide', Access stores data 'tall and narrow'

  6. #21
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    all the number values can be calculated with an algorithm.
    Thanks for your support and good advice.

    I will have learn more about creating algorithms, I don't know much about that and also Arrays sound good too.

    I'll now marked the Problem as solved and take on board your Excel and Access comparisons.

    Cheers Darren

  7. #22
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    arrays will be faster because everything is loaded into memory and is referenced a bit like the excel Index function. Algorithms are just complex calculations - (a/b*c)+d - type of thing, rather than working it out in stages such as x=a/b, y=x*c, z=y+d

Page 2 of 2 FirstFirst 12
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