Accessing each Character in the Record Field

[subedimite]

In VBA Access,

How can I read a filed from a table and then read each record in the field and then access each character insided the record for that filed. should I use a split function.

I tried this

Do Until recordset1.EOF
  
  UpdateTable_sub_data = findSubCommands(recordset1![data])
  
  recordset1.MoveNext
 
 Loop
 
 Debug.Print UpdateTable_sub_data(0)



------------------------------------------------------------------------------
Function findSubCommands(str As String)
  
 findSubCommands = Split(str, "+")

End Function

After doing this, I can only print the first data filed .
if I try, Debug.Print UpdateTable_sub _data(1)
The script out of range. Why there is not second record in the array??

[NeoPa]

I'm confused by your use of the terminology. Data in Access is stored within a field. Fields fit together into a record and records make up a table.

To access data within a field you would first need to ensure that it is text data. If not, it could possibly be converted to text using a function such as Format(). Accessing individual characters within such a string is fairly straightforward and done by using the Mid() function. A loop of some kind would enable all characters to be accessed in order.

Welcome to Bytes!

[ADezii]

The following code will extract every Character in a Field named [Location] for every Record in a Table named Training_Events . What you do with each Character is up to you.

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim intNumOfCharsInField As Integer
Dim intCharCtr As Integer
Dim strChar As String

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("SELECT [Location] FROM Training_Events", dbOpenForwardOnly)

With rst
  Do While Not .EOF
    If Not IsNull(![Location]) Then
      intNumOfCharsInField = Len(![Location])
        For intCharCtr = 1 To intNumOfCharsInField
          'Here is where each Character would be analyzed
          strChar = Mid$(![Location], intCharCtr, 1)
        Next
    Else
      'Whatever you wish to do with NULL Fields
    End If
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

[subedimite]

Thanks for that. The Mid fucntion seemed to be appropriate for me. If I want to use the split function ( for the recordset) to split the table field and store in array where each index is a record in that filed, what should I use as the delimeter, is that carriage return.


Sorry! I might have been saying something not directly. All wanted to do was sace each record of a filed in a string array and then access each caharecter as above so that I might have to replace some characters from other table.



Thanks heaps

[NeoPa]

Node elements in a recordset are field values. As such, you are starting with strings or numbers. I have great difficulty trying to work out what your question means as you are not using the terms in a meaningful way. I mentioned this in my first post. It is important to use the correct words when talking or writing, otherwise it can be very difficult to even guess what you're trying to say.

In short, I can see no situation here where Split() would be required, but then I may be missing something as I have very little idea what you're trying to say.

[ADezii]

If you wish to extract Field Values from a Recordset, and place them into an Array, then The GetRows() Method is what you are looking. I'm posting a Link to an Article that describes the use of this Method below. Like NePa, Iam confused by the nature of your request, and I see no possible use of the Split() Function in this context.
How to Use the GetRows() Method in DAO

[subedimite]

Yes that is right.

I do get bit impatience while while writing these requests. I will keep in mnd about this one and make a better habit.

Yep this what exactly what I wnat to do. It must be one of the very common thing to do in db programming.

Let my try this one. Cheers!

[subedimite]

your help has been pretty helpful. Thanks.

[NeoPa]

That's fine.

Let us know how you get on with it.

[subedimite]

It went pretty well Thanks. I am a pretty new VBA user but I am getting there:)