I have developed a database in Access but am now having a problem moving it over to MSSQL. I'm experienced in Access and new to SQL. The problem is with Functions. In my access database I have a table called Mortgage_Rates. What I have done is create a qurey that will generate the APR based upon the rate.

Here is my SQL Statement from Access.

SELECT

Product,

Purch_Ref_Rate,

-PMT(Purch_Ref_Rate/12,180,100000,0,0) AS Payment,

Rate(180,Payment,-(100000-2500),0)*12 AS APR,

FROM

Mortgage_Rates;

This works great in Access however when I create the View in SQL I get an Error saying that the PMT Function is not a valid function. I did a little digging in the Help files and found it was valid in something called SQL Server Analysis Services. I installed it and took a look at it. I am not sure if this is what I was looking for.

So the question is,

Do anyone know how to make my (Access) SQL Statement work in MSSQL(2000 pro). Or Do I have to use this Analysis Services thing. Or am I completely missing the mark altogether.

This gives the ability to truncate the results to any number of digits accurately

(there are values that can't be rounded properly using float datatype)

Also changed 0.05 to 0.001 for more precision

Hilaire

create function rate (@NumberOfMonths int, @Payment decimal(15,7), @Amount decimal(15,7))

returns decimal(15,6)

as

begin

declare @Payment_calc decimal(15,7), @rate_max decimal(15,7), @rate_min decimal(15,7), @rate decimal(15,7), @iter_cnt int

set @rate_max=0.2

set @rate=0.1

set @rate_min=0.0

set @iter_cnt=0

set @Payment_calc = (@amount*@rate/(1-power(1+

while ((abs(@Payment-@Payment_ca

begin

if (@Payment>@Payment_calc ) set @rate_min=@rate else set @rate_max = @rate

set @rate = (@rate_min + @rate_max)/2.0

set @Payment_calc = (@amount*@rate/(1-power(1+

set @iter_cnt = @iter_cnt + 1

end

return round(@rate, 6)

end