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:
   

   Split_Field: [Field1] & Chr$(13) & Chr$(10) & [Field2]

2. Save the Query.
3. Now, Export the Query containing the single, Calculated Field.
4. The results should be as follows:
   

   xxxx
   yyyyyyy

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
jkhfjkfhjkdfhdjfhdjkfhdjfhdjkfhdjkfhdfhdfhdfjdjjfh djfhdjf

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
jkhfjkfhjkdfhdjfhdjkfhdjfhdjkfhdjkfhdfhdfhdfjdjjfh djfhdjf

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
jkhfjkfhjkdfhdjfhdjkfhdjfhdjkfhdjkfhdfhdfhdfjdjjfh djfhdjf

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
jkhfjkfhjkdfhdjfhdjkfhdjfhdjkfhdjkfhdfhdfhdfjdjjfh djfhdjf

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.

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.OpenRecordset("tblTest", dbOpenForwardOnly)
Set rst_2 = MyDB.OpenRecordset("tblResults", dbOpenDynaset, dbAppendOnly)
 
intNumOfFields = rst_1.Fields.Count
 
'Delete any pre-existing Records in tblResults
CurrentDb.Execute "Delete * From tblResults", dbFailOnError
 
Do While Not rst_1.EOF
  For intFieldCounter = 0 To intNumOfFields - 1             'iterate Fields/Record
    If Not IsNull(rst_1.Fields(intFieldCounter)) Then       'ignore NULLS
      rst_2.AddNew
        rst_2.Fields(0) = "000000"
      rst_2.Update
      rst_2.AddNew
        rst_2.Fields(0) = rst_1.Fields(intFieldCounter)
      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

tblTest

Field1        Field2
One            Two
Three        Four
Null        Six
Seven        Eight
Nine        Ten
Eleven        Null
Thirteen    Fourteen
Fifteen        Sixteen

tblResults after code execution

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

[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:

DoCmd.TransferText acExportDelim, , "tblResults", "C:\Results.txt", False

yields

"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"

[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.