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?


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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

cmcgregorAuthor Commented:
I dont think there is anywhere to put something like that in the DoCmd.TransferDatabase command.
Just create a query with the fields including the FORMAT statement and use instead of:
acTable, _
acQuery, _

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cmcgregorAuthor Commented:
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.
Does your dbf table support the number of decimals ?

cmcgregorAuthor Commented:
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.
No, the dbf table that needs to receive the data.
When that has a max of five, transferring 9 won't work.
Getting a text field with nine decimals could be used to export the file to a .csv file and import that into the .dbf.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmcgregorAuthor Commented:
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.
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.

EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.