No dates in 1st 5 records of excel spreadsheet

  • Thread starter ragtopcaddy via AccessMonster.com
  • Start date
R

ragtopcaddy via AccessMonster.com

I understand that when linking to an Excel spreadsheet, Access determines
from the 1st few records, what datatype to make the field. I have a date
field whose 1st 5 records are null. Access doesn't return any of the values
in that field. They are all null. How do I fix this?
 
M

Mika Oukka

If you are just doing this once, just fill the first record with any example
data with the correct datatypes.

As you have brought the data to access remove this first row.

For repetitive tasks record a macro in Excel to generate the first datarow
and a delete query in Access to delete this particular record.

Regards,

Mika Oukka
 
R

Ron2006

As an alternative to the excel macro you can open the spreadsheet from
access and insert a row in the row 2 location and put example
information in each of the columns to match what they should be. Close
the spreadsheet and then import it.

I have done this in a number of applications to also get around a
decimal overflow warning thatstops the import. The warning seems to be
spurious, but you have no way of knowing which record is causeing the
problem. The above method has solved the problem every time.

Once the table is imported I run an access query to delete that
record.

Ron
 
M

Mika Oukka

Also you cn do the following:

Before importing the data, have blank table ready with correct data
definitions and corresponding fieldnames in Access. Then as importing the
data from Excel just choose to import your data into the existing blank
table.

This is one way to have the data definitions predefined.

Mika Oukka, IT-Consultant
 
R

ragtopcaddy via AccessMonster.com

My thanks to you both for your suggestions.

Mika said:
Also you cn do the following:

Before importing the data, have blank table ready with correct data
definitions and corresponding fieldnames in Access. Then as importing the
data from Excel just choose to import your data into the existing blank
table.

This is one way to have the data definitions predefined.

Mika Oukka, IT-Consultant
As an alternative to the excel macro you can open the spreadsheet from
access and insert a row in the row 2 location and put example
Click to expand...
[quoted text clipped - 10 lines]
Ron
Click to expand...
Click to expand...
 
Top