How to use a multi-dimensional array

[Seth Schrock]

I have looked online, but I don't seem to be able to find the information that I'm wanting. Here is what I'm trying to do.

I have used the split function to create an array of parts of a string such as

Dim str as String
Dim strArray() as String

str = "Left(str, 8) & Mid(str, 10, 7)"
strArray = Split(str, "&")

Now, I'm needing to track the starting location and length for each string in the array.

I've never used a multi-dimensional array before, so I'm not exactly sure if this is the best solution or not or even if I would need a 2 or 3 dimensional array. I also thought that (not sure if this is possible) that I could create a user-defined data type that would have the two fields I needed and then declare the array as my new data type. I could also create my own custom collection using a class module. I suppose that I could even create a table and actually store the information, but I would rather not use that method. Any help in knowing which method would be the best and also how to implement that solution would be very appreciated.

[zmbd]

Seth,
I'm fairly certain of what you want; however, would you provide a few strings with the data you want tracked?

[ADezii]

You already have an Array of Strings and Sub-Strings using the Split() Function. You can now actually use 2 Parallel Arrays to store the Locations of the Sub-Strings within the Strings as well as the Major String Lengths as follows:

Dim strTestString As String
Dim varTestString As Variant
Dim intCtr As Integer
Dim intCtr2 As Integer
Dim astrStart() As Integer
Dim astrLength() As Integer

strTestString = "Philadelphia,ade,Halloween,ee,Encyclopedia,dia,Bytes,yt"

varTestString = Split(strTestString, ",")

ReDim astrStart((UBound(varTestString) - 1) / 2)
ReDim astrLength((UBound(varTestString) - 1) / 2)

For intCtr = LBound(varTestString) To UBound(varTestString)
  If intCtr Mod 2 = 0 Then
    astrStart(intCtr2) = InStr(varTestString(intCtr), varTestString(intCtr + 1))
    astrLength(intCtr2) = Len(varTestString(intCtr))
      intCtr2 = intCtr2 + 1
  End If
Next

Debug.Print "String", "Sub-String", "Location", "Length"
Debug.Print "---------------------------------------------------"

For intCtr = LBound(astrStart) To UBound(astrStart)
  Debug.Print varTestString(intCtr * 2), varTestString(intCtr * 2 + 1), astrStart(intCtr), astrLength(intCtr)
Next
  
Debug.Print "---------------------------------------------------"

OUTPUT:

String        Sub-String    Location      Length
---------------------------------------------------
Philadelphia  ade            5             12 
Halloween     ee             7             9 
Encyclopedia  dia            10            12 
Bytes         yt             2             5 
---------------------------------------------------

P.S. - I am not saying that this is the 'Optimal' Solution, but it is the first thing that came into my mind (LOL)!

[NeoPa]

Seth,

The absence from your post of any example string data makes this very difficult to help with. You'd find it difficult to follow anything said as it has no context for you (without providing a whole bunch of example data for you and hoping you follow what's being said - I hope you appreciate that's not the way to go about things).

Just generally then, Split() is great for creating an array, but doesn't work well with multi-dimensional arrays. That said, if you're processing through the data then each iteration of the inner loop can create a new array from the data within an outer array.

Obviously, if the data is originally stored in a string, then the inner and outer separators must be different character strings (generally a single character for each of course).

[Seth Schrock]

The example that I'm using is in line 4 of the code block that I posted.

str = Left(str, 8) & Mid(str, 10, 7)

So basically, the output that I'm wanting for the above example, would be this:

String_Function       Starting_Location        Length
Left(str, 8)                    1                                8
Mid(str, 10, 7)              10                              7

My current array has the contents of the String_Function column, but I'm needing a method to store the Starting_Locati on and Length columns.

I just realized what might be part of the confusion about the sample string. I'm not needing any information about the data that these string functions will be working on, but just the string functions themselves. So the output that ADezii posted isn't the data that I'm looking for, but the idea of the multiple arrays just might work.

Maybe a little more background of what I'm trying to do would help. I'm importing to text files into my database. Both have a matching field that I use to compare the lists. However, each table stores that field in a different format (added spaces and extra characters) and I don't know that the next time that I need to import these tables that the format will be the same. So I'm creating a form that has a textbox that allows me to type in the string functions that I'm needing applied to the fields to make their formats the same. I also have a textbox that shows some sample data. Once I enter the string functions (as in my example) I want those sections to be highlighted and the non selected text to not be highlighted. For example, if my data is 12345678 9012345 6 then I would want a display like this (using bold instead of highlight as I don't know how to highlight text in this forum) [12345678] [9012345] [6]The spaces would not be highlighted. So my thinking is that if I can get separate out the string functions (which I have done) and then find out where they start and how long they are in effect, then I can reverse engineer, if you will, to know which parts aren't selected and then I can put all these little strings (selected and non-selected) together with the correct highlights.

String Function          Sel./not sel.           Text Result
Mid(str, 1, 0)            Not                        ""
Left(str, 8)                Selected                "12345678"
Mid(str, 9, 1)            Not                        " "
Mid(str, 10, 7)          Selected                "9012345"
Mid(str, 17, 2)          Not                       " 6"
[B]Result[/B]                                        [B]12345678[/B] [B]9012345[/B] 6

Should I just create an array of the text results instead of working so much with the string functions?

[NeoPa]

Seth, The example you highlight is code. My post specifically referred to example data. I'm really not sure why you need this explained at all. I thought you were experienced enough for that fact to be blindingly obvious (from your earlier posts).

It seems you now have some sample data, but buried somewhere in the text. I'm happy to see if I can help, but I'm not inclined to dig through your long post just to find the data you've been asked to share.

NB. Example data can go a long way towards filling in the gaps in the logic of your explanation. Many people struggle to get their ideas across clearly. Effort should be expended, but ultimately very few questions illustrate this done well. Very few have such skills. In such circumstances example data can make a lot of difference.

[Seth Schrock]

I guess I'm seeing the string function code as the data since my other code is only working with the string function code at this point. Also, there are currently two formats of the actual data and the point of my code is to try to make it not matter what the actual data is like. However, I will give you some data in the same format as what I currently have. The real data is confidential, so I can't give you that.
Here is the data for the string functions that I posted earlier.

12345678 9012345 6
98765432 1098765 4
19283746 5192837 5
43215432 1765437 6

For the following data, I will be using the Left(str, 15) string function (to leave off the last digit):

1234567890123450
9876543210987650
1928374651928370
4321543217654370

In the future I might have other formats, which is why I'm creating this ability to enter the string functions in a text box and then have the query use what I entered instead of hard-coding this into the query.

I hope this helps. I apologize for the confusion on my end of what information it was that you were requesting.

[zmbd]

Seth,
are these always going to be numeric in nature?
have you looked at the regex?
you have add the reference to it in the library or late bind.

[Seth Schrock]

I think that they will be, but I suppose that there could be some circumstances where there would be astrix at the beginning.
I haven't ever heard of regex so no I haven't looked it up. I will do so now and see what it can do.

[zmbd]

(regular expressions - not a default library reference)
The only reason I bring it up is that it appears that you want only the first 15 numeric charactors of the input. Is that correct?

[ADezii]

Let's make the following assumptions:

1. Your Table Name is tblData with a TEXT Field named [Data] that contains your Numeric Strings.
2. You also have a Text Field on your Form named txtFormula.
3. Create a simple Query named qryFormula consisting of the following SQL:
   

   SELECT tblData.Data, fEvalFormula([Forms]![Form2]![txtFormula],[Data]) AS Result
   FROM tblData;

4. In the txtFormula Field, enter your Formula Name, a Length Value, and a Start Value seperated by Commas. If a Length or Start does not appply, enter 0 (Zero). For example to return 5 Characters from each Value starting at Position 8:
   

   Mid,5,8

   and to return the rightmost 10 Characters:
   

   Right,10,0

   and to return all Characters starting from the 6th:
   

   Mid,0,6

