Formatting Fields and Fixed Width Exporting

[Panman01]

Hi, I can get around in Access but I'm no guru. I've been charged with helping create a conversion process to take data from our DBASE4 files, format them in a particular way, and exporting them as fixed width format (with tabs, go figure).



We know how to connect Access to our DBASE4 files and view the data but I don't know how to get Access to format the fields and make a fixed width export. So, the two questions are:

How can Access change the formatting of a field? Ex: I need a standard date field to become YYYYMMDD. Another field must be pre-pended with an extra 0. Another must become a different value in the export file (value == "Y" ? "X" : ""). A fake field with all X's or needs to be inserted.

How can Access export the data in fixed width format and know how long each field should be. Oh, and between each field a comma needs to be added (yes, this is pointless but the requirement).

Maybe the better question is, can Access do this? I have this feeling that we can just about automate the entire process. Right now we have to export the data from our DBASE4 files, combine them manually, import it into Excel (to do the formatting), and export it as fixed width.

[CraigDolphin]

The general approach would be to create a query that formats or creates the data as you wish, then export the query results to a tab delimited, or comma delimited text file. It's hard to tell which you need because these are typically either-or choices.

You may find this post useful in figuring out the export process
Tricks and Tips with Exporting tbl/qry to text - Access World Forums

Spoecific functions, that you should do some reading on, that can be used to make calculated/formatted fields in your query would likely include:
Format()
IIF()
You might find this link helpful in finding functions that meet your needs
Access Topics: Functions (By Category)

To create a fake column containing the value 'X' for all records, just type in the desired name of the field in the 'Field' row of the query builder, followed immediately by a colon and a space, then simply type "X" (including the apostrophes)

Similarly, for appending a "0" to all records in a field, just create a calculated field in a similar way using the concatenate operator '&'

ie
NewFieldName: "0" & [OldFieldName]

However, if the old field contain a numerical value that you wish to have a certain number of digits, then you would likely be better off to convert the correctly formatted number to a string.

ie:
NewFieldName: CStr(Format([OldFieldName],"00000"))
Would return a 5 digit string
1 -> 00001
15-> 00015
134-> 00134