Unparsable Record w/ Excel Import

[cherzigMX]

I am trying to import a series of records from Excel into a table in Access 2010. The field names in the Excel table match the field names in the Access table. The field formats are the same as well (both set to numbers).



I am receiving an "unparsable record" error for all fields in the Excel file that are being entered into fields in Access that are 'lookup' fields that allow multiple entries.

The Exel fields have been filed in with the 'numbers' that correspond to the correct 'lookup' selections. There is only one item in the excel field -> while access will allow multiple entries for the field, I for the records I'm importing I only need one.

For the fields that are 'lookup' but allow only one value the excel data is being imported and displayed correctly in Access.

Do I need to format the fields that are matching to multiple entry fields differently or in a special way?

Thanks for your help.

[Robeen]

First - I don't use Lookup fields.. . either in Excel or in Access.

I usually let Access 'Import to a New Table . . .'.
That way Access creates the table and gets the data types 'right'.
I HAVE had problems when I try & do it the way you describe & create a Table & assign fields what I think to be the appropriate data types.

Have you tried letting Access create the table for you?

[cherzigMX]

I have tried having Access import to a new table but still ran into trouble trying to append the records to the table that already existed, which is ultimately where I need the data... But it's possible I built my append query incorrectly...

[ssanfu]

Do I need to format the fields that are matching to multiple entry fields differently or in a special way?

Yes. Get rid of the "Look Up Field".

A look up field is different than a look up TABLE.

See
http://access.mvps.org/access/tencommandments.htm
and especially
http://access.mvps.org/access/lookupfields.htm

Like Robeen, I never use "look up fields". They hide the true data...

Here are two more sites to look at:
http://www.utteraccess.com/forum/Mul...-t1964036.html
http://social.msdn.microsoft.com/For...-83004700e144/

[cherzigMX]

Not using "lookup" field is not an option. But, perhaps I'm using the wrong language here...what I'm calling a 'lookup field' is a field that looks up & displays data from another table, the user then selects the value(s) they want to assign to the new record. Due to the complexity and accuracy needs of our access DB we need the values input to be selected by our users vs. always typing in the value that they may or may not know.

Cecily

[ssanfu]

Just to be sure we are talking apples & apples, a field in in a table.

A form has controls (bound or unbound). A combo box control can "look up' values from a different table.

This is different from a "look up field". A look up field is defined in a table when you are creating/modifying a table; in design view, you set the field type (Text, Number, Date/Time). At the bottom of the display are two tabs. If you set the field type as Number, the "General" tab is where you can set what type of number: Integer, Long Integer, Single... The "Look up" tab is where you can change the Display Control from "Text Box" to "Combo box". This is the infamous "Lookup Field".

A combo box control on a form to look up values - GOOD
A look up field in a table - BAD

[cherzigMX]

Ah! We're talking the good here -> A combo box control on a form to look up values

[ssanfu]

So the next thing to do is get rid of the multi-valued fields.

As you see by the last two links I posted (post #4), you can't import to multi-valued fields (without a lot of code)