5. The Function used in the Calculated Field will:
   1. Parse the contents of the txtFormula which is passed to it.
   2. Remove all Spaces from each String.
   3. Determine what Function is passed as well as any Arguments.
   4. Determine Length & Start Values.
   5. Make the necessary Calculations.
   
   

   Public Function fEvalFormula(strFormula As String, strData As String)
   Dim strDataNoSpaces As String
   Dim varFormula As Variant
   Dim intLength As Integer
   Dim intStart As Integer
   
   varFormula = Split(strFormula, ",")             'Extract Formula
   intLength = varFormula(1)                       'Length
   
   strDataNoSpaces = Replace(strData, " ", "")     'Remove Spaces
   
   Select Case varFormula(0)                       'The actual Formula
     Case "Left"
       fEvalFormula = Left(strDataNoSpaces, intLength)
     Case "Right"
       fEvalFormula = Right(strDataNoSpaces, intLength)
     Case "Mid"        'May/may not have an Option Start Argument
         intStart = varFormula(2)                  'Start Argument passed
           If intLength <> 0 Then                 'Specified Length
             fEvalFormula = Mid(strDataNoSpaces, intStart, intLength)
           Else
             fEvalFormula = Mid(strDataNoSpaces, intStart)
           End If
     Case Else
   End Select
   End Function

6. The Query can be opened with Minimal Validation in the Click() Event of a Command Button on your Form.
   

   Dim strMsg As String
   
   strMsg = "You must supply at a Minimum a Formula, Length, and Start Values seperated by " & _
            "Commas"
   
   If UBound(Split(Me![txtFormula], ",")) < 2 Then
     MsgBox strMsg, vbExclamation, "Invalid Entry"
       Exit Sub
   End If
   
   DoCmd.OpenQuery "qryFormula"

7. Using Mid,0,6 as an entry in txtFormula, the Query would produce:
   

   Data	              Result
   12345678 9012345 6	67890123456
   98765432 1098765 4	43210987654
   19283746 5192837 5	74651928375
   43215432 1765437 6	43217654376

P.S. - Given your skill set, I'm sure no further explanation is needed. The Logic can easily be expanded to include additional functionality.

[NeoPa]

So, currently you have two separate formats of data, but you cannot preclude extra formats being added later. Correct?

