Exporting tables with fields in correct format

[ianbrown76]

Hi,

I received a text file which had an income field in an incorrect format [left justified and missing some zeroes].

I imported the file to a table, made the relevant changes so it is now right justified and has leading zeroes.

How do I export the table so it is in the correct format? At the moment my income field on the exported file is left justified missing the leading zeroes.

I have tried copying the income field into a field with a data type of text but this does the same.

If anyone can help I'd most appreciate it.

Ian

 

[randysmid]

Hi,
Create a query that you can use to export the data. But, on the fields that have the incorrect format, you can create a new field using data from the old field, plus the format function to return the desired format. Here is how it might look (presuming the name of the field in the table is called fldIncome):
IncomeFormatted: Format(([fldIncome]),"000,000,000.00&quot

Note, the "0" acts as a placeholder, and will display zero even if there is no value. You would use a # (pound) sign if you wish to display nothing. Of course, you can remove the ".00" if you don't want any pennies showing. HTH,
Randy Smith
California Teachers Association

 

[ianbrown76]

Many thanks randysmid, that solved the problem.