Aug 8 2007, 03:54 PM
In Access, is it possible to write a query that sums up a column, but skips over rows that are not numeric so that it does not return a datatype mismatch error?
Aug 8 2007, 04:22 PM
You will need to create a query that eliminates those records first before doing the Totals query. HTH.
Aug 8 2007, 05:48 PM
theDBguy is right and this is one way you might do that...
where isnumeric(field) = true
Aug 9 2007, 08:53 AM
Or you can use iif() to skip over just the values in that field without eliminating that record entirely if different fields will have null in different records. Like below:
select sum(iif(isnumeric(field), field,0)) as sumoffield, sum(iif(isnumeric(field1), field1,0)) as sumoffield1 from table
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here