Select case in SQL statement 2

[desmur]

I need to reference a select case statement in my sql query in access. I used a select case in a public procedure. It compares hours worked to percentage paid. Once this is determined it then compares this rate to the rate currently paid and takes the higher rate. I would like to have this all done in the query because I can't get the totalling to work in a report. How do I refer to the select case that is in my public procedure in a query.

 

[r937]

in access, use the IIF function instead of CASE


rudy

 

[SgtJarrow]

r937,

I have to disagree...IIFs in queries are really only good for a two-statement function...anything else should call a custom function....

desmur,

put somthing like the following in your query:

MyFunction: NameOfFunctionHere([nameoffieldhere])

For example...if you want taxvalue function to return a certain value based on tax field use:

MyTax: TaxValue([Tax])

This will evaluate the field for each record using your function.... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[desmur]

Thanks for your help but when I add newrate:findrate([sumofts_hrs])to my query I get an error that says syntex error in query expression. What did I do wrong?

 

[desmur]

This is my public funtion
Public Function findrate(sumOfTs_hrs As Integer, newrate As Integer) As Integer

Select Case sumOfTs_hrs

Case Is > 8000
newrate = 0.7

Case 6000 To 7999
newrate = 0.68

Case 4000 To 5999
newrate = 0.66

Case 2000 To 3999
newrate = 0.64

Case 1000 To 1999
newrate = 0.62

Case Else
newrate = 0.6

End Select

End Function

 

[desmur]

I also put newrate: findrate([sumofts_hrs])directly into the query and got the following error
Wrong number of arguments used with function in query expression findrate([sumofts_hrs])

 

[tlbroadbent]

The function returns the value. You don't need to declare the newrate variable or paramter. The call fails because the funcion is defined with two parameters and you pass only one. Modify the function as follows.

Public Function findrate(sumOfTs_hrs As Integer) As Integer

Select Case sumOfTs_hrs

Case Is > 8000
findrate = 0.7

Case 6000 To 7999
findrate = 0.68

Case 4000 To 5999
findrate = 0.66

Case 2000 To 3999
findrate = 0.64

Case 1000 To 1999
findrate = 0.62

Case Else
findrate = 0.6

End Select
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[cmmrfrds]

Access also has the switch funtion. Here is the syntax in a select statement - example.

SELECT tblSales.Item, tblSales.Vendor, tblSales.DateSold, switch(item="xxx",.25,item="yyy",.75,item="zzz",.55) AS rate
FROM tblSales;

 

[desmur]

Thank you so much tlbroadbent that has put me on the right track