Eliminating records with #Num!

[Lambi000]

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!

 

[Clif McIrvin]

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

 

[Lambi000]

Thanks, Clif. I can't turn the field into text because inevitably this date
field subtracts from another date field. It's one of those "how long did
this job take to do" kind of thing. The #Num! field means that nobody
responded to the job and the job probably shouldn't be there.

But you made me realize something. Since I write the SQL that sends me that
spreadsheet, I may as well just try to eliminate those records from the
host's recordset.

Thanks!

 

[Clif McIrvin]

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 just opened up my db with the linked sheet and verified that there are
some Null values in my linked date column -- the date column of my
linked sheet looks like this:

Row Date Flag
1 Header Flag
2 date x
3 date x
4 text x
5 date x
6 date x
7 text x
8 <real data begins> <empty>

In the linked table, rows 4 and 7 show #Num!, and any blank cells
beginning with row 8 show Null.

I added an extra 'flag' column at the end of the spreadheet data so I
could identify the throw-away rows.

 

[Clif McIrvin]

Thanks, Clif. I can't turn the field into text because inevitably
this date
field subtracts from another date field. It's one of those "how long
did
this job take to do" kind of thing. The #Num! field means that nobody
responded to the job and the job probably shouldn't be there.

If you can make it text in the spreadsheet you can link it without
errors and use a calcualted field in a query to turn in back into a
date.

But you made me realize something. Since I write the SQL that sends
me that
spreadsheet, I may as well just try to eliminate those records from
the
host's recordset.

Either that, or leave the logic for a later process and modify the SQL
to generate a formatted text field instead of a date field -- perhaps
insert a " ' " (apostrophe, or single quote) as the first character of
that field ... that should force Excel to format all cells as text data.

Thanks!

You're welcome.

When I couldn't find the information I was trying to remember I searched
the archives for #Num! and found a couple very informative replies:

http://groups.google.com/group/microsoft.public.access/msg/d828f84b12333396
or http://tinyurl.com/3vt5rm

and

From: "Van T. Dinh" <[email protected]>
Subject: Re: link with excel returns #NUM errors
Date: Mon, 25 Apr 2005 10:37:52 +1000
Newsgroups: microsoft.public.access

I find the easy way is to modify the Excel file as follows:

* Insert a "calculated" Column next to the MixedColumn using the
function
TEXT() as follows:

= TEXT([CorrespondingCellInMixedColumn], "General")

for all cells in this Column. Save and close the Excel file.

* Link the Excel file to Access, ignore the original MixedColumn and use
the
"calculated" Column. All values in this Column will be Text so the
values
won't have #NUM entries.

--
HTH
Van T. Dinh
MVP (Access)
------------ end quote

at
http://groups.google.com/group/microsoft.public.access/msg/ffb18303e837d5aa
or http://tinyurl.com/4vf6uv


You could try
= TEXT(RC[-1], "General")
as the formula, and use your SQL to add the calculated column to your
output.

 

[Clif McIrvin]

I'll add that in the Excel sheet I'm linking to I found that a cell
containing a single space (in an otherwise numeric or date column) would
link as #Num!.