Re: Access linked table column shows datatype text for oracle number column dimensioned as 16,0 why
Date: 2000/05/27
Message-ID: <8gnkk5$j3t_at_journal.concentric.net>#1/1
(duplicate response sent to originator's email address)
Sean,
I'll try to keep this short ;)
Access can't handle (display/store/represent) numeric data with more than 15
significant
digits. Period.
The "double" data type only has 15 digit precision, and long integers are
useless
(dangerous) beyond 9 digits. Check the Access Help file under: "Data Types"
The only data type Access has that can properly represent (display) the field is "text".
In a similar fashion, "text" fields are, for some inexplicable reason,
limited to 255
characters.
Yes it is a HUGE limitation in Access, and to my knowledge there are no
direct
work-arounds.
If (by chance) the actual data values in these numeric fields are always 15
digits
or less, you could take advantage of the [CCur] or [CDbl] built-in functions
in
a calculated query field to convert the "string" data into a numeric field
in a query.
Since Access absolutely can't represent numeric data > 15 digits, if data
exceeds
this, all you'll get is an #ERROR# in that field. I'm sure you've seen this
when
Excel can't interpret (or calculate) a cell's value.
VBA has a "decimal" variant data type (sub-type?) which will allow you to
perform
calculations once you converted it in VBA code (using the [CDec] function),
but
neither the data type, nor the [CDec] function can be used in calculated
query fields.
It's a case of converting TO decimal, perform your calculations, then
convert BACK to
a string for storage, ie: It's a lot of work.
Microsoft gave us all a really raw deal on this one.... along with the
fantastically myopic
255 character string limitation... what WERE they thinking?!
Welcome to another of Access' numerous limitations/frustrations. It re-iterates the point I've had to swallow a number of times:
Access is un-fit for mission-critical applications.
Good luck,
Brian
"Sean Harvell" <sean_at_harvell.com> wrote in message news:8gmakd$kjl$1_at_hiram.io.com...
> Access linked table column incorrectly shows datatype as text for an
oracle
> number column dimensioned as 16,0
<SNIP>
> Thank You!!!
> -Sean M. Harvell
> sean_at_harvell.com
Received on Sat May 27 2000 - 00:00:00 CEST