Variables in SQL? (1 Viewer)

[lewando_bria]

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

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

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

 

[The_Doc_Man]

OK, I'll mention the VBA solution since you indicate that would be an acceptable option.

In any general module, declare a new public function with whatever inputs you need. Have it return the datatype you need it to return.

Public Function CheckCase(loValue as Long) as String

Dim stAnswer as String

select case loValue
case 0 to 99
stAnswer = "Low"
case 100 to 199
stAnswer = "Med"
case >200
stAnswer = "High"
case else
stAnswer = "Funky"
end select

CheckCase = stAnswer

end function

Compile this code. Now you can use this function in a query as

=CheckCase([myfield])

You might wish to include some other error checking such as what to do if the sucker is null. But this is all that is required for your VBA function. Remember: Gotta be a PUBLIC function, gotta return the data you need as a consistent data type.

P.S. once you have the function, you can even do queries that do a GroupBy on it. That's why the "consistent" part...

 

[Pat Hartman]

Select IIf([Actual Hours]<41,"Low",IIf([Actual Hours]<240,"Medium","High")) as HoursWeight, Count(*) as CountWeight
From YourTable
Group by HoursWeight;

 

[lewando_bria]

ok i'm a little confused as to what the count(*) does...does that count the number of total entries...or is it specific to count just each category separately?

 

[Pat Hartman]

It counts each entry separately. You will end up with a list of all the distinct values as follows:

low 45
medium 89
high 14

 

[lewando_bria]

o ok i gotcha...thanks everyone i figured it out!!! ;alfjds;lj