By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Summing text field containing blanks

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Baz

<st**********@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
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


SELECT sum(issuelist.[actual hours])) from issuelist WHERE
IsNumeric([actual hours])
Nov 13 '05 #2

P: n/a
Fabulous...thanks!

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.