By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,153 Members | 2,581 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,153 IT Pros & Developers. It's quick & easy.

weired behavor

P: n/a
Hi All,

In AccessXP I have a report, in print preview the report looks good,
when I choose Office Links - Analyze It With Excel the spreadsheet
opens and will have different values in a particular field for 6 of
the 45 records; example, in the field NbrValue for one record the
print preview shows 03-592998 but then when I choose analyze it with
excel that same record/field in the spreadsheet shows 465380.
I searched the table for that value and it does not exist in any field
so I have no idea where/how the transportation of data from the report
in report preview gets translated to a different value.
Anyone else ever experience this?
bobh.
Dec 11 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Tue, 11 Dec 2007 13:35:09 -0800 (PST), bobh <vu******@yahoo.com>
wrote:
>Hi All,

In AccessXP I have a report, in print preview the report looks good,
when I choose Office Links - Analyze It With Excel the spreadsheet
opens and will have different values in a particular field for 6 of
the 45 records; example, in the field NbrValue for one record the
print preview shows 03-592998 but then when I choose analyze it with
excel that same record/field in the spreadsheet shows 465380.
I searched the table for that value and it does not exist in any field
so I have no idea where/how the transportation of data from the report
in report preview gets translated to a different value.
Anyone else ever experience this?
bobh.
Excel likes to do the math for you. No idea how it is coming up with
those particular numbers. If there are no text characters in the
field, it will often assume that it is numeric and do the subtraction.
Dec 11 '07 #2

P: n/a
On Dec 11, 4:35 pm, bobh <vulca...@yahoo.comwrote:
Hi All,

In AccessXP I have a report, in print preview the report looks good,
when I choose Office Links - Analyze It With Excel the spreadsheet
opens and will have different values in a particular field for 6 of
the 45 records; example, in the field NbrValue for one record the
print preview shows 03-592998 but then when I choose analyze it with
excel that same record/field in the spreadsheet shows 465380.
I searched the table for that value and it does not exist in any field
so I have no idea where/how the transportation of data from the report
in report preview gets translated to a different value.
Anyone else ever experience this?
bobh.
I'll take a guess. Access tries to read "03-592998" as a Variant Date
before returning the number of days from #12/30/1899#. In converting
592998 into years, Access appears to check a sign bit at 2^16,
adjusting the value modulo 2^16 and use the remaining bits (or their
two's complement if negative) to obtain an Integer which it uses as
input for the year. If the resulting number (when positive) is
greater than 9999, a "Type Mismatch" error occurs as normal.
Otherwise, the CDate function treats the year value input as it
usually does. 592998 becomes 3174. #3/1/3174# =465380 days. Note:
CDate("03-65530") =#3/1/1994# which is -6 years from #3/1/2000#
(I.e., 65530's two's complement is 101 + 1 = 110 =-6, and seems to
fall at first glance under the special year rules) so negative years
between -1 and -99 might act similarly to the positive years between 1
and 99, but that did not seem to be the case given my limited amount
of testing. Note that the negative values are positive arguments when
they enter the CDate function so they sneak past, so to speak. I may
be wrong, but it seems that all the negative year inputs use the year
2000 for their base. For example, CDate("03-64630") =#3/1/1094#
(I.e. 1111110001110110 =1110001001 + 1 = 1110001010 =-906, which
is in range when a base year of 2000 is added), but CDate("03-63530")
="Type Mismatch," since 63530 = 1111100000101010 =0000011111010110
= -2006 and 2000 - 2006 < 0. I'll have to try out a few more examples
to make sure that 2000 is used for all the negative values. Maybe
this will all make better sense to me after a night's sleep.

James A. Fortune
MP*******@FortuneJames.com
Dec 12 '07 #3

P: n/a
On Dec 12, 12:25 am, CDMAPos...@fortunejames.com wrote:
>
I'll take a guess. Access tries to read "03-592998" as a Variant Date
>...
I've had some time to think about the problem. First, I came about it
from Access' perspective instead of from the Excel perspective, but
the date conversion methods are likely identical. Without actually
checking the results :-), it seems that negative numbers using 2000
for their base is simply an extension of the special rules for years
under 30. I.e., since negative numbers weren't expected as input,
they follow the condition of '< 30' that was meant for years 0 to 29.
So why were Long's allowed as input for years instead of Integers? It
is my best guess that many of Access' built-in functions are simply
calls to API functions already being used by Windows such as CharUpper
by UCase(). Whether an API function or a DLL is used, the data type
used for the year is wider than a VBA Integer. The value checks for
years between 100 and 9999 along with the special rules for years 0 to
29 and 30 to 99 at first glance appear to handle the bounds, but the
negative values from conversion of the Long year to a signed variable
seem to be a surprise to the author(s) of the CDate function. Even
more subtle is the way the negative values fall between the cracks
into the logic for years 0 to 29. These are all guesses, but they
seem to explain most of what you discovered.

James A. Fortune
CD********@FortuneJames.com
Dec 14 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.