Export Access table to dbase causes 5 decimal places for number fields 1

[emmi59]

Exporting tables from Access to dbase files causes 5 decimal places for all number fields. This is unwanted since the other application which imports the dbase files expects number fields with e.g. 0 or 2 decimal places.

Does anybody know how to tell Access the number of decimal places to use for export to dbase?

I have tried the following to no avail:

- define number fields in access tables as "fixed decimal numbers" (or so, in German: "Festkommazahl")
- define the exact number of decimal places for each number field (0 or 2)
- use all available dbase Versions when exporting (dbase III, IV and 5.0)

The code is like this:

DoCmd.TransferDatabase acExport, "dBase 5.0", Pfad, acTable, "TBL_Export", "export.dbf", False

Please help.

 

[PaulBricker]

Just some random thoughts. If you define the field as currency, then you should only get 2 decimal places. The caveat is you get placeholders for the thousands digit, and you get the $ sign. You might be able to strip those out in dbase. You could bring the data into a query and use the round function set to two places and then export the query instead of the table. Or you might have better luck defining the field as Fixed in a query using the properties box for the field, and again, export the query.
Just some thoughts.

Paul

 

[emmi59]

>Just some random thoughts.
Thank you for thinking ;-)

>If you define the field as currency, then you should
>only get 2 decimal places.
>The caveat is you get placeholders for the thousands
>digit, and you get the $ sign. You might be able
>to strip those out in dbase.
The target application is not dbase itself but a program we cannot modify/extend to this functionality.

>You could bring the data into a query and use the
>round function set to two places and then export
>the query instead of the table.
Tried this already. No luck.

>Or you might have better luck defining the field as
>Fixed in a query using the properties box for the field,
>and again, export the query.
Tried this already. No luck.

>Just some thoughts.
Thank you.

Any more ideas? Am searching on this problem for two days now (netto) and using 3 different Access forums from all over the world. Have also searched newsgroups on this topic.
No more than 4 answers (1 here, 3 in forum #2, 0 in forum #3) up to now...

:-(

 

[dhulbert]

Emmi.

If you can format the data before you export it change the numberic fields to text fields and it exports fine with the correct No of dec places.

 

[emmi59]

When I convert the fields to text (using a query) and then export the result of this query into a dbase file, every text field has a length of 254 characters and no decimal places at all. Also it is of type "C" (character) which is incompatible with the application which imports the dbase files.

Sorry, but this solution is not appropriate...

Thank you anyway.

 

[SilentAiche]

emmi59,

The target application is not dbase itself but a program we cannot modify/extend to this functionality.

Does this mean you cannot reformat the number field after the export? It seems like all the suggestions so far have focused on pre-export solutions (I know the idea of post-export reformatting is obvious, but sometimes those are the ones that get by us!).

Anyway, my feeble contribution would be this: try exporting to an intermediary, such as Excel, and then to dbase. There may some formatting options in the intermiary that give you the desired results in the end.

Good luck - it sounds frustrating.

Tim

 

[emmi59]

Hi, Tim

if it were not the dbase format but Excel, CSV, Word or whatever I would not hesitate to manipulate the data after export using VBA or whatever, but manipulating dBase files is somewhat tricky and might make them unreadable for the following application.
And since the export is part of a solution where the user uses the database with a convenient surface not letting him know of tables, queries and export options, it must work smooth and without irritating call of Excel or so to convert data.
So I have to find a solution which is invisible and independent from other software (the companies where this solution is meant to be used don´t even have Excel at all places, if at all...)

 

[SilentAiche]

emmi59,

Sorry, I misunderstood the problem - I thought it was a one-time conversion, and so could be as "clunky" and multi-step as necessary to get the job done. I'm afraid I don't have a solution for you- the problem is bigger than I realized.

Good luck!
Tim

 

[PaulBricker]

How are these values originally generated? Do they actually have 5 decimal places when they are placed in the Access Table or is the export process adding some phantom digits?
Does the new location have any functionality at all that you know of? Can we multiply the value by 100 and export as an Integer and then divide by 100 on the other side to return to the proper value?

Still thinking.

Paul

 

[emmi59]

>How are these values originally generated?

I have tables with fields defined as LongInteger resp. Double. They are set up to have fixed decimal places (0 resp. 2). The data is added to these tables programmatically by means of execution of stored insert queries basing on data from other tables, which in turn were created resp. filled through other queries and VBA script code and so on...

>Do they actually have 5 decimal places when they are placed in the Access Table or is the export process adding some phantom digits?

The 5 decimal places appear only trough the export and inside the dbase files.

>Does the new location have any functionality at all that you know of?

Yes ;-)

>Can we multiply the value by 100 and export as an Integer and then divide by 100 on the other side to return to the proper value?

No. Since this is an application which is independent from my solution and is also used in different environments where properly formatted dbase files are given, it could not be modified.

 

[PaulBricker]

Can you get rid of dbase as the middleware? Can you export to Excel to format the fields and export from Excel to Dbase from there? What does resp. mean?


Paul

 

[dhulbert]

How does the data feed into the application at the moment? I'm presumiong there is an exisitng fee from somewhere and you're replacing it with your access database.

 

[emmi59]

@PaulBricker:

Of course I could export the tables to somethinge else but dbase but this would require some more step of conversation for the user to finally generate the needed dbase files

"resp." is an abbreviation for "respectively"

@dhulbert:

The target application is a Lotus Notes application which imports data through an existing interface from dbase files. In the original environment these dbase files were generated from a 3rd party banking software which allows only for the export of dbase files (it is an older software...). In this environment no changes can be made.
Now we plan to use the LN application (without changing, to keep only one version) in other environments too, where the original banking software is not in use and is not applicable (different host systems) and then my Access application should play the role of the banking software and generate the dbase files.

 

[emmi59]

I finally found it (with the help of someone else) that to get rid of the 5 decimal places you have to define your fields in the table to be exported as DECIMAL. Then you can define the number of digits and decimal places for each field and when exporting to dbase (5.0) the fields in the .dbf file are like expected. Took me a while, but better late than never...

 

[PaulBricker]

Thank you for that update. Just to clarify, in the table, you would define the field as Number and set the Format to Decimal.
Appreciate you letting us know. Hopefully someone will be able to pass that on in the future.


Paul

 

[ddelk]

I dont seem to have a decimal format in my selection. I have a 'Fixed'. When I use it, and set the Decimal to 2, I still get 5 decimal places upon export.

 

[ddelk]

Forget my first comment, the Decimal setting is actually under Field Size, not Format. However, I set the following

Field Size: Decimal
Format: Fixed
Precision: 18
Scale: 2
Decimal: 2

Upon export to dbase IV, I still get 5 decimal places. I changed the Format to General and even Currency and still get 5 decimal places. I am looking at the structure of the dbase file as well and it is getting set to 5 decimal places.

 

[PaulBricker]

Duh! Sorry my clarification wasn't quite so clear. I knew it was the Field Size because I hand to clear it in my own head. Blame it on Monday.

Paul

 

[ddelk]

But it worked for you? Still not working for me.

 

[PaulBricker]

I'm having a worse week than I thought. Sorry, I guess I didn't read close enough and missed a post along the way also.
I haven't actually tried to export anything to dbase in 10 years, so I'm out of that loop although I never remember having that issue (although we don't export a lot of numbers anyway). Can you try something like this on a Query Field line

Left([NumericField],Instr(1,[NumericField],".")+2)

and then try and export that field.

Let me know, I'll try and be a little more attentive seeing as how it's Wednesday.

Paul