identifying and using delimited portions of a text field 1

[uscitizen]

hi -

I have a text field in a table; the table is called something like 'Updates' and the text field is called 'PK'. The table's used to help identify records in the database that were updated by a user. For example, one field in the 'Updates' table is the TableName' field which points to the table updated by the user. The 'PK' field identifies the record in the 'TableName' field that was updated. The 'PK' field typically has multiple components, delimited by semicolons (";&quot. Although there may be more than two components attached with any 'PK' entry, only the first two are of interest. The first points to the 'Patient Number' and the second points to the 'Cycle Number' of the record.

What I'd like to do is create two additional fields in the 'Updates' table which take as their value the value of the first and, when it is not null, the value of the second component of the 'PK' field. And you guessed, the pair would be called 'Patient Number' and 'Cycle Number' and take the first and second (when available) component of 'PK', respectively.

I have looked in the Access help documentation for the method but not had much luck, but I'm convinced it's doable.

[I plan to post this query in Access Other Topics as well]

 

[Norris68]

You could write some VBA code to loop through a recordset and use the Split function to divide your compound PK field into individual strings then, update the appropriate fields. Look up the split function in the help - it returns an array of strings. Use the UBound function to find out how many were returned (it's always a zero-based array)

e.g.

Dim S() As String 'Undimensioned array of strings
'Initialise & open recordset
While Not R.EOF
S() = Split(R("PK&quot.Value,";&quot
'Need a "R.Edit" here if DAO recordset
R("Patient Number&quot.Value = S(0)
If UBound(S()) > 0 Then
R("Cycle Number&quot.Value = S(1)
End If
R.Update
R.MoveNext
Wend
'blah blah blah

 

[uscitizen]

This looks useful. Thank you.

 

[uscitizen]

ps, could you decode:


While Not R.EOF
S() = Split(R("PK&quot.Value,";&quot
'Need a "R.Edit" here if DAO recordset
R("Patient Number&quot.Value = S(0)
If UBound(S()) > 0 Then
R("Cycle Number&quot.Value = S(1)
End If
R.Update
R.MoveNext
Wend


it's clear to me that the Split function is using the PK field and told that ";" delimits in that field. ditto that S(0) must refer to first member of a two member array, but the rest is a little more opaque.

 

[Norris68]

Assumes we have some code here to open up your recordset

'While not at the end of our recordset
While Not R.EOF
'Split the PK field into strings based upon ';' delimiter, saving them in S() array
S() = Split(R("PK&quot.Value,";&quot
'If using a DAO recordset as opposed to an ADO recordset
'we need to put it into edit mode before modifying data
'Need a "R.Edit" here if DAO recordset
'The first string is patient number, put it into that field
R("Patient Number&quot.Value = S(0)
'If we have more than one string from our Split() function
'i.e. if the Upper Bound of our string array > 0 (remember it's zero-based)
If UBound(S()) > 0 Then
'Put the next string into the Cycle Number field
R("Cycle Number&quot.Value = S(1)
End If
'Save the changes to the record
R.Update
'Get the next record
R.MoveNext
'Loop again if not at EOF
Wend

... then some more code to tidy up

 

[uscitizen]

Hey, Thanks

 

[uscitizen]

In my elation over the decode, I didn't ask about a coupla more minor details, i.e.

Once I add the two fields to the 'Updates' table I started out this discussion by describing, i.e. "Patient Number" and "Cycle Number", how do I launch this thing.

Your code tells me that we need to have the recordset open and defined as ADO vs DAO.

I'm talking about an 'ordinary' MS Access table amidst a slew of other 'ordinay' MS Access tables in an MS Access 2000 database. I suppose there's no good definition of 'ordinary' to be found in MS' documentation, but I don't think of the tables as being 'ADO' or 'DAO', which is probably a reflection of my newbie status to Access. Can you please let me in on where the code would go and what it would take to get it into '1st gear' and moving?

 

[Norris68]

OK, I assumed you have added extra fields called 'Patient Number' and 'Cycle Number' to your table definition to hold the values that we split out from your compound 'PK' field.

If you are using Access 2000 you will probably have ADO recordsets. The type you have depends on the references set within the VBA editor Tools -> References dialog. If you are a novice, declare a recordset (Dim r As ADODB.Recordset), place the cursor over the 'Recordset' keyword and hit the F1 key. Then read everything you can find in the help about recordsets. Basically they provide a mechanism for navigating through/updating the records held in a table using VBA code. The help provides a lot more detail than I could possibly give here.

The main ADO objects to look up are: Connection, Recordset and Command. Access has a built-in connection object that can be referenced within your VBA code as CodeProject.Connection - this saves you having to open another connection to the database you are working in.