Trimming Decimal Places on a DBF Export 1

[efinnen]

Hey All

Any help would be greatly appreciated.

I have a series of tables that are formatted correctly in Access. When I batch export them (or do them one at a time) to a dbf They come in with decimal places. Anyone know how to remove them?

I'm somewhat at a loss since they are defined as Integers in Access but seem to lose that characteristic when getting exported.

Any advice?

Thanks
Eric

 

[JeremyNYC]

Eric,

Have you played with the Format function? And, does it matter if they have trailing decimal zeros in the dbf? And, if it's a one-time thing you're doing, maybe you could convert the field to text before doing the export, as you shouldn't lose anything from an integer field.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[efinnen]

Unfortunately this process will have to be repeated on a frequent basis

I have not played with the format function (yet). Any suggestions would be appreciated. What I have been doing is running Alter table commands on it.

Code snippet below:
Select Case rst("Exportname&quot
Case "polbnda"
AlterFieldType rst("exportname&quot, "F_Code", "TEXT", "5"
AlterFieldType rst("exportname&quot, "ACC", "INTEGER"
AlterFieldType rst("exportname&quot, "Nam", "TEXT", "75"
AlterFieldType rst("exportname&quot, "USS", "INTEGER"
MsgBox "polbnda"

where AlterFieldtype is a module which just runs a docmd.rumsql alter table.


I'm running all these processes and then importing the dbf's into another program. Which can be a real pain in the butt for proper input formats. In other words if the dbf has trailing zeroes. It gets categorized as something else. Whereas no zeroes it gets lumped as another category (floating/integer etc)

Any of this info helpful??

Thanks again
-Eric

 

[KenReay]

Hi

From your comments so far I assume you are doing the export via a docmd.otputto or docmd.tranferdatabse type command

Am I right?

If yes, why not define your dbf files exactly as you want them ie no decimal places etc, and then link to these tables in Access (this is quite possible via getexternaldata\link), then you can populate the tables using SQL append queries, and if the column(s) in the dbf is defined in advance with no decimal places, I would have thought this would solve the problem

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[efinnen]

Ken

That was it thanks.....

Linking the external dbf (with correct fields worked fine)

-Eric