import error "Subscript out of range" (from Excel to Access) 1

[surfside1]

I'm importing an excel worksheet into Access. I've done it many times but this particular one has problems.

It says "1st row contains data that cannot be used for valid Access field name". My first row is the heading and I specified 1st row contains column header. Nothing there seems to contain any words that would be something access would care about.

I ignore that and it continues.

I then get this error "Import Spreadsheet Wizard
Subscript out of range"

The error table shows 100 rows "Type Conversion" error. It shows 3 rows for a date field, and the rest text fields for zip code. The worksheet has 1300 records. I sucessfully imported the same worksheet with only 1200 records and the columns haven't changed much, just added a few more columns and the additional rows.

When I look at the errored rows they don't look any different then the other 1200 rows. In fact I got these same errors on the previous successful import, but it went ahead and imported. This time it is not.

Andy ideas? Thanks!

 

[SkipVought]

Hi,

Do you have VALID DATES in all rows of your date field?

Skip,

_[red][/red]

 

[surfside1]

There are only a few dates but the column's format is date the rest are blank.

As far as the zip code some have 12345-1234 as example and it is defined as text.

 

[SkipVought]

"...the column's format is date the rest are blank."

Try entering a ZERO in each blank field. to do that, enter a 1 in a blank cell on another sheet, COPY the cell, select the date column, Edit>Paste Special - MULTIPLY. The delete the ROWS below the table that do not contain data but have ZEROS in the date column.

Skip,

_[red][/red]

 

[surfside1]

Thanks, I tried but will do it again in the A.M. I did it for date, do I need to also do for the zip because I got the same error.

Is there a way to remove the zeros during a find/replace or something? I have 1300 recs.

Thanks so much! I'll have to read up on the multiply!

 

[SkipVought]

I was referring to Excel functionality.

Skip,

_[red][/red]

 

[surfside1]

Hey Skip, thanks alot, everything is fine!

 

[surfside1]

I was able to import the table and I changed the date field from text to date and it said it was going to delete ___ records, I told it to and it blanked out the dates I had. When I reimported it and didn't change the date field to text, it put the numberic value like 39125 in the field. How do I keep it from elinating the value and keeping it in a date format?

There are only a few dates completed in XL so I can update the values to their dates in Access, but I think I will be updating the XL a few more times and re-importing before I keep everything in Access and manage it there.

Thanks!