ACCESS TO EXCEL

[pauld]

I am using Access 2003.

When exporting a query to Excel 2003, some "Number" fields seem to have more
characters added to the right of the decimal point than are in the Access
Table. For example, the field contains 2 numbers to the right of the decimal
point in Access, but after exporting, the field in Excel shows 6 numbers to
the right of the decimal point.

Is there any way to "force" the same numbers to the right of the decimal
point?

I'm wondering if this is an Access issue or an Excel issue.

 

[Jerry Whittle]

It could be an Access issue in a couple of different ways.

One would be the well known floating point issue. Do a Ctrl + g key
combination and in the Immediate window type:
Debug.Print 3.1 - 3.11

That sure isn't the -0.01 that you would expect. Close but no cigar!

Another thing that might be happening is that your data actually has that
many decimals stored in the table; however, you have set formatting to only
show 2 places.

You can force the number of decimals by using the Format Function.

Format([TheField], "0.00")

However the Format function changes the values from numbers to strings.

If 4 decimal places is exceptable, you could use the CCur function as it
will return a number:

CCur([TheField])