Getting -0 for 0 in Excel When using Transferspreadsheet

[John_]

I am using the transferspreadsheet command in a macro to export a table to
Excel (using Access 2002). When I open the spreadsheet in Excel, any zero is
appearing as -0 in Excel. However, when I open the table in Access, these
same fields appear as 0. Is there a way to fix this so zeros don't appear
with the negative sign? Thanks for your help.

 

[Ken Snell MVP]

I'm guessing that your field in ACCESS table is a floating point number data
type (Single or Double). What you see is caused by rounding. Although you
see 0 in the field, it may actually be this number:

-0.0000000000000003

Which is 'seen' as zero in the display, but is not actually zero.

You can use a query to get the data from your table, using a calculated
field to convert the almost zero value to an actual zero:

SELECT Field1, Field2,
IIf(Abs(Field3 - 0) <0.000000001, 0, Field3) AS Field3,
Field4, Field5
FROM Yourtablename

Then export this query to your EXCEL file.

 

[John_]

Thanks Ken! That worked great! I really appreciate it.