MEMO fields

[ConfusedNAccess]

In One Table I have- a type of definition table---
10=Title|StandardText 11=Active|InDefault|GracePeriod|DateDue|DateReceived|Received|SignoffNote
12=Active|InDefault|RequiredValue|ActualValue|SignoffNote 10,11,12 are text fields

In another table I have the following fields with data
11=|Y|10|20020211||N~Y|N|10|20020311||N~Y|N|10|20020411||N
12=Y|N|0.0000|0.0000|~Y|N|-0.1500|0.0588|~Y|N|-0.1500|-0.0222|~Y|N|-0.1500|-0.0278|~Y|N|-0.1500|0.0000|~Y|N|0.0000|0.0000|

the 11 and 12 are text and the results- is a memo field

how do i extract what is reflected in the memo field, to make sense?

 

[rjoubert]

http://www.tek-tips.com/faqs.cfm?fid=1823

 

[ConfusedNAccess]

where do i put this code?

 

[rjoubert]

Create a code module in your Access DB, then you can call it from within your Query.

 

[rjoubert]

Well, since this function returns an Array, you may need to create some additional code to loop through the records in your table and call this function for each record. I'm not sure what you want to do with the results...you could probably save them to another table.

 

[ConfusedNAccess]

Would you be able to guide me through this. I have no idea what i'm doing.

 

[rjoubert]

Ok, I put together some code that should help you.

First, you have your Function...this was the one that was in the link I gave to you previously...

Public Function Split(ByVal sString As String, _
                      sDelimiter As String, _
                      Optional iCompare As Long = vbBinaryCompare) As Variant
                    
    Dim sArray() As String
    Dim iArrayUpper As Integer
    Dim iPosition As Integer
    
    iArrayUpper = 0
    
    iPosition = InStr(1, sString, sDelimiter, iCompare)
    
    Do While iPosition > 0
        ReDim Preserve sArray(iArrayUpper)
        sArray(iArrayUpper) = Left$(sString, iPosition - 1)
        sString = Right$(sString, Len(sString) - iPosition)
        iPosition = InStr(1, sString, sDelimiter, iCompare)
        iArrayUpper = iArrayUpper + 1
    Loop
    
    ReDim Preserve sArray(iArrayUpper)
    
    sArray(iArrayUpper) = sString
    Split = sArray
    
End Function

Then you need a procedure to go through the records in your table, split your "|" delimited strings, and add records to a new table...

Public Sub ParseData()

    Dim objRS1 As Recordset
    Dim objRS2 As Recordset
    Dim strRecNo As String
    Dim sArray() As String
    
    Set objRS1 = CurrentDb.OpenRecordset("test")
    Set objRS2 = CurrentDb.OpenRecordset("newtable")
    
    objRS1.MoveFirst
    
    Do Until objRS1.EOF
        sArray = Split(objRS1.Fields("test"), "|")
        strRecNo = objRS1.Fields("recordNo")
        
        For i = 0 To UBound(sArray)
            objRS2.AddNew
            objRS2.Fields("recordNo") = strRecNo
            objRS2.Fields("Data") = sArray(i)
            objRS2.Update
        Next
        
        objRS1.MoveNext
    Loop
    
    objRS2.Close
    objRS1.Close
    Set objRS2 = Nothing
    Set objRS1 = Nothing
    
End Sub

In my example, I have a table called "test", with a "recordNo" field and a "test" field. The "test" field contains the "|" delimited string.
test
recordNo test
1 1|4|6|7|8
2 2|9|10|15|17
3 3|6|9|12|15

I then created a new table, called "newtable", with a "recordNo" field and a "Data" field. After running the procedure above (ParseData), there will be 5 records for each of the 3 records in my test table (15 records total).

newtable
recordNo Data
1 1
1 4
1 6
1 7
1 8
2 2
2 9
2 10
2 15
2 17
3 3
3 6
3 9
3 12
3 15

This is certainly less complex than your data, but I hope it helps.

Good Luck!

 

[ConfusedNAccess]

This is very helpful, but i'm a beginner. Where am I putting this info? In 2 separate modules? When I create or copy and paste this into a new module..

where do i go from there? what do i do with the module once it's been created?

Please bear with me.. i'm a self taught access user, and i haven't taught myself this part yet.. LOL.

I do REALLY appreciate your help!
Meryn

 

[rjoubert]

You can create one module and just copy both the function and the procedure into the new module. Or you could put them into two separate modules...it's up to you.

To run the procedure (ParseData) you could either put it in a macro and run it, or hit the Run button (looks like a green Play button on your VCR) on your toolbar (when you have the code module open).