Exporting records in a certain order into a text file

[albyh]

Can someone please help me I need to export some data from a table into a text file. The problem is it needs to be in the following format:

0[Field 1] [Field2]
[Field 3] 1
9[Field 1] 1

Field 2 is the date, i need the module to export all records with a certain date.

Any help would be GREATLY appreciated.

 

[scriverb]

You need to export a query of your data to format it in this manner. I am not sure if I am interpretting your post correctly but I am going to assume that the 0 and 9 are leading characters that you want cancatenated on the front of the other data.

0[Field 1] [Field2]
[Field 3] 1
9[Field 1] 1


Select "0" & A.[Field 1] & A.[Field 2] as NewField1, A.[Field 3] & "1" as NewField2, "9" & [Field 1] & "1" as NewField3
FROM <yourtablename> as A
ORDER BY (&quot;0&quot; & A.[Field 1] & A.[Field 2]);

If this is not what you need just post back with an explaination.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[albyh]

How do I then export the data into a text file?

Alban

 

[scriverb]

You can use the following statement type to export the recordset from a query:
Syntax
DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype], source, destination[, structureonly][, saveloginid]

Here is an example you can use:
DoCmd.TransferDatabase acExport, &quot;Microsft Access&quot;, &quot;. C:\<path to location of database >. .YourDatabaseName.mdb&quot;, acQuery, &quot;YourQueryName&quot;, &quot;c: <path pointing to destination location> . .ExportedTextFileName.txt&quot;

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[albyh]

Sorry about this but I'm realy new about this exactly how do I put this into a module to make it work. What do I have to declare.

Thanks

Alban

 

[scriverb]

You need use an existing form or create a new form. Now create a Command Button from the Tools toolbar. Right click the Command Button and select Properties. Click the Events tab at the top and in the On Click row select Event Procedure. Now click the build button just to the right with the 3dots . . . You are now deep inside the ACCESS VBA machine. This is where you put the VBA code as I described. After you have the syntax correct and save the form you can open the form and click the button to initiate the export of your data.

Now open up the ACCESS Help from the menu and search for the topic TransferDatabase method. This will give you an indepth definitaion of yhe parameters.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???