Lambi000 said:
Hi,
We get a lot of info from outside sources. I link to a spreadsheet
that has
a time field that often doesn't get filled in. When I look at the
linked
spreadsheet, those fields appear as #Num! as well they should. I
would like
to eliminate those records within Access but I can't figure out the
criteria.
It's not null and it's not "#Num!", nor is it / / : :, the
placeholders for the date.
Really it's just a blank date, so what can I do to get rid of those
records
short of doing something in Excel?
Help!
This is not an easy issue to resolve. It has to do with the fact that
Access makes no provision for type conversion when linking to an Excel
sheet. One solution is to force Access to consider the column to be
text instead of date; then build a query on the linked table that uses a
calculated field to convert the column values to dates (or null).
Access determines the data type for each column by looking at the first
few (1? 5? 8? - I've not been able to determine that to my satisfaction)
rows and taking a 'majority' vote on what it finds. (The process is
discussed in the installed help on linking to Excel data .) There are
priority rules that come into play, so it's not a simple majority vote -
and I neglected to keep track of that information.
In my linked spreadsheet, I have manually modified the first 7 rows
(inserted dummy rows) to be populated with the data type I want in each
column. The problem with this is that Excel is fond of setting the data
type for individual cells, and if Access finds a date type while linking
a text column it will report the #Num! error. The work-around is to use
the procedure in this KB article to force every cell to text type in the
Excel sheet.
(815277) - Explains the "Numeric Field Overflow" error message that
occurs when you query a table that is linked to an Excel spreadsheet.
This article provides a workaround to resolve this problem. Requires
basic macro, coding, and interoperability skills.
http://support.microsoft.com/kb/815277/en-us
Are the date cells that show up #Num! in the linked table in fact empty
(blank - not a space or zero length string) in the Excel sheet? I seem
to remember that Access will properly link Nulls into empty Date cells
when linking. If the missing dates are in fact non-blank cells in the
Excel sheet, the procedure presented in KB815277 could be altered to
clear the offending cells.
I have seen several discussions of this topic, either in these access
forums, or in the excel forums - some of them were extremely helpful.
HTH