Summing text field containing blanks

I have a text field that contains mostly numbers, but the rows without
a value have a blank in them. I need to sum on this row, but I get a
conversion error.

I've tried using various functions like

SELECT iif(iserror(cdbl(issuelist.[actual hours])),
sum(0+0),sum(issuelist.[actual hours])) from issuelist;

but then I get the error that the specified expression is not part of
an aggregate function. Short of converting the field to numeric or
updating all blank rows to null prior to the query, is there any way to
construct this properly?

thanks

re: Summing text field containing blanks

<steveprevost@hotmail.com> wrote in message
news:1122998787.105938.235610@g44g2000cwa.googlegr oups.com...[color=blue]
> I have a text field that contains mostly numbers, but the rows without
> a value have a blank in them. I need to sum on this row, but I get a
> conversion error.
>
> I've tried using various functions like
>
> SELECT iif(iserror(cdbl(issuelist.[actual hours])),
> sum(0+0),sum(issuelist.[actual hours])) from issuelist;
>
> but then I get the error that the specified expression is not part of
> an aggregate function. Short of converting the field to numeric or
> updating all blank rows to null prior to the query, is there any way to
> construct this properly?
>
> thanks
>[/color]

SELECT sum(issuelist.[actual hours])) from issuelist WHERE
IsNumeric([actual hours])

re: Summing text field containing blanks

Fabulous...thanks!