SQL Newbie Troubles

[WickedElm]

Hey there...I have written an SQL statement in Access '97 in order to decide wether a figure is "Low", "Med", or "High"...Now I need to set up a way to display the number of Lows, Meds, and Highs...I am not sure of wether you can declare variables in sql or how to go about doing this...the coding that i have now is as follows...the "Hours Weight:" portion sets up the name of the field where data is put in as noted by the code...

Hours Weight: IIf([Actual Hours]<41,&quot;Low&quot;,IIf([Actual Hours]<240,&quot;Medium&quot;,&quot;High&quot)

I want to set this up so that separate variables are passed the respective number of Lows, Meds, highs so that that number can be displayed elsewhere in the query....if anyone could help me out with either the syntax of declaring the variables or have any suggestions to help my problem would be great....thanks!

another question that may help is how do you call a VBA module in this form...because then I could easily call that function to take care of eveything i need....I could change the code above to be

Hours Weight: [call to VBA]

where that function takes care of everything because i am much better with VBA

 

[scriverb]

Well, you are doing quite well with what you have started.

Calling a function is real easy. Create a Function in a database Class module called ReturnLowMedHigh which is where you can use VBA code to return a value. Make the call in SQL like this:
Hours Weight: ReturnLowMedHigh()

But, you don't necessarily have to do that. You IIF statement looks just fine. If you want to use variables to trigger the expressions do the following:

Create global variables and Functions in the database class module:
Global vLow as long, vMed as long
Function Low()
Low = vLow
End Function
Function Med()
Med = vMed
End Function

Set the values somewhere in your code:
vLow = 41
vMed = 240

SQL code using Functions:
Hours Weight: IIf([Actual Hours]<Low(),&quot;Low&quot;,IIf([Actual Hours]<Med(),&quot;Medium&quot;,&quot;High&quot)

Hope this is sufficient to get you started on the right track.
Bob Scriver