Preparing data for exporting

[David]

A friend of mine has to export some data from his current access
database into a CSV file to be imported into an accounting package.

There are a few things that need to be done most of which I am quie
capable of doing. For expample writing the query to grab all the
relevant data is fine.
Building the macro to export the data via transfertext is also fine.

The problem I have lies in his product description field. In his
database it is a simple text field set to 255 characters.
The program which it is going to go to can only accept 30 characters
per line for the description field.
What I have to do is put a CrLf after every 30 characters in the
product description field, for every record before it is exported.

I am quessing here that the solution will be a VBA one. I understand
the basics of VBA however you might need to take it slowly with me.

I really hope someone can help me with this problem.

[Nigel tombs]

Hmmm...

I assume that the target table has, say 4 or 5 fields (Desc1,
Desc2,Desc3,Desc4,Desc5) to store the descrition each is 30 chars.

You could avoid VBA using a query, but you should get your hands dirty.
However, here is a possible non vba way.....
You could create a query that uses the MID( ) function to strip out each
block of 30 chars.

SELECT
MID(Description,1,30) AS Desc1,
MID(Description,31,30) AS Desc2,
MID(Description,62,30) AS Desc3,
MID(Description,93,30) AS Desc4,
MID(Description,124,30) AS Desc5,
FROM
tblProducts

"David" <oz****@bigpond.net.au> wrote in message
news:a4**************************@posting.google.c om...

A friend of mine has to export some data from his current access
database into a CSV file to be imported into an accounting package.

There are a few things that need to be done most of which I am quie
capable of doing. For expample writing the query to grab all the
relevant data is fine.
Building the macro to export the data via transfertext is also fine.

The problem I have lies in his product description field. In his
database it is a simple text field set to 255 characters.
The program which it is going to go to can only accept 30 characters
per line for the description field.
What I have to do is put a CrLf after every 30 characters in the
product description field, for every record before it is exported.

I am quessing here that the solution will be a VBA one. I understand
the basics of VBA however you might need to take it slowly with me.

I really hope someone can help me with this problem.

[David]

Thanks for the suggestion Nigel unfortunatly the target
table/application will have just a single field.

The target table(not in access) can handle multiline text fields
provided each line is marked by a CrLF.

So what I have is in my mates DB a whole heap of products some are
under 30 chars others are well over 180. I have to insert a CrLF into
each product description field at the 30,60, 90 etc marks.
WHen the target application imports this it will place the text after
the CrLf on the next line. Without the CrLf it will truncate the field
which is not what he wants.
"Nigel tombs" <ic*****@tiscali.co.uk> wrote in message news:<40********@mk-nntp-2.news.uk.tiscali.com>...

Hmmm...

I assume that the target table has, say 4 or 5 fields (Desc1,
Desc2,Desc3,Desc4,Desc5) to store the descrition each is 30 chars.

You could avoid VBA using a query, but you should get your hands dirty.
However, here is a possible non vba way.....
You could create a query that uses the MID( ) function to strip out each
block of 30 chars.

SELECT
MID(Description,1,30) AS Desc1,
MID(Description,31,30) AS Desc2,
MID(Description,62,30) AS Desc3,
MID(Description,93,30) AS Desc4,
MID(Description,124,30) AS Desc5,
FROM
tblProducts

[Pieter Linden]

oz****@bigpond.net.au (David) wrote in message news:<a4**************************@posting.google. com>...

Thanks for the suggestion Nigel unfortunatly the target
table/application will have just a single field.

The target table(not in access) can handle multiline text fields
provided each line is marked by a CrLF.

So what I have is in my mates DB a whole heap of products some are
under 30 chars others are well over 180. I have to insert a CrLF into
each product description field at the 30,60, 90 etc marks.
WHen the target application imports this it will place the text after
the CrLf on the next line. Without the CrLf it will truncate the field
which is not what he wants.

Only way I can think of doing it if you can't use a query is to use VB
loop through the table one record at a time and do something like what
Nigel suggested.

Something like...
-create a holder variable for your new string
Do until you've read all chars
-read the nth 30 characters off the field's contents
-write that to your variable and append a CrLf
Loop

when you get to the end of the string, write it back to the field it
came from.

[rkc]

David wrote:

Thanks for the suggestion Nigel unfortunatly the target
table/application will have just a single field.

The target table(not in access) can handle multiline text fields
provided each line is marked by a CrLF.

So what I have is in my mates DB a whole heap of products some are
under 30 chars others are well over 180. I have to insert a CrLF into
each product description field at the 30,60, 90 etc marks.
WHen the target application imports this it will place the text after
the CrLf on the next line. Without the CrLf it will truncate the field
which is not what he wants.

You could use a vba function in an update query.
Something like

UPDATE TableName
SET FieldWithBreaks = AddBreak(FieldWithoutBreaks,30);

<AddBreak Function>
Function AddBreak(s As String, break As Integer) As String
Dim i As Integer
Dim lastSpace As Integer
Dim c As String
Dim workStr As String
Dim result As String

workStr = s

Do While Len(workStr) > break
lastSpace = 0
For i = 1 To Len(workStr)
c = Mid$(workStr, i, 1)
If c = " " Then lastSpace = i
If i = break Then
result = result & Left$(workStr, lastSpace - 1) & vbCrLf
workStr = Mid$(workStr, lastSpace + 1)
Exit For
End If
Next
Loop

AddBreak = result & workStr

End Function
</AddBReak Function>