Re: Summing values where one is #Error doesn't work
- From: "will" <will@xxxxxxxxxxxxx>
- Date: Fri, 18 May 2007 13:50:28 +0100
You hero - I just needed to use Val instead of Clng... didn't even need the
IIF as it defaults to a NULL if it can't handle it. I've been scratching my
head over that for hours and you solved in 2 mins - THANKS!
"Dale Fye" <dale.fye@xxxxxxxxxx> wrote in message
news:A8779970-2179-41D9-BE0A-16EB2AEF73DA@xxxxxxxxxxxxxxxx
How are you converting the text to number?
You might try something like:
IIF(isnumeric([yourField]), Val([yourField]), NULL)
This would put a NULL in the numeric field if the data in that cell could
not be converted to a number, and would be ignored by the SUM( ) aggregate
function.
Dale
--
Email address is not valid.
Please reply to newsgroup only.
"will" wrote:
I have a raw_data table (originating from an external source and pasted
regularly into Access).
I have a normalised_query query based on raw_data and doing 'stuff'
including converting a text column to a number column. Where this
conversion fails (<1% of cases) the column in the query contains #Error.
I now want to run another query which includes a sum of that column, but
because the value #Error appears in one of the rows, the sum fails.
What's the simplest way around this?
.
- References:
- Prev by Date: Summing values where one is #Error doesn't work
- Next by Date: Re: Can you combine multiple functions into one with Access 2002 connected SQLServer
- Previous by thread: Summing values where one is #Error doesn't work
- Next by thread: RE : Access 2003 : Numbering alarms by PermitNo so that after 365 days of the last number 1, the count returns to 1
- Index(es):
Relevant Pages
|
