Exporting query data to Excel and hyphen character

[antifashionpimp]

Hello all,

I have always been wondering why this happens, and now I have a problem with it.

When I export a Table/Query from Access to Excel (be it with Right CLick -> Export...or TransferSpreadsheet) there is an apostrophe character (') appended to the front of some/each cell in Excel. You cannot see it immediately, but when you click on the cell, there is this character. WHY? Does anyone else have experience in this?

Thanks in advance.

 

[antifashionpimp]

I actually had a look here, and this has given me a tip.

 

[antifashionpimp]

OK, now I am stuck again:

The following query is causing the problem:

SELECT qryAbtGruppenProblemSumme.ABTGRUPPE, qryAbtGruppenProblemSumme.ANZPROBPUNKTE, Nz([qryAbtGruppenProblemSumme_MaTerminÜber].[ANZPROBPUNKTEMASSUEBER],0) AS ANZPROBPUNKTEMASSUEBER
FROM qryAbtGruppenProblemSumme LEFT JOIN qryAbtGruppenProblemSumme_MaTerminÜber ON qryAbtGruppenProblemSumme.ABTGRUPPE = qryAbtGruppenProblemSumme_MaTerminÜber.ABTGRUPPE;

In the output, the column ANZPROBPUNKTEMASSUEBER contains the apostrophe appended. The table where this data comes from is a ODBC source, I checked the data type though and it is NUMBER. However, in this query it seems to be text!

Cant I override this by specifying the format of this column in the query?

 

[antifashionpimp]

Found the solution:

INT(Nz([qryAbtGruppenProblemSumme_MaTerminÜber].[ANZPROBPUNKTEMASSUEBER],0)) AS ANZPROBPUNKTEMASSUEBER

changes the format to Integer