[AccessD] Excel link weirdness

Thanks, I will gve that a try.

Debbie 

-----Original Message-----
From: Gustav Brock <Gustav at cactus.dk>
Sent: Friday, November 07, 2008 10:59 AM
To: accessd at databaseadvisors.com
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com