Query - Convery Text to Number in a group by

[SAC]

Ms Access 2003

I'm trying to convert text to a number in a query where the column is
grouped.

Val(whateverthetextis) works fine, but then when I click the sigma for a
totals query I get data type mismatch error.

Thanks for your help.

 

[John W. Vinson]

Ms Access 2003

I'm trying to convert text to a number in a query where the column is
grouped.

Val(whateverthetextis) works fine, but then when I click the sigma for a
totals query I get data type mismatch error.

Thanks for your help.

Please post the SQL of the query and indicate what you're trying to sum and/or
to group on.

 

[SAC]

Thanks!!! the nulls were stopping it.

You should be able to aggregate the values returned by the Val function
without any trouble. In fact if the column contains text values which can
all be interpreted as numbers then you can aggregate the text values
directly.


If any of the values cannot be interpreted as a number, however, then you
cannot aggregate them directly, but if the Val function is used to return
the
text as numbers, any which cannot be interpreted as a number will return
zero,
so the values can then be aggregated.

If any row contains a Null in the column, however, then the Val function
will
not operate on the Null, so you'd need to use the Nz function to return a
value in place of the Null, e.g. a zero:

Val(Nz([YourColumn],0))

If you still have problems post back with the SQL of the query.

Ken Sheridan
Stafford, England

Ms Access 2003

I'm trying to convert text to a number in a query where the column is
grouped.

Val(whateverthetextis) works fine, but then when I click the sigma for a
totals query I get data type mismatch error.

Thanks for your help.

 

[SAC]

I didn't use the NZ function so the nulls were stopping it.

Thanks for your help!!