Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

MS Access - VBA - Arrays

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.
  • 4
  • 2
1 Solution
RyanProject Engineer, ElectricalCommented:
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
keschusterAuthor Commented:
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
RyanProject Engineer, ElectricalCommented:
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

keschusterAuthor Commented:
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 ;-)
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{ ")
RyanProject Engineer, ElectricalCommented:
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.
RyanProject Engineer, ElectricalCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now