Please, when you get a second, can you show your sample data along with the expected results. We have an understanding of the format which contains spaces - although the explanation contradicts the example somewhat in that it indicates the " 6" should not be included whereas your data (from post #5) indicates otherwise - but we have no clear understanding of what the other format should produce as a result. My guess would be, because the data in line #1 of each sample set of data contains fundamentally similar characters, that you are after similar results. In this case I suspect you want two strings - "12345678" & "9012345".

If I were looking to handle this I would create a table with four fields :

FirstStart    Numeric    Start position of first string.
FirstLength   Numeric    Length of first string.
SecondStart   Numeric    Start position of second string.
SecondLength  Numeric    Length of second string.

I see nothing to say how the code should recognise which format the data is found in for any particular file :-( so I have no suggestions for that.

If my understanding is correct the two records you need for the data we've seen and (hopefully) understand to date are :

FirstStart    FirstLength   SecondStart   SecondLength
     1             8              9             7
     1             8             10             7

Others can be added later when new formats come along.

The code for this, after the determination has been made as to which record of the table should be used, might be something like :

'strRaw contains a record of raw data from the table.
'rsVar points to the preselected record from the control table.
Private Function GetID(strRaw As String, rsVar As DAO.Recordset) As String
    With rsVar
        GetID = Mid(strRaw, !FirstStart, !FirstLength) _
              & "," _
              & Mid(strRaw, !SecondStart, !SecondLength)
    End With
End Function

Split(GetID(... ), ",") could be used to separate out the two elements or treat them as an array.

[Seth Schrock]

Sorry I haven't been able to get back sooner. I've been traveling all day.

First, to answer Z's question... With these two formats, I am getting the first 15 numbers from each. Both formats are the exact same number formatted differently. The one difference is that the last character is replaced by a 0 in the second format. Unfortunately, I have no control over the formats given to me so I can't just have the different systems format them the same. :(
I have heard of regular expressions (but not the regex abbreviation), but not really what they can do, so I'll look them up.

@Adezii While, your solution does look like it would be very efficient to pass the string functions to the query, the main problem that I'm having is coming up with a way to highlight the parts of the sample data that I have chosen to select using the Left, Right or Mid functions. I believe that the only way to do this is to separate out the parts between the selected parts and then piece the whole string back together alternating selected/not selected/selected so that I can highlight the selected text and not the text that wasn't selected. Thus I would need a system (like your parallel arrays that you suggested in your first post so that I can calculate which parts of the string weren't selected. I haven't been able to try your system out yet because I've been busy with Christmas traveling since I first posted the question.

@NeoPa, Here is the sample data with the wanted outcome beside it:

12345678 9012345 6               123456789012345
98765432 1098765 4               987654321098765
19283746 5192837 5               192837465192837  
43215432 1765437 6               432154321765437

and

1234567890123450          123456789012345
9876543210987650          987654321098765
1928374651928370          192837465192837
4321543217654370          432154321765437

You are also correct that I have two separate formats of data with extra formats possible in the future. However, I will only have to deal with two at a time as I will only have two lists given to me (if that makes any difference). However, from the standpoint of the piece of code that I'm attempting to write, only one format is being considered at one time.
From each format I want the resulting format to make it so that I can compare the data between the two lists. As should be shown (unless I mistyped some of the numbers) the resultant strings are now the same and thus I'm able to create a join based on this calculated field.

If I'm looking at the string 12345678 9012345 6, I would type in Left(str, 8) & Mid(str, 10, 7) as that would be the combination of string functions needed to get my desired outcome of 123456789012345. What I need my code to do is to be able to separate out the individual string functions (which I've done using the split() function on the & character), then split each string function down to get its starting point and effective length and store this information somehow, calculate out that there are no characters prior to the first bit of selected text, that there is a character between the bits of selected text (the space) and that there are some characters after the last bit of selected text (the space and the 6). So now I'll have stored the information to allow me to have the following information:

Mid(str, 1, 0)     =""     'Characters prior to selected text
Left(str, 8)   = "12345678"   'Characters in first bit of selected text
Mid(str, 9, 1)  = " "   'Characters between bits of selected text
Mid(str, 10, 7)  = "9012345"  'Characters in the second bit of selected text
Mid(str, 17, 2)  = " 6"   'Characters after the selected text

This would then allow me to loop through the list and create the following string to assign to a textbox that had rich text enabled (the exact HTML tags escape me right now, but I figured out what they need to be for real. I will just make up my own tags for this post :) )

Me.TextBox = "" & <Highlight>"12345678"</Highlight> & " " & <Highlight>"9012345"</Highlight> & " 6"

So my problem isn't getting my string functions to the query, but storing the information about the string functions that would allow me to make a separate list of the string functions necessary to get the text that wasn't selected by the user. I hope this all makes sense.

[zmbd]

then the regular expressions would work.
Regular Expressions haven't been available for VBA until just recently and they're not an easy use in VBA; thus, I've only a very rudimentary understanding however what I would do is:
pattern match "[0-9]|[.]"
Build the string from the objRegExp.Execu te(yourinputstr inghere)
You could have 123ABCDEF456789 = 123456789
You could have 00123A456b789c0 .ABCDEFG989789 = 001234567890.98 9789
etc...
would (or just the [0-9] for the pattern match and then the decimal wouldn't be included) then left(str,15) and you're done.

You could do the same buy takeing a character by character read of the input, keeping only the numeric. Tedious but it works.

[NeoPa]

I would suggest then, that instead of string functions that are passed, what you really need is to standardise on the Mid() function only, and then store, and pass, paired parameters of start position and length for every substring that you want to keep. I see no reason to identify the substrings that you want to ignore. You can ignore those.

This is where I was heading in my post when I described the table with the four fields.

The function wasn't quite as you need as it assumed you wanted an array of results, but it now appears from your latest post you want the separate substrings concatenated into a single, longer, string instead. this is easily accomplished by the simple expedient of leaving out the extra comms (,) from the code in that post :

'strRaw contains a record of raw data from the table.
'rsVar points to the preselected record from the control table.
Private Function GetID(strRaw As String, rsVar As DAO.Recordset) As String
    With rsVar
        GetID = Mid(strRaw, !FirstStart, !FirstLength) _
              & Mid(strRaw, !SecondStart, !SecondLength)
    End With
End Function