Sum

[kbreiss]

I have a report that has sums in the "report footer" In the control source of my sum text box I have "=Sum([initial_verf])"....and it works fine with all numerics being in the initial_verf. However some of my data contains "N/A" and it throws an error b/c the "N/A" is not a number. How can I have my sum not look at the "N/A"....or however so I don't get an error? Thanks in advance,

Kacy
________
Lovely Wendie99

 

[KenHigg]

Try some thing like:

=sum(iif([initial_verf] = "N/A", 0,[initial_verf]))

I haven't tried this but you can give it a whirl...

 

[kbreiss]

thanks for the response...unfortunately I'm still getting an error message....any other suggestions?
________
St. catharines components plant

 

[Rich]

=Sum(Iif (IsNumeric([initial_verf]),[initial_verf],0)) ?

 

[kbreiss]

I still get the "Microsoft Jet database engine could not execute the SQL statement because it contains a field that has an invalid data type" error....any other ways around this? They want to see "N/A" in the report rather than leaving it blank or putting it at zero. Thanks in advance,

Kacy
________
STARCRAFT 2 HOW TO GET REPLAY

 

[Pat Hartman]

If you need to do arithmetic with a number, store it as a number! Don't store it as text. You can use null to mean N/A and not have to worry about aggregate functions handling the value properly. They all ignore nulls.