Need to change Negative numbers to zero in already calculated fiel

  • Thread starter bowlers database
  • Start date
B

bowlers database

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.
 
O

Ofer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top