Numeric Field Overflow

[KWhamill]

I have a table linked to a spreadsheet and on that spreadsheet there is a
column (H, F8 on this query) formatted in currency with no symbol. The link
table has it formatted as double with an * leading. I have written a query
to filter that table specificaly to exclude the items in this column that are
Zero. when i add the criteria <>0 i get a numeric field over flow. Not right
away but after i run the query a couple of times. I've included the SQL but I
can't imagine that this is the problem. I can't modify the spreadsheet But
for my purposes it doesn't matter what kind of Number is in Field F8 as long
as it is a number and i can do math with it.
SELECT link_life_amort.F3, link_life_amort.F4, link_life_amort.F5,
link_life_amort.F7, link_life_amort.F8, link_life_amort.F10
FROM link_life_amort
WHERE (((link_life_amort.F8)<>0));

 

[John Spencer]

I doubt that putting a # sign before the zero will work. It will
probably cause a syntax error (in a query).

You might try the following.

WHERE IIF(IsNumeric(F8),CDbl(F8),Null) <> 0

However, if the stored value has a leading "*" sign that will not work.
You might try

WHERE IIF(IsNumeric(Mid(F8,2)),CDbl(Mid(F8,2)),Null) <> 0

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KWhamill]

Sorry Chris but acces thought i was trying to specify a date with improper
syntax

 

[KWhamill]

John,
I had my hopes. I had thought of the First one but the second. still after
an initial success I get Numeric overflow. I can only run this query once
before I have to go back and edit and run it again. Or I have found based on
something I found elsewhere that if i compact and repair the database the
query works.