Setting field type for mixed content (1 Viewer)

Hi,

I have an excel file coming from a 3rd party supplier as a source. One of the fields in the excel contains serial numbers, which are in most cases just numbers but occasionally there are cells which begin with characters, followed by numbers. When I link the excel to Access (2016), the serial number column is automatically assigned "Number" Data Type. I wouldn't want to convert the excel to a local table, since it changes almost daily on a shared folder, so keeping it as linked works best.

However, this leads to a problem. When I generate a query to pull the data from the excel file to a table (with modifications), all of the serial numbers which have characters in the cells appear as empty cells in the Access table. If I use Str, Right or Left strings, the same happens. If I use Val or Cstr, both lead into a Null or conversion error.

How can I get Access to understand that the source should not be numbers but text, and it would therefore handle the source data as text before doing anything else to it? Or is this a lost cause...?

Thank you!

Best regards,

Yari

One option is to import the data to a table - when importing you can specify the type of field - which you wil need to specify as text.

the other option is before linking to sort your excel file so you have the text values at the top - Access reviews the first few rows (think it is 16) to determine the data type - and blanks will be treated as text.

Hmm, the sorting option worked, but only if I do the sorting every time for the source file. When the file gets updated, it is again setting the field type to Numeric, giving the same error again. I wouldn't want to be sorting it every day to get the link to work...

I wonder if I could generate a query to do the import, or a VBA macro, so that I can update the data within Access with a click of a button? I see there is ImportExportSpreadsheet function in the Macro Tools, but that doesn't seem to have options for setting field types.

Cheers!

Yari

you can try, but not sure what the exact structure would be, you'll need to google, but it is something like

SELECT * FROM myTable IN (C:\....)