Format Field - While Download

[iwelcomesu]

Good Morning,
I am useing below code to download the comma delimited text files to access table.
Function Load_Attendance_Data()
Dim NextFile, ImportFile, FileCriteria, ctr As Variant
Dim DB_Path As Variant
' get the DB path
DB_Path = "C:\temp\"
FileCriteria = "C:\temp\" & "*.txt"
' create field with path and filename to import MAKE THIS READ ALL files that start with 'FILE FOR'
NextFile = Dir(FileCriteria)
' Check we have something to import
If NextFile = "" Then
MsgBox "No files to import", , "Error"
Exit Function
End If
ctr = 0
' Import each file that meets the criteria 'File for*.xls'
While NextFile <> ""
' count files imported
ctr = ctr + 1
' add the path to the returned filename
ImportFile = DB_Path & NextFile
' Import file into table
DoCmd.TransferText acImportDelim, , "RawData", ImportFile, False
' get another file if it exists
NextFile = Dir()
Wend
MsgBox ctr & " files imported", , "Attendance Import"
End Function



My requirement would be there is one field with numbers, and it should be converted as formatted filed as below.
All the files are downloading as Text fields.

Actual Filed Formatted filed
1 1.00%
0.25666 0.257%
0.222 0.222%
2.3 2.30%
4.5 4.50%
0 0

can you suggest which code is better to use get the required output.

Regards,
Hari

http://accessworld.accessworld.netdna-cdn.com/forums/images/misc/progress.gif

[hansup]

Consider importing the CSV data into an intermediate table rather than directly into RawData.

'first empty the intermediate table
CurrentDb.Execute "DELETE FROM CSV_Import;"
'then import CSV data
DoCmd.TransferText acImportDelim, , "CSV_Import", ImportFile, False

Next you can use an "append query" to copy the rows from CSV_Import into RawData. Design that query with the Format function to transform the field values as you wish. Here is an example, copied from the Immediate Window, using Format as I think you want.

? Format("0.25666","0.000\%")
0.257%

I don't know your table structure, so can only suggest something similar to this as your append query.

INSERT INTO RawData (column1)
SELECT Format(column1, "0.000\%")
FROM CSV_Import;