Using the SUM expression w/ numeric & text values? (1 Viewer)

[Sboynton]

I am attempting to total a record on a report. It is dollar amounts for the most part, but there are some cases where the dollar amount is unknown, so "unknown" is just typed in the field instead of the number. When I go to calculate the record in my report using the SUM expression, I get an error. Is there a work-around for this, so that Access can add up all the numbers and just leave out the "unknowns"?

 

[chrismcbride]

If I understand this correctly, you have both numbers and text going to the same data table. If this is the case then the data type for this field must be text. If that is the case then you probably can't use Sum.
I suggest that you set the field data type to number (double) and have your users make no entry if the price is unknown. You can then use and IIF statement to print "Unknown" where ever there is a null value. You will then be able to use Sum but you will need another IIF that substitutes null for 0 when calculating the Sum.
Good Luck
Chris

[This message has been edited by chrismcbride (edited 10-05-2000).]

 

[Atomic Shrimp]

I would do it like Chris suggests, but use the Nz (null to zero) function to cause the sum to see null values as zero.

Mike