Need to change Negative numbers to zero in already calculated fiel

[Guest]

I have a bowling database that I enter in all of the weekly scores in one
table, then I use a query to figure out all of the stats, eg.., Total Pins,
Average, Handicap. Only problem is with the handicap field because we have
bowlers with zero handicap but the calcualations show a negative number. The
way our handicaps are figured out is a bowler's average minus 220 then take
90% of that and that's their handicap. I haven't figured out a way to have
all negative handicaps equal 0 in the same query.
The field looks like this in SQL " (((220-[AVG])*90)/100) AS HDP".
Everything I've tried changes the all the averages not just the negative
ones. I know I could just make another query to change the negative handicaps
to zero but I'd like to avoid any extra steps if possible. Also this is part
of a Make Table query, so if there is any way to go into the field properties
of use the Validation to change all negative numbers to zero, that would work
just as well.
Thanks in advance.

 

[Lynn Trapp]

IIF((((220-[AVG])*90)/100) < 0, 0, (((220-[AVG])*90)/100))

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

In the SQL you can create a criteria for the HDP

Try SQL like
Select (((220-[AVG])*90)/100) AS HDP, IIf([HDP]<0,0,[HDP]) As HDP_Pos From
TableName

 

[Heidi]

Was this a database you created yourself?