[AccessD] Excel link weirdness

Hi Debbie

You may try this tip:

Create a select query which takes all the fields from the Excel table and converts them to strings using CStr() which - contrary to Str() - accepts a string as input and strips the leading space from positive numbers.

If your linked Excel table is called xlsImport, something like this:

SELECT
    CStr(xlsImport.ID) AS ID,
    CStr(xlsImport.PartNo) AS PartNo,
    CStr(xlsImport.Specification) AS Specification,
    etc. ...,
    CStr(xlsImport.Note) AS Note
FROM
    xlsImport;

Then, whenever you need the data from that sheet (or named space), pull them from the query and not from the linked table.

If a field can contain nulls, modify the expression for that field like this:
    CStr("" & xlsImport.Note) AS Note

or, if you prefer so:
    CStr(Nz(xlsImport.Note)) AS Note

/gustav


>>> delam at zyterra.com 05-11-2008 19:48 >>>
I am linking an excel spreadsheet into an access database, both 2002.  Some fields are not translating over to access at all and I cannot discover the reason.  For instance one field is client/matter number and the values often repeat.  The identical value will sometimes show up and other times be the #!NUM# I cannot interpret value.

Any thoughts of some gotchas to look for?  

Debbie 

Debbie Elam