access 2000/msde views problem

[pflangan]

I'm just upgrading our local database to access 2000 and MSDE. The problem I'm having, is that views don't seem to be all they're made out to be.

I had this query in access that calculated an amount due, based on the amount of hours a person has worked. There are three different rates; first hour, second hour, all other hours over 2 hours.

Using queries in Access I was able to call a function I had coded in a module to perform a SELECT CASE on the amount of hours worked and return the amount due to my query.

using views, I cannot do this. I cannot use compound statements (IF ELSE or CASE .. WHEN).

does anyone know how to get around this? I know I can sit the whole thing into a stored procedure, but then I need to use VBA and ADO to process the output.


although it's not really necessary here's the old query and VBA that did the job.

Query
------
SELECT jobID, getPayRate([hours]) AS amountDue FROM JobCard

VBA
---------
public Function getPayRate(iHours as integer) as currency
SELECT CASE iHours
CASE 1
getPayRate = 65
CASE 2
getPayRate = 100
CASE ELSE
getPayRate = 100 + ((iHours - 2) * 25)
END SELECT

end Function
---------

 

[Kubilus1]

Try creating a SQL string like so:

SQLcode = "Select jobID, " & getPayRate([hours]) & "As amountDue FROM JobCard"

and send the pass-through query by VBA. Keep your old function.

Check out:

thread701-91679 a post on sending a parameterized query by VBA. It's for Access 97 and SQL 7, but you might be able to apply it in your case.

 

[pflangan]

Thanks for the effort.

I've been wreckin my head most of yesterday, trying to figure it out, and after many coffees, I think I've come to a solution.

I've built the query using CASE WHEN etc., and then pasted the SQL into the control source of my form. tada.