"If Then" in a query? (1 Viewer)

[legendv]

I'm trying to multiply a user defined length times a user defined width. That product is then defined a number which is to be added to other user defined numbers. For instance,
user entered 5 for length and 5 for width thus query results size of 25. Scale is:
<= 25 add 1
>25 and <30 add 2
>30 and <40 add 3
>40 add 4
This 1,2,3, or 4 then gets added to various other numbers that give me no problems. I guess my question is, can this be written in a query?

 

[David R]

Use IIF in a query situation. Even though it's only a true/false statement, you can nest them:
IIF(Condition,True,(IIF(Condition2,True,(IIF...,False))))

 

[legendv]

IIF([isize]<=0,0+ [iexudate] + [itissue] ,(IIF([isize]>0 and <.3,1+ [iexudate] + [itissue] ,(IIF[isize]=>.3,10+ [iexudate] + [itissue] ))))

David maybe I can explain this better in text.

if isize is less than or equal to zero then I want to add the value of zero + userdefined entry of the iexudate and itissue.
There's an entire scale I'm implementing and yes the other fields are integers.
It says I have a syntax error, will you help me some more?

 

[legendv]

Thanks, I got it now.

 

[David R]

You might find this process faster if you define a public function. I haven't tested it, but you could certainly build both queries and run them to see which works better in a production environment.

Public Function AdjustScale(val As Integer)
Dim newval As Byte
   Select Case val
   Case Is < 25
      newval = 1
   Case 25 To 29
      newval = 2
   Case 30 To 39
      newval = 3
   Case Is > 39
      newval = 4
   End Select

   AdjustScale = newval
End Function

 

[legendv]

Sorry about the new thread, but I didn't think you would come back to this one. Anyway, I guess I might have to. The iif statement apparently only holds 9 or 10 conditions, and says its too complex now. I was wondering, since I really like the data in the query where I know a little about manipulating it. But I'm not sure how to programaticaly define a variable and have it go into and add to other data in the query to produce a result that stays in the query. Is there a way to keep it in the query with 11 conditions listed?

 

[David R]

Queries don't have code modules attached to them like forms and reports do. You have to call a stand-alone module if you're going to do more than elementary work with variables (i.e. parameters).

What I gave you should work. If you run into further problems post back what you're trying to use in the code module and someone can help you spot-check it.

 

[legendv]

Thanks!