Problem exporting numbers to text from Access

[chadman]

Not exactly a PHP question, but I thought someone here might have some advice.

I am exporting an Access table into text, which is then imported to SQL. My problem is that all exported number fields are given the #.## format, with two decimal places. THis is annoying with integers, but it is killing me with my FLOAT values, for which I need three digits past the decimal.

Question: How can I modify Access so that it exports numbers into a text file in the format I desire? I have read documentation about changing this in the Export wizard, but my Wizard gives no option for this, at least that I can find.

Can anyone help me with my Access problem? Thanks.

[bastien]

how are you importing? with PHP...handle it on the import when reading in the file

[Roger_Ramjet]

Access's default number storage is in double precision floating point, even for integers and long ints. The only decimal storage it will do is with the Currency data type (4 decimal places). This is always a problem when exporting data from access. Your floats can be affected by the no of decimal places specified for a column in the table design view, or the query design - this affects presentation not storage.

The solution that I use is to create a query to select the data, and use the access string function Cstr(number_col) to convert nos to strings in decimal format. I also wrote a function to convert dates to mysql format at the same time. It makes it all a lot easier when importing into mysql.

eg, access query from a webload app of mine

SELECT tblPayments.PaymentID, tblPayments.DebtID, tblPayments.PayType, mysqldate([tblPayments].[DateIn]) AS DateIn, CStr([tblPayments].[AmtPay]) AS AmtPay, Trim([tblCurrencyCodes].[Print]) AS Curr, tblPayments.User INTO tblLoadPays
FROM tblPayments INNER JOIN tblCurrencyCodes ON tblPayments.Currency = tblCurrencyCodes.Currency
WHERE (((tblPayments.WebLoad)=True) AND ((tblPayments.AmtPay)>0));

You can do the same with php, but then you have to process each row instead of being able to use load data infile or the like.