how can i split a section of text into multiple fields 2

[overdraft015]

(sorry the subject wasnt descriptive enough)

i have a field which can be up to 50chars long. it is a description of products. i build a table to show the product and description (in seperate fields) but i need to be able to split the description into multiple fields because the char size is to big for what im doing.

e.g.

Product Desc1(10chars) Desc2(10chars) Desc3(10chars)

1234 Super Size Cup 100 c l (white)


i can do this using the Mid$() function but i dont want to split a word into two fields. how can intelligently make it so that it either puts the word that would b split into the next field to ensure the full word stays whole?

hope i made it clear enough and hope someone can held. i was thinking maybe ill have to loop or something but cant get my head around it.

 

[TheAceMan1]

How are ya overdraft015 . . .

Can't you accomplish the same thing by just [blue]stretching the width of the textbox?[/blue] . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[overdraft015]

i am using a special barcode label printer (with its own software) i can lookup the data from the access db but strangley enough the software has no "wrap text" funuction so i have to split the description into multiple rows (because of the size of the label im printing)

 

[Remou]

How about:

Dim strText, astrText

strText = "Super Size Cup 100 c 1 (white) Super Size Cup 100 c 1 (white) Super Size Cup 100 c 1 (white)"

i = 45
Do Until i > Len(strText)
    If Mid(strText, i, 1) = " " Then
        Mid(strText, i, 1) = "|"
        i = i + 50
    Else
        i = i + 1
    End If
Loop

astrText = Split(strText, "|")

For i = 0 To UBound(astrText)
    Debug.Print astrText(i); Len(astrText(i))
Next

 

[overdraft015]

thanks Remou,

could i be a pain and ask you to 'comment it so i know what this does. so i can debug / change as necesary.

Thank you. i will also try this now.

 

[JBinQLD]

G'day mate,

split it into simple parts. Pseudo code below

set rowx=""

Take in your string,

break on space

rowx=first part

loop

break on next space

check length of rowx+PosOfNextSpace

if length<limit, add next word to rowx

else

rowx=rowx+1=Foundword

until end of string

This is massively over simplified but hopefuly you can get gist

 

[Remou]

I very rarely do this here. But ok.

'Somewhere to put stuff
Dim strText As String
Dim astrText As Variant
Dim i as Integer

'Sample 'in' string
strText = "Super Size Cup 100 c 1 (white) Super Size Cup 100 c 1 (white) Super Size Cup 100 c 1 (white)"

'Pick a break point 
i = 45
'Continue looking for breaks for the length of the string
Do Until i > Len(strText)
    'If there is an appropriate space ...
    If Mid(strText, i, 1) = " " Then
       '... replace it with a suitable delimiter ...
        Mid(strText, i, 1) = "|"
       'and jump to the next block to search
        i = i + 50
    'Otherwise ...
    Else
        ' ... keep looking.
        i = i + 1
    End If
Loop

'We now have a string with | delimiters, so Split
'to an array ...
astrText = Split(strText, "|")

'... that we can have fun with ...
For i = 0 To UBound(astrText)
    '... eg. print to immediate window.
    Debug.Print astrText(i); Len(astrText(i))
Next

 

[overdraft015]

thank you. i think i have managed to tweak it to giv eme what i want.

Thank you again for all your help

 

[overdraft015]

ok i have used your code and all works well (well for the debugging part) i now need to be able to update a specific table and specific field with the results

the bit i think i need help on is - DoCmd.RunSQL "" this is where i want to update the field descfield that i defined above in the table "New Prod" where Prod = rs.field(1)


here is my code:

---------------------------------

Private Sub Command0_Click()

Dim strText As String
Dim astrText As Variant
Dim i As Integer
Dim strQueryName As String
Dim db As Database
Dim rs As Recordset
Dim descfield As String

strQueryName = "SELECT Trim([cmprod.cmp_desc]) AS [Desc], cmprod.cmp_product AS Prod FROM cmprod" ' this gets the list of products to change description for

Set db = CurrentDb()
Set rs = db.OpenRecordset(strQueryName)

Do Until rs.EOF

'Sample 'in' string
strText = rs.Fields(0)

'Pick a break point
i = 20
'Continue looking for breaks for the length of the string
Do Until i > Len(strText)
'If there is an appropriate space ...
If Mid(strText, i, 1) = " " Then
'... replace it with a suitable delimiter ...
Mid(strText, i, 1) = "|"
'and jump to the next block to search
i = i + 20
'Otherwise ...
Else
' ... keep looking.
i = i + 1
End If
Loop

'We now have a string with | delimiters, so Split
'to an array ...
astrText = Split(strText, "|")

flddesc = 1

For i = 0 To UBound(astrText)

'... eg. print to immediate window.
descfield = "Desc" & flddesc

DoCmd.RunSQL "" this is where i want to update the field descfield that i defined above in the table "New Prod" where Prod = rs.field(1)

flddesc = flddesc + 1
Next
rs.MoveNext
Loop

rs.Close
db.Close

End Sub

 

[Remou]

I am not quite sure I understand, but I suspect:

If UBound(astrText)>2 Then
   MsgBox "This won't fit, too many parts."
End If

strSQL="SELECT ta, da FROM TheOtherTable " _
& "WHERE ID=" & rs!ID
 
rs2=db.OpenRecordset(strSQL)
rs2.Edit
rs2!Desc1=astrText(0)
rs2!Desc2=astrText(1)
rs2!Desc3=astrText(2)
rs2.Update

 

[overdraft015]

tried the code but not sure i may be doing it right or maybe not what i need.

what im doing is from the code u original supplied to get the delimited data is update a table which has the following fields in it

product desc1 desc2 desc3

depending on which part of the delimited data goes into which field so 1st part in desc1 second part in desc2 3rd in desc3 (assuming it has this many parts if not it will just go to the next record)

does this make sense?

Michael.

 

[Remou]

The code I posted should do what you describe, assuming the table to be updated already has records corresponding to numeric ids in the original table. Please post the last bit of code as modified for your application. Please also say whether the new table already has records or not and whether the key field is numeric or text.

 

[overdraft015]

the ID im matching up in the table is a text field. whens its a text field i can only get as far as "rs2 = db.OpenRecordset(strsql)" then i get a datatype mismatch. so i changed to a number and now it fails on
rs2.Desc1 = astrText(0) (error: Object doesn't support this property or method (Error 438))

btw i need that field i changed to a number to be a text field. i believe i can define the field type in the sql but im not sure on this.

this is the code:-

Option Compare Database

Private Sub Command0_Click()

Dim strText As String
Dim astrText As Variant
Dim i As Integer
Dim strQueryName As String
Dim db As Database
Dim rs As Recordset
Dim descfield As String

strQueryName = "SELECT Trim([cmprod.cmp_desc]) AS [Desc], Trim([cmprod.cmp_product]) AS Prod FROM cmprod" ' this gets the list of products to change description for

Set db = CurrentDb()
Set rs = db.OpenRecordset(strQueryName)

Do Until rs.EOF

'Sample 'in' string
strText = rs.Fields(0)

'Pick a break point
i = 20
'Continue looking for breaks for the length of the string
Do Until i > Len(strText)
'If there is an appropriate space ...
If Mid(strText, i, 1) = " " Then
'... replace it with a suitable delimiter ...
Mid(strText, i, 1) = "|"
'and jump to the next block to search
i = i + 20
'Otherwise ...
Else
' ... keep looking.
i = i + 1
End If
Loop

'We now have a string with | delimiters, so Split
'to an array ...
astrText = Split(strText, "|")

For i = 0 To UBound(astrText)

If UBound(astrText) > 2 Then
MsgBox "This won't fit, too many parts."
End If

strsql = "SELECT Product, desc1, desc2, desc3 FROM [New Prod] " _
& "WHERE product=" & rs.Fields(1)

rs2 = db.OpenRecordset(strsql)
rs.Edit
rs2.Desc1 = astrText(0)
rs2!Desc2 = astrText(1)
rs2!Desc3 = astrText(2)
rs2.Update

Next
rs.MoveNext
Loop

rs.Close
db.Close

End Sub

 

[overdraft015]

also yes i have put some records in the table that im changing (this is the table i had to change the field to a number from text)

 

[Remou]

You need delimiters for text fields:

[tt]strsql = "SELECT Product, desc1, desc2, desc3 FROM [New Prod] " _
& "WHERE product='" & rs.Fields(1) & "'"[/tt]

 

[overdraft015]

sorry i made a mistake in the code at line rs.Edit should read rs2.Edit this is where it now fails with the error

Object doesn't support this property or method (Error 438)

 

[PHV]

Replace this:
rs2.Desc1 = astrText(0)
with this:
rs2[!]![/!]Desc1 = astrText(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[overdraft015]

this is the update of the update section code (for clarification)

rs2 = db.OpenRecordset(strsql)
rs2.Edit '>>>>>>>>>this is where it fails<<<<<<<<<<<<
rs2!Desc1 = astrText(0)
rs2!Desc2 = astrText(1)
rs2!Desc3 = astrText(2)
rs2.Update

 

[PHV]

[!]Set [/!]rs2 = db.OpenRecordset(strsql)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[overdraft015]

thank you all for your help. se ebelow for the final code that works (i will be changing this slightly for production)

thank you all again


---------------

Option Compare Database

Private Sub Command0_Click()
On Error Resume Next ' this is a temporary solution because if there is less than 3 splits then it will fail on the update - i will remove this and check the number of splits before i update and possibly use a GOTO statement

Dim strText As String
Dim astrText As Variant
Dim i As Integer
Dim strQueryName As String
Dim db As Database
Dim rs As Recordset

strQueryName = "SELECT Trim([cmprod.cmp_desc]) AS [Desc], Trim([cmprod.cmp_product]) AS Prod FROM cmprod" ' this gets the list of products to change description for

Set db = CurrentDb()
Set rs = db.OpenRecordset(strQueryName)

Do Until rs.EOF

strText = rs.Fields(0)

'break point
i = 20
'look for breaks for the length of the string
Do Until i > Len(strText)
'If there is an appropriate space ...
If Mid(strText, i, 1) = " " Then
'... replace it with a suitable delimiter ...
Mid(strText, i, 1) = "|"
'and jump to the next block to search
i = i + 20
'Otherwise ...
Else
' ... keep looking.
i = i + 1
End If
Loop

'We now have a string with | delimiters, so Split to an array ...
astrText = Split(strText, "|")

For i = 0 To UBound(astrText)

If UBound(astrText) > 2 Then
MsgBox "This won't fit, too many parts."
End If

strsql = "SELECT Product, desc1, desc2, desc3 FROM [New Prod] " _
& "WHERE product='" & rs.Fields(1) & "'"

Set rs2 = db.OpenRecordset(strsql)
rs2.Edit
rs2!Desc1 = astrText(0)
rs2!Desc2 = astrText(1)
rs2!Desc3 = astrText(2)
rs2.Update

Next
rs.MoveNext
Loop

rs.Close
db.Close

End Sub