Analyze With Excel (Access 2003 Bug ??????)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MarkS - 04 Jul 2007 09:58 GMT

I have two records which contain the following values in a Text Field of
length 18
5624-010
4068-005
I created a query and used the Analyze With Excel Function within Access.
The records displayed correctly.
Then I created a report based on the Query or the Table. From the Report
Preview I chose the Analyze With Excel Function and the records displayed as
1360439 and 791969 incorrectly.

This is a scaled down version of the problem. This only seems to happen in
Access 2003 and not 2000 as I have checked this. Is this a known bug or do
the records form so mathematical notation. I have several other examples if
required;
8535-001
5057-006

Reply to this Message

Andy Hull - 04 Jul 2007 14:40 GMT

Hi Mark

Excel is treating the info as a date and displaying the Excel code for the
relevant date.

To see for yourself, go into excel and type 1360439 and 791969 into the
first 2 cells.
Now, format these cells as a date with a format like dd-mm-yyyy.

You will see, 01-10-5624 and 01-05-4068 respectively.

But what you really want to know is how to stop it!

The only way I have found to work around it is to add a character such as a
single quote to the beginning of the field in the query that feeds the report.

Regards

Andy Hull

> I have two records which contain the following values in a Text Field of
> length 18
[quoted text clipped - 12 lines]
> 8535-001
> 5057-006

Reply to this Message