MS Access - VBA - Arrays

Posted on 2012-08-23
Last Modified: 2012-08-24
I'm trying to figure out how to setup an array to hold these values ( I'm going to hardcode them into the array

{   0    P
Z   0   P
A   1   P
B   2   P
C   3   N
D   4  N
E   5   N

The first column will be the value I want to search for, the 2nd and 3rd columns I will want to return from the search of the array.  

Need to know how to do this.
Question by:keschuster
    LVL 13

    Expert Comment

    Hideous brute force coding method.  If that's all the bigger the data is, then this is fine.  If its much bigger, I'd store it in a table.

    dim arr(6,3) as string
    arr(0,0) = "{"

    You can easily search it. Here's 1 way...
    for i=0 to 6
      select case arr(i,0,0)
      case "{}"
      case "z"
      end select
    next i

    Author Comment

    This is the full list.
    '{   0P
    'Z   0P
    'A   1P
    'B   2P
    'C   3P
    'D   4P
    'E   5P
    'F   6P
    'G   7P
    'H   8P
    'I   9P
    '}   0N
    'J   1N
    'K   2N
    'L   3N
    'M   4N
    'N   5N
    'O   6N
    'P   7N
    'Q   8N
    'R   9N
    '-    N
    '+    P

    I hear ya on sticking it in a table.  I need to evaluate data in a few hundred thousand rows and several columns based on this set of data then update to 3 other fields.  I was wanting to eliminate a lookup to another table by getting these values into an array in memory that I can search on while processing each row.  I'm thinking that might speed things up a bit.  Do you feel differently
    LVL 13

    Expert Comment

    Depends what kind of processing you have to do.  Anything that you can process natively in SQL will be quicker than pulling it to a recordset, manipulating it, and putting it back.

    If you do have to process it in code, then a static array is the quickest way to access random data.

    Author Comment

    Let me give you some more an tell me what you think.  I have a large table with 3 columns with data like this...


    Notice the last character of each string.  I need to get the last chararacter of each string - then look it up in the list above to return number and the P or N.  The number replaces the character at the end and the P or N determines if the number is negative or positive.

    Then format with decimal.  The result would look like this

    000000528745{     52,874.50
    534496I                 53, 449.69
    000000015558E    1,555.85
    000000216400}     -21,640.00
    433176-                  -4,331.76
    16800+                   168.00

    I want to do this on one sql statement using a custom to do this conversion.
    What do you think.  Gotta run out but will be back ;-)
    LVL 29

    Accepted Solution

    How about if you use a Select Case statement in a function?
    Function DecodeNumber(strNumber As String) As String
        Dim symb As String, strLastNum As String
        Dim isNeg As Boolean
        strNumber = Trim(strNumber)
        symb = Right(strNumber, 1)
        Select Case symb
            Case "Z", "{"
                strLastNum = "0"
            Case "A" To "I"
                strLastNum = CStr(Asc(symb) - 64)
            Case "}"
                strLastNum = "0"
                isNeg = True
            Case "J" To "R"
                strLastNum = CStr(Asc(symb) - 73)
                isNeg = True
            Case "-"
                isNeg = True
            Case "+"
        End Select
        DecodeNumber = Val(Left(strNumber, Len(strNumber) - 1) & strLastNum) * IIf(isNeg, -1, 1) / 100
        DecodeNumber = Format(DecodeNumber, "#,###.00")
    End Function

    Open in new window

    In your query, you can call your function with DecodeNumber("000000528745{ ")
    LVL 13

    Expert Comment

    Yes, that can be done in an SQL statement.  Strange request, but ok.

    SELECT val(left(data.text,len(data.text)-1) & ref.val2)/100*iif(val3="N",-1,1) as newval
    FROM data INNER JOIN  ref ON right(data.text,1)=ref.val1

    ref has 3 columns Val1,2,3 corresponding to your "array"
    data has a column of your lookups which i called [text].  You may need to include a trim if theres spaces.
    LVL 13

    Expert Comment

    It would be a bit quicker to replace those Ps and Ns with 1 and -1, you can then knock out the IIF.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now