Minimum between Calc and set #

[sellis]

I am running a query and trying to figure out how to set up a function to get either the results of a calculation or 500 whichever is smaller. Any help would be greatly appreciated.

 

[boblarson]

Something like this but you didn't give much information as to what you are calculating and where.

Function CalcTest() As Long
Dim Calc As Long
   Calc = (x+y) * 2
      If Calc < 500 Then
         CalcTest = Calc
      Else
         CalcTest = 500
      End If
End Function

 

[sellis]

Ok. I get what you said about more info. Sorry. What I have currently is Access 2010 and my query is currently ROUND(Rate*0.6667*40,2) where rate is pulling from a table. I need the result of that query to be less than the 500. I have no idea how to enter the information you gave me in my version of Access. Thanks for your quick answer though.

 

[boblarson]

No function needed if this is in a query. Just use this as a new field:

NewFieldNameHere:IIf(ROUND([Rate]*0.6667*40,2) < 500, ROUND([Rate]*0.6667*40,2), 500)

 

[sellis]

That was sooo awesome. Took me a minute to get the syntax right and make it work for me, but it works perfectly. Thanks soooooo much.