text to double during import

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'm trying to import two excel spreadsheets into Access 2000. All fields, in excel, are set to text, yet when I import them in access this one field appears as double. What's even more confusing is that of the two files, A and B, this only happens for one of them

The field ID appears in both spreadsheets and has been set to format text

When importing the two spreadsheets into access, A.ID is imported as a double while B.ID remains as text. As a result I am getting a mismatch when trying to compare the two fields. Also, I am unable to change the data type during import as this option isn't available

Would be very grateful for any help

Cheer
/Greg
 
Hi Greg,

You can't "set" Excel cells "to text". You can *format* them as text,
but Access ignores this and just looks at the actual contents to see if
they are numeric or text.

One possibility is that one of your worksheets contains ordinary numbers
and the other contains numbers that Excel is storing as text. (You can
force Excel to do this by starting the value with an apostrophe '; this
appears in the formula bar but not in the worksheet or in exported
data.) Another is that there's a text value in the first few rows of
B.ID and this is forcing Access to import the entire column as text.

The above gives you one line of attack. Another is to import the data as
it comes and then to change the type of A.ID. You can this as follows:

1) in A, rename ID to OldID
2) create a new text field called ID
3) use an Update query to update ID to something like
CStr(CLng([OldID]))
4) set ID as the primary key
5) delete OldID
 
Greg said:
Hi,

I'm trying to import two excel spreadsheets into Access 2000. All fields, in excel, are set to text, yet when I import them in access this one field appears as double. What's even more confusing is that of the two files, A and B, this only happens for one of them.

The field ID appears in both spreadsheets and has been set to format text.

When importing the two spreadsheets into access, A.ID is imported as a double while B.ID remains as text. As a result I am getting a mismatch when trying to compare the two fields. Also, I am unable to change the data type during import as this option isn't available.

Would be very grateful for any help.

Cheers
/Greg
Click to expand...

Another thing you can do is put a single quote ' in front of the value
in the first cell. Access will see this this column or field as text.

exp:

1234
'1234

Ron
 
Back
Top