asked on

Exporting table to DBF and number of decimal spaces

I have this code (below) that works fine with one exception. In my table I have a field set as Double with 9 decimal spaces. When I export the table to DBF it only gives the field 5 decimal places. Anyway to force it to give 9?

Thanks!

Private Sub ExportWebSponsorsGeocodecmd_Click()
On Error GoTo Err_ExportWebSponsorsGeocodecmd_Click
DoCmd.SetWarnings True
DoCmd.TransferDatabase acExport, "dBase III", "\\Clustertor1\storage\SponsorDatabase\GeoCode\", acTable, _
"tbl_Combined_All_Sponsors", Format(Now(), "mmddyy") & "_GeoCode.dbf"
MsgBox "File Exported for Geocoding", vbOKOnly, "Finished"
End Sub

nico5038

you could try to use a format statement in your query like:
format(field,"0.00000000") as NineDecimals

Nic;o)

cmcgregor

ASKER

I dont think there is anywhere to put something like that in the DoCmd.TransferDatabase command.

nico5038

Just create a query with the fields including the FORMAT statement and use instead of:
acTable, _
"tbl_Combined_All_Sponsors"
acQuery, _
"qry_Combined_All_Sponsors"

Nic;o)

cmcgregor

ASKER

I thought that would work too but it doesnt. When I create the query and look at it in select mode it has the 9 decimal spaces. When I export it to dbf it changes it to a character field.

nico5038

Does your dbf table support the number of decimals ?

Nic;o)

cmcgregor

ASKER

The access table.? I have the number format set a double with 9 decimal but it always exports 5 decimals.
I export as dbaseIII, dbase V and dbase Iv and none of them work.

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmcgregor

ASKER

can i do that all through access though? I dont think I can. I am trying to automate the process, so would not want them to have to export to txt then open foxpro import to dbf etc...
I may be out of luck on this one.

stevbe

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: nico5038 {http:#9688054}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer