Table data from other tables

[denileigh]

I am not sure how to word this or how to search the forums for an answer and am getting frusterated.

I need to create a table that pulls data from other tables based on a job number and a check box (true or false).

I have to export the data from the table to a text file and set a fixed with for the columns so that it will turn out like this:

1HCPT UBR RTMX002063TE 682782 CS000000010008518RR025008518RR04 000916500007000DY0Detailed Inspection------------------------------ E
1HCPT UBR RTMX002063TE 682782 CSA00000010004458GT009004458RR04 000705000000000DY0Jack Car A End------------------------------------ E
1HCPT UBR RTMX002063TE 682782 CSB00000010004458GT009004458RR04 000705000000000DY0Jack Car B End------------------------------------ E
1HCPT UBR RTMX002063TE 682782 CS000000020004400DF009004400RR04 000141000000000DY0Hook-up Top Rod----------------------------------- E
1HCPT UBR RTMX002063TE 682782 CS000000010004450FV009004450RR04 000047000000000DY0Lube Center Plate--------------------------------- E



I can't seem to do this in a query because it does not give me an export to text option.

Can anyone help me figure out a way to do this?

 

[denileigh]

Mine won't let me export a query to a text file. Am I looking in the wrong place?

 

[KeithG]

Are you right clicking on the query in the database window and selecting export?

 

[denileigh]

Well, we may be getting closer. If I right click on the query in the database window, it doesn't ask me which job number to do.

If I open the query and select all the records, the export wizard runs, however I get an error that says:

Export Text Wizard:
Too few parameters. Expected 1.

Is there any way around this?

 

[KeithG]

You can set your parameter to retrieve it value from a form. If you decide to do this make sure the form is open when you try to export.

 

[denileigh]

Huh? I'm green behind the ears here. Where do I set what parameter? The query is all alone and not tied to a form. It has approx. 35 fields that neext to export to a 500 character text string.

 

[WayneRyan]

d,

How about another approach?

Extremely simple example putting out 3 fields with lengths
10, 20 and 30 characters.

Dim rst As DAO.Recordset
Dim sql As String

Open "C:\OutputFile.txt" For Output As #1
sql = "Select A.Field1, A.Field2, B.Field3 " & _
      "From   Table1 As A Left Join Table2 As B On " & _
      "    A.JobNumber = B.JobNumber"

Set rst = CurrentDb.OpenRecordset(sql)
While Not rst.EOF And Not rst.BOF
   Print #1, Space(10 - Len(rst!Field1) & _
             Space(20 - Len(rst!Field2) & _
             Space(30 - Len(rst!Field3)
   rst.MoveNext
   Wend
Close #1

Wayne

 

[denileigh]

Wayne,

I can try to figure that one out. I assume I need to list all 30 something fields?

Di

 

[denileigh]

P.S. Also Wayne, where do I try that code? In the table or the query?

 

[WayneRyan]

d,

Just put the code on a Command Button.

You could make it a Public Function and reference it in a query, but I don't
think you'd want to apply it to every row. It already traverses the rows it
needs.

As to your question, you would have to reference all fields. If you wanted to
include ALL fields at a static number of characters you could use this:

Dim OutString As String
OutString = ""
For i = 0 To rst.Fields.Count - 1
   OutString = OutString & Space(30 - Len(rst.Fields(i)))
   Next i
Print #1, outString

Wayne