I need to import tables from excel frequently which have a room number 

field that is mostly numeric, but has a few fields containing a letter.  

It is formatted as text in excel, but for some reason access automatically 

reads it as a double, and the option box on the import wizard where I 

could specify the data type is always grayed out.  This makes for a lot of 

annoying cut and paste.  Does anyone know why this happens?

This is a multi-part message in MIME format.



------=_NextPart_000_0006_01C1A58B.3E1930C0

Content-Type: text/plain;

	charset="US-ASCII"

Content-Transfer-Encoding: 7bit



It sounds as if you are importing into a new table instead of an

existing table.  If this is the case, then the first record from Excel

has a room number that is numeric.  When this happens then Access will

create the room number field as numeric.  If the room number of the

first Excel record was text, then Access would create create a text

field in the table. 



I would create an tblImportTable and create each field to import and set

the Data Type to your preferences.  When you are ready to import the

data, first delete any data in the table then import the file.  I

believe you will find this will solve your problem.



A better way would be to creat a form with a button on it that will

automatically do this for you.  In the button's OnClick event you could

write code to;

1. Delete all records in the tblImportTable  - DoCmd.RunSQL ("DELETE *

FROM tblImportTable)

2. Use the Common Dialog Box to permit the user the ability to select

which Excel file to import. (Only if the path and file name are not

always the same)

3. Use the docmd.TransferSpreadsheet function to import the spreadsheet.



 I hope this helps.



 



John Ruff - The Eternal Optimist J



 





-----Original Message-----

From: Daren Porter [mailto:porterdt@h...]

Sent: Friday, January 25, 2002 6:19 PM

To: Access

Subject: [access] Data type problem importing from Excel





I need to import tables from excel frequently which have a room number

field that is mostly numeric, but has a few fields containing a letter. 

It is formatted as text in excel, but for some reason access

automatically

reads it as a double, and the option box on the import wizard where I

could specify the data type is always grayed out.  This makes for a lot

of

annoying cut and paste.  Does anyone know why this happens?

This is a multi-part message in MIME format.



------=_NextPart_000_0004_01C1A5BD.0ED72FB0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: 7bit



MessageBetter yet, from the import wizard screen click on the "Advanced" button in the lower left corner and make an

import specification for this file.  It will let you specify the field type and name for each field.

  -----Original Message-----

  From: John Ruff [mailto:papparuff@c...]

  Sent: Friday, January 25, 2002 10:30 AM

  To: Access

  Subject: [access] RE: Data type problem importing from Excel





  It sounds as if you are importing into a new table instead of an existing table.  If this is the case, then the first

record from Excel has a room number that is numeric.  When this happens then Access will create the room number field as

numeric.  If the room number of the first Excel record was text, then Access would create create a text field in the

table.



  I would create an tblImportTable and create each field to import and set the Data Type to your preferences.  When you

are ready to import the data, first delete any data in the table then import the file.  I believe you will find this

will solve your problem.



  A better way would be to creat a form with a button on it that will automatically do this for you.  In the button's

OnClick event you could write code to;

  1. Delete all records in the tblImportTable  - DoCmd.RunSQL ("DELETE * FROM tblImportTable)

  2. Use the Common Dialog Box to permit the user the ability to select which Excel file to import. (Only if the path

and file name are not always the same)

  3. Use the docmd.TransferSpreadsheet function to import the spreadsheet.



   I hope this helps.







  John Ruff - The Eternal Optimist J









  -----Original Message-----

  From: Daren Porter [mailto:porterdt@h...]

  Sent: Friday, January 25, 2002 6:19 PM

  To: Access

  Subject: [access] Data type problem importing from Excel





  I need to import tables from excel frequently which have a room number

  field that is mostly numeric, but has a few fields containing a letter.

  It is formatted as text in excel, but for some reason access automatically

  reads it as a double, and the option box on the import wizard where I

  could specify the data type is always grayed out.  This makes for a lot of

  annoying cut and paste.  Does anyone know why this happens?

This is a multi-part message in MIME format.



------=_NextPart_000_0005_01C1A5D6.11A190F0

Content-Type: text/plain;

	charset="US-ASCII"

Content-Transfer-Encoding: 7bit



I do not prefer this method because if you ever have to export/import

the db for whatever reason then you also have to import the

specifications.  If you forget to do this, then you will end up with the

same problem.  

 

John Ruff - The Eternal Optimist J



 



 



-----Original Message-----

From: Leo Scott [mailto:leoscott@c...] 

Sent: Friday, January 25, 2002 4:27 PM

To: Access

Subject: [access] RE: Data type problem importing from Excel







Better yet, from the import wizard screen click on the "Advanced" button

in the lower left corner and make an import specification for this file.

It will let you specify the field type and name for each field.  



-----Original Message-----

From: John Ruff [mailto:papparuff@c...]

Sent: Friday, January 25, 2002 10:30 AM

To: Access

Subject: [access] RE: Data type problem importing from Excel







It sounds as if you are importing into a new table instead of an

existing table.  If this is the case, then the first record from Excel

has a room number that is numeric.  When this happens then Access will

create the room number field as numeric.  If the room number of the

first Excel record was text, then Access would create create a text

field in the table. 



I would create an tblImportTable and create each field to import and set

the Data Type to your preferences.  When you are ready to import the

data, first delete any data in the table then import the file.  I

believe you will find this will solve your problem.



A better way would be to creat a form with a button on it that will

automatically do this for you.  In the button's OnClick event you could

write code to;

1. Delete all records in the tblImportTable  - DoCmd.RunSQL ("DELETE *

FROM tblImportTable)

2. Use the Common Dialog Box to permit the user the ability to select

which Excel file to import. (Only if the path and file name are not

always the same)

3. Use the docmd.TransferSpreadsheet function to import the spreadsheet.



 I hope this helps.



 



John Ruff - The Eternal Optimist J



 





-----Original Message-----

From: Daren Porter [mailto:porterdt@h...]

Sent: Friday, January 25, 2002 6:19 PM

To: Access

Subject: [access] Data type problem importing from Excel





I need to import tables from excel frequently which have a room number

field that is mostly numeric, but has a few fields containing a letter. 

It is formatted as text in excel, but for some reason access

automatically

reads it as a double, and the option box on the import wizard where I

could specify the data type is always grayed out.  This makes for a lot

of

annoying cut and paste.  Does anyone know why this happens?