Summing values where one is #Error doesn't work

[will]

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?

 

[Dale Fye]

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

 

[will]

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!

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.

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?

 

[Dale Fye]

Glad I was able to help.

--
Email address is not valid.
Please reply to newsgroup only.

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!

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.

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?