Export to Text File

[BDW1969]

I have records that I need to break into multiple lines.

For example, the record could be - POL 112 KMJN 9898 X INS 874 CND X VEH 874 CND 9876 NHGB X

I need it to look like the following, coming out as a comma delimited file

"POL","112","KMJN","9898","X"
"INS","874","CND","X"
"VEH","874","CND","9876","NHGB","X

 

[PHV]

A starting point (ac2k or above):
strRecord = "POL 112 KMJN 9898 X INS 874 CND X VEH 874 CND 9876 NHGB X"
MsgBox Replace(Replace("""" & strRecord & """", " ", ""","""), "X"",", "X""" & vbCrLf)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lynchg]

You could split them up using a union query but you need to have the same number of fields in each of the selects in your union. I assume that you want to have the 3 rows from the first row of your table in succession in your comma delimited file.

In order to do this you would first need to add one field to your existing table, make it an autonumber field so you will have the values 1,2,3,etc. Then you will need to create a virtual field in your union to use in your order by clause. I am pretty sure something like this will work:

Select fld1, fld2, fld3, fld4, fld5, '', yourAutoNumber, 1 as "Ordering"
Union
Select fld6, fld7, fld8, fld9, '', '', yourAutoNumber, 2
Union
Select fld10, fld11, fld12, fld13, fld14, fld15, yourAutoNumber, 3
Order By yourAutoNumber, Ordering

 

[lynchg]

I wasn't assuming that the "X" value always indicatedthat you drop to a new line. If that is true then PHV's solution looks like it will work.

One thing to keep in mind, you could have done a better job explaining the rules around what you want to do, I found myself guessing at a solution.