help splitting after x number of chars

[medic61]

I hope someone can help me with this. I'm printing a catalog of products. I need to export as xml so it can be imported. I have a query in access that is creating 3 fields, prod name, a combo of desciption, weight and size and a price field. I need to count characters in prod name and my combo field and split into another line after "x" number of characters. Is this possible?

Thanks!

Shane

 

[MajP]

SELECT 
 tblOne.fieldToSplit, 
 Left([fieldToSplit],3) AS LeftThree, 
 Mid([FieldToSplit],4) AS RightPastThree
FROM 
 tblOne;

fieldToSplit LeftThree	RightPastThree
abcXXXX      abc        XXXX
cdeY!@#$     cde        Y!@#$
efg12345     efg        12345

 

[Andrzejek]

Sure it is possible...

Show us what data you have to start with, data that you hope to get after the code is run, and the piece of code where you were trying to achive it.

Have fun.

---- Andy

 

[medic61]

Field 1 is just "Name"
Field 2 is combo of "Desc,Weight,Size"
Field 3 is just "Price"

Sample:
Marine Shirt High quality screenprinted with full back and front left chest 1.5oz Large $15.95

I want it to split after 40 characters and spaces to end up with:

Marine Shirt High quality screenprinted with
full back and front left chest 1.5oz Large $15.95

Not sure about the code, but I think this is what you want from the SQL View?:
SELECT Main.Name, Trim([Desc]& " "&[Weight]&" "&[Size]) AS Expr1, Main.Price
FROM Main
ORDER BY Main.Sort, Main.Lines;

Thanks again!

 

[strongm]

Line splitting is one of those things that is actually more complex than it sounds. You might like to review some of the comments (and maybe the example code) in this thread: thread222-871904

 

[medic61]

I know it's complex, that's why I need help. I have reviewed that and I'm no where closer. It's all greek.

 

[Andrzejek]

I want it to split after 40 characters and spaces

and you give this as an example:
[tt][blue]
Marine Shirt High quality screenprinted with[/blue][/tt]

That's 48 characters

If you are talking about just this field:
[tt][blue]
High quality screenprinted with[/blue][/tt]

That's 31 characters. You can still fit the word "full" in your 40 characters here.

So, what do you want to split?

I ask because I did something like that and would like to get your rules before comming with any code to do it.

Have fun.

---- Andy

 

[Andrzejek]

OK, here is my take.
If you only want to split it once (your field will not be longer than 80 characters):

Option Explicit

Private Sub Command1_Click()

Debug.Print SplitAt40("High quality screenprinted with full back and front left chest 1.5oz Large")

End Sub

Private Function SplitAt40(str As String) As String
Dim i As Integer
Dim strOut As String

For i = 40 To 1 Step -1
    If Mid(str, i, 1) = " " Then
        strOut = Left(str, i - 1)
        strOut = strOut & vbNewLine & Mid(str, i + 1)
        Exit For
    End If
Next i

SplitAt40 = strOut

End Function

As outcome you'll get:
[blue]
[tt]High quality screenprinted with full
back and front left chest 1.5oz Large[/tt][/blue]


Have fun.

---- Andy

 

[medic61]

I would prefer it to count both the Name and Expr1 when it calclulates if possible.

 

[Andrzejek]

That's fine, you can pass whatever string you want into the [tt]SplitAt40[/tt] function and you get back another 2 line string back.

You can do:[tt]
Debug.Print SplitAt40("Marine Shirt High quality screenprinted with full back and front left chest 1.5oz Large")[/tt]

But what about if you want to split it more than just one time?
Will you ever want to have 3 lines (4 lines?) of up to 40 characters?

Have fun.

---- Andy

 

[medic61]

Only worried about 1 line splitting, should never need split more than that. So what exacatly is the code I need to place so that it checks every line in my query when I run it and where do I place it at?

 

[Andrzejek]

You can split it just before you display it for the user.
What is your code right now *without* the split at 40 characters?

If you have something in your rs like that:

SELECT Main.Name, Trim([Desc] & " " & [Weight] & " " & [Size]) AS Expr1, Main.Price
FROM Main
ORDER BY Main.Sort, Main.Lines

And you show your data in a label that can display multi line:

Label1.Caption = rs!Expr1.Value

you can do:

Label1.Caption = SplitAt40(rs!Expr1.Value)

Have fun.

---- Andy

 

[medic61]

Okay, now I"m totally lost! Don't know if this helps or not, but I need the code in a query so I can export the query as xml.

 

[Andrzejek]

To be honest, I don't know how to do it in Access query.

What you can try is: in your Main table, add 2 new fields (Descr1 and Descr2) and run a little 'one time shot' program in VBA where you take your one long line of text and split it into 2, moving first into Descr1 and second into Descr2. This way you will have in your DB what you need tor your xml.

It is by far not the perfect solution (I don't like to have the same data in 2 places in my DB), but that's just 'work around' to have it working for now. I am sure there are a lot smart people out there who may have better ways of doing it.

Have fun.

---- Andy