Split fields into lines

[rajeevs]

Hi all

I have a query with two fields. When i use the export to text i want the fields
to show as two lines in the text file. but now it is showing as same line. Pls help

eg: my result is now as xxxx yyyyyyy
i need to get the result as
xxxx
yyyyyyy

Hope my question is clear. Pls help me
Thank you all

[ADezii]

Hi all

I have a query with two fields. When i use the export to text i want the fields
to show as two lines in the text file. but now it is showing as same line. Pls help

eg: my result is now as xxxx yyyyyyy
i need to get the result as
xxxx
yyyyyyy

Hope my question is clear. Pls help me
Thank you all

1. Remove the 2 Fields from the Query and replace them with a single Calculated Field named Split_Field (or anything else for that matter) as in:
   [CODE=sql]Split_Field: [Field1] & Chr$(13) & Chr$(10) & [Field2][/CODE]
2. Save the Query.
3. Now, Export the Query containing the single, Calculated Field.
4. The results should be as follows:
   [CODE=text]
   xxxx
   yyyyyyy[/CODE]
5. What the Calculated Field does is to concatenate a Carriage Return (Chr$(13))/LineFeed (Chr$(10)) combination which effective places the 2 Fields on separate lines. BTW, the Intrinsic Constant vbCrLf will not work.
6. Let me know how you make out.

[rajeevs]

Thank you for the quick response and solution. It works..... great.
Once again thank you so much

[ADezii]

Thank you for the quick response and solution. It works..... great.
Once again thank you so much

You are quite welcome.

[rajeevs]

HI dezyl

I have another prob with the same question. My actual result in the query shaould show the split fields as two different records. As per your advise it works for text file and in the text file it shows two lines. But my actual need is to split that in the query and show as different records.
my data in the table/query shows two fields like field1 xxxxx and field2 as yyyyy
i need the field1 as record1 and field2 as record2. Can you help me how to do this?
Thank yo for the help

[ADezii]

HI dezyl

I have another prob with the same question. My actual result in the query shaould show the split fields as two different records. As per your advise it works for text file and in the text file it shows two lines. But my actual need is to split that in the query and show as different records.
my data in the table/query shows two fields like field1 xxxxx and field2 as yyyyy
i need the field1 as record1 and field2 as record2. Can you help me how to do this?
Thank yo for the help

I'm not sure if this can be accomplished via SQL, but it can definately be done via VBA code. If you are interested in this approach, let me klnow and I'll see what I can do.

[rajeevs]

Hi ADezil

Thanks for the reply. I am interested to solve the problem in any manner.
Please help me. I can explain you about the scenario
I have a table with multiple fields. My query combine all the fileds and give the result as a single field. like that the query will show reocrds. I need to export that records as a text file. But the text file should have a line(normally six zeros like 000000)before each record,
eg: if my query result is with 4 records like below

wewerrrw wrerwrwrwrk lfjdkljfkdflkdf ldkjflkfjlkfj
jhkdjkfhjdhfjkd kdhfkjdhfkj jkdhfkjdhfjkdh kjdhfkjdhf
kjfhdjkfhjd llkfkljfd lkjsfklfjkl lkjflkjfkl ljfljk lkjflkdjf lkjf
jkhfjkfhjkdfhdj fhdjkfhdjfhdjkf hdjkfhdfhdfhdfj djjfhdjfhdjf

i need that to export as

000000
jhkdjkfhjdhfjkd kdhfkjdhfkj jkdhfkjdhfjkdh kjdhfkjdhf
000000
jhkdjkfhjdhfjkd kdhfkjdhfkj jkdhfkjdhfjkdh kjdhfkjdhf
000000
kjfhdjkfhjd llkfkljfd lkjsfklfjkl lkjflkjfkl ljfljk lkjflkdjf lkjf
000000
jkhfjkfhjkdfhdj fhdjkfhdjfhdjkf hdjkfhdfhdfhdfj djjfhdjfhdjf

Your first reply give me the solution like a single record in two lines.
But i request for a vb code which can insert a pre defined record before each record in the table.
Thank you so much for your kindness and the patience you are showing

[ADezii]

Hi ADezil

Thanks for the reply. I am interested to solve the problem in any manner.
Please help me. I can explain you about the scenario
I have a table with multiple fields. My query combine all the fileds and give the result as a single field. like that the query will show reocrds. I need to export that records as a text file. But the text file should have a line(normally six zeros like 000000)before each record,
eg: if my query result is with 4 records like below

wewerrrw wrerwrwrwrk lfjdkljfkdflkdf ldkjflkfjlkfj
jhkdjkfhjdhfjkd kdhfkjdhfkj jkdhfkjdhfjkdh kjdhfkjdhf
kjfhdjkfhjd llkfkljfd lkjsfklfjkl lkjflkjfkl ljfljk lkjflkdjf lkjf
jkhfjkfhjkdfhdj fhdjkfhdjfhdjkf hdjkfhdfhdfhdfj djjfhdjfhdjf

i need that to export as

000000
jhkdjkfhjdhfjkd kdhfkjdhfkj jkdhfkjdhfjkdh kjdhfkjdhf
000000
jhkdjkfhjdhfjkd kdhfkjdhfkj jkdhfkjdhfjkdh kjdhfkjdhf
000000
kjfhdjkfhjd llkfkljfd lkjsfklfjkl lkjflkjfkl ljfljk lkjflkdjf lkjf
000000
jkhfjkfhjkdfhdj fhdjkfhdjfhdjkf hdjkfhdfhdfhdfj djjfhdjfhdjf

Your first reply give me the solution like a single record in two lines.
But i request for a vb code which can insert a pre defined record before each record in the table.
Thank you so much for your kindness and the patience you are showing

Will have a solution for you later.

[ADezii]

1. I've made the assumption that your Table is named tblTest and that it consists of 2 Fields (TEXT) named [Field1] and [Field2].
2. Create a New Table named tblResults and a Field within it named [TheField] (TEXT 255).
3. Run the following code and/or download the attached Test Database to see what is actually happening.
4. The algorithm is generic in that regardless of the number of Fields in tblTest, each Field will always be written to a single Record in tblResults prefaced by "000000".
5. Nulls are ignored as evidenced by the missing values in [Field1] ("Five") and [Field2] ("Twelve").
6. Let me know how you make out, all relevant info is posted below, and the Test Database is attached.

[CODE=vb]
Dim MyDB As DAO.Database
Dim intNumOfFields As Integer
Dim intFieldCounter As Integer

Dim rst_1 As DAO.Recordset
Dim rst_2 As DAO.Recordset

Set MyDB = CurrentDb()

Set rst_1 = MyDB.OpenRecord set("tblTest", dbOpenForwardOn ly)
Set rst_2 = MyDB.OpenRecord set("tblResults ", dbOpenDynaset, dbAppendOnly)

intNumOfFields = rst_1.Fields.Co unt

'Delete any pre-existing Records in tblResults
CurrentDb.Execu te "Delete * From tblResults", dbFailOnError

Do While Not rst_1.EOF
For intFieldCounter = 0 To intNumOfFields - 1 'iterate Fields/Record
If Not IsNull(rst_1.Fi elds(intFieldCo unter)) Then 'ignore NULLS
rst_2.AddNew
rst_2.Fields(0) = "000000"
rst_2.Update
rst_2.AddNew
rst_2.Fields(0) = rst_1.Fields(in tFieldCounter)
rst_2.Update
End If
Next
rst_1.MoveNext 'advance to the next Record
Loop

rst_1.Close
Set rst_1 = Nothing
rst_2.Close
Set rst_2 = Nothing

'Let's see the results!
DoCmd.OpenTable "tblResults ", acViewNormal, acReadOnly
DoCmd.Maximize[/CODE]
tblTest
[CODE=text]
Field1 Field2
One Two
Three Four
Null Six
Seven Eight
Nine Ten
Eleven Null
Thirteen Fourteen
Fifteen Sixteen[/CODE]
tblResults after code execution
[CODE=text]
TheField
000000
One
000000
Two
000000
Three
000000
Four
000000
Six
000000
Seven
000000
Eight
000000
Nine
000000
Ten
000000
Eleven
000000
Thirteen
000000
Fourteen
000000
Fifteen
000000
Sixteen[/CODE]

[rajeevs]

Hi ADezii

I just tried that code. It works as i reuested. But i need to try once i go to my office whether the text file will work according to my requirement. Thank you so much for the quick reply and the help and support you are providing. I will let you know once i achieve the full result of my database and will contact you if i need further help. Thank you once again

[ADezii]

Hi ADezii

I just tried that code. It works as i reuested. But i need to try once i go to my office whether the text file will work according to my requirement. Thank you so much for the quick reply and the help and support you are providing. I will let you know once i achieve the full result of my database and will contact you if i need further help. Thank you once again

Exporting tblResults as a Text File as in:
[CODE=vb]DoCmd.TransferT ext acExportDelim, , "tblResults ", "C:\Results.txt ", False[/CODE]
yields[CODE=text]
"000000"
"One"
"000000"
"Two"
"000000"
"Three"
"000000"
"Four"
"000000"
"Six"
"000000"
"Seven"
"000000"
"Eight"
"000000"
"Nine"
"000000"
"Ten"
"000000"
"Eleven"
"000000"
"Thirteen"
"000000"
"Fourteen"
"000000"
"Fifteen"
"000000"
"Sixteen"[/CODE]

[rajeevs]

Hi AdDezii

Sorry for the delay in reply. I was so busy during the last few days. Your help was so great and it gives me result as i expected. Hope you will continue helping me whenever i stuck with something.

Thank u so much

Regards

[ADezii]

Hi AdDezii

Sorry for the delay in reply. I was so busy during the last few days. Your help was so great and it gives me result as i expected. Hope you will continue helping me whenever i stuck with something.

Thank u so much

Regards

You are quite welcome and we are always here if and when you need us.