I am trying to take a Excel spreadsheet and link the sheet to a Access
database. The problem is that part of the information is in fractions
when I
import it into the database all the fractions turn into decimals. I have
changed the format to text and re-typed the fractions in and that seems to
work but the information that I am using is quite extensive and re-typing
each cell would take hours.
Is there an easier way for this to be linked or imported?
If I am reading you correctly, you have stuff in a cell in an Excel
worksheet that looks like this: "1/4"
And after it imports to an Access table, it looks like this: ".25"
I have never personally done an import from Excel to Access that contains
fractions, but here are a few tips:
1. To retain the text, never EVER use a Number data type in Access for
stuff that looks numeric. For example, my ZIP (Postal Code) happens to be
"19021-6020". If I store that in Access as Text, it retains the proper
value. But if I store it in Access as Numeric, then Access interprets the
"-" character as a subtraction symbol, automatically does the math, and
stores the result. So in the case of my ZIP Code, I get "13001" stored in
the database.
2. Same thing with phone numbers, social security numbers, or any other
value that looks like numbers but has "-" embedded in it. And you would not
believe the bizarreness that happens when a phone number extention is
prefixed with a "/" character: (215)-555-1212/1234
3. The way to handle this kind of input is to make sure the column in
your Access table is defined as Text instead of Number. The only time you
should store stuff in Access as a Numeric datatype is when you fully intend
to do math on it. Since you never do math on a postal code or phone number,
just store it as text.
4. Once you have done that, and you define your "xyz" column as text, and
then you import some stuff into it, occasionally, Access's Import Wizard
will screw it up, because the Import Wizard is brain-dead.
So once you have converted your numeric column to text, and then fixed up
all your data bindings, forms, code and so forth, it is still possible for
the Import Wizard to mess up your data. If this is what you are
experiencing, then you will have to put the Wizard behind you and just write
your own code to open the file, read the contents, build a giant recordset
object and run through a loop, saying rs.Update for each row in the file.
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei
news:
[email protected]...
