VB to Clean up Long Ship To Address

[Starke]

I am using bar tender hooked up to a MS Access DB to print labels. I
have a problem with the Access table. The problem is that the "Ship
to" field is written like this:

Warehouse "adidas, Ltd." /142181, MOSKOVSKAYA OBLAST,/KLIMOVSK,
BEREGKOVSKYI/PROEZD 17-A 38290

I need it formated like this:

Warehouse "adidas, Ltd."
142181, MOSKOVSKAYA OBLAST,
KLIMOVSK, BEREGKOVSKYI/PROEZD 17-A 38290

My question is, how can I break this up. I can add columns to my DB
as

address 1
address 2
address 3
and so on, but I need to be able to read between the "/". Also,
bartender as a spot for VB, so I may be able to break it up inside the
app. But I still don't know how to read only between the "/".

[JimH]

A cheap and dirty way!

Export the address database to word doc.
Import it as a coma delimited file.

I am sure there are Access Gurus here who can offer you professional advice,
bypassing my primitive solution.

--
Jim Hagani
QA Manager
TFC Manufacturing
P: [phone]
F: [phone]
jh*****@tfcmfg. com
"Starke" <st********@hot mail.comwrote in message
news:11******** **************@ c35g2000hsg.goo glegroups.com.. .

>I am using bar tender hooked up to a MS Access DB to print labels. I
have a problem with the Access table. The problem is that the "Ship
to" field is written like this:

Warehouse "adidas, Ltd." /142181, MOSKOVSKAYA OBLAST,/KLIMOVSK,
BEREGKOVSKYI/PROEZD 17-A 38290

I need it formated like this:

Warehouse "adidas, Ltd."
142181, MOSKOVSKAYA OBLAST,
KLIMOVSK, BEREGKOVSKYI/PROEZD 17-A 38290

My question is, how can I break this up. I can add columns to my DB
as

address 1
address 2
address 3
and so on, but I need to be able to read between the "/". Also,
bartender as a spot for VB, so I may be able to break it up inside the
app. But I still don't know how to read only between the "/".

[Rich P]

You will have to do a little bit of programming - something like this:

Dim DB As DAO.Database, RS As DAO.Recordset, str1() As String, i As
Integer

Set DB = CurrentDB
Set RS = DB.OpenRecordSe t("yourTbl")
Do While Not RS.EOF
str1 = Split(RS!Addres s, "/")
For i = 0 To Ubound(str1)
Debug.Print(str 1(i)
Next
RS.MoveNext
Loop

Whenever you have delimeters, you can use the Split function to convert
the string containing the delimeters into an array of string. Then you
can get the desired format. In your report you would place code similar
to this in the Report Section of the Report Code Module:

Note: make the variables module level vars (declare at the top of the
module)

Private Sub Report_Open(Can cel As Integer)
Set DB = CurrentDB
Set RS = DB.OpenRecordSe t("yourTbl")
End Sub

then in the Detail section you could do something like this:

Private Sub Detail_Format(C ancel As Integer, FormatCount As Integer)
str1 = split(RS!Addres s, "/")
txt0 = str1(0)
txt1 = str1(1)
txt2 = str1(2)

RS.MoveNext
End Sub

Good Luck

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On 1 May 2007 11:32:00 -0700, Starke <st********@hot mail.comwrote:

select Replace(ShipTo, "/", Chr$(13) & Chr$(10)) from SomeTable

-Tom.

>I am using bar tender hooked up to a MS Access DB to print labels. I
have a problem with the Access table. The problem is that the "Ship
to" field is written like this:

Warehouse "adidas, Ltd." /142181, MOSKOVSKAYA OBLAST,/KLIMOVSK,
BEREGKOVSKYI/PROEZD 17-A 38290

I need it formated like this:

Warehouse "adidas, Ltd."
142181, MOSKOVSKAYA OBLAST,
KLIMOVSK, BEREGKOVSKYI/PROEZD 17-A 38290

My question is, how can I break this up. I can add columns to my DB
as

address 1
address 2
address 3
and so on, but I need to be able to read between the "/". Also,
bartender as a spot for VB, so I may be able to break it up inside the
app. But I still don't know how to read only between the "/".