Stored Procedure from Access Module

[FrankPV]

I'm running an Access 2000 front end to an SQL Server 2000 Database. I developed a Data Access Page to allow display of some price data for salespeople over our company intranet. My problem is the Access query data source for the new Data Access Page will not work. The query relied on several functions that don't work in the DAP. I've converted the query to a view but I don't understand how to do conditional processing inside the view or how to use a stored procedure to get the desired result. The SQL code for the view is as follows:

SELECT TOP 100 PERCENT dbo.Invoice_Items.QUANTITY, dbo.Invoice_Items.DESCRIPTIO, dbo.Invoice_Items.FLONO, dbo.Invoice_Items.PRICE,
dbo.Invoice_Items.BOXCHG, dbo.Invoice_Items.DUTY, dbo.Invoice_Items.COLOR, dbo.Invoice_Items.VARIETY, dbo.Invoice_Items.OTHCHGS,
dbo.Invoice_Items.BOXNO, dbo.Invoice_Items.FREIGHT, dbo.Flower_Names_and_Prices.STEMPERBCH, dbo.Invoices.RECDATE, dbo.Stores.ArrMkup,
dbo.Stores.LooseMkup, dbo.Stores.[Store Number], dbo.Flower_Names_and_Prices.DUMPPCT, dbo.Flower_Names_and_Prices.minarrprice,
dbo.Flower_Names_and_Prices.MINPRICE, CONVERT(money,
((dbo.Invoice_Items.PRICE * dbo.Invoice_Items.QUANTITY + dbo.Invoice_Items.BOXNO * dbo.Invoice_Items.BOXCHG + dbo.Invoice_Items.FREIGHT + dbo.Invoice_Items.OTHCHGS)
/ dbo.Invoice_Items.QUANTITY / dbo.Flower_Names_and_Prices.STEMPERBCH * dbo.Stores.LooseMkup)
* (1 + dbo.Flower_Names_and_Prices.DUMPPCT)) AS LooseCutPrice, ROUND(CONVERT(money,
((dbo.Invoice_Items.PRICE * dbo.Invoice_Items.QUANTITY + dbo.Invoice_Items.BOXNO * dbo.Invoice_Items.BOXCHG + dbo.Invoice_Items.FREIGHT + dbo.Invoice_Items.OTHCHGS)
/ dbo.Invoice_Items.QUANTITY / dbo.Flower_Names_and_Prices.STEMPERBCH * dbo.Stores.ArrMkup)
* (1 + dbo.Flower_Names_and_Prices.DUMPPCT)), 1) AS Arrprice
FROM dbo.Stores INNER JOIN
dbo.Flower_Names_and_Prices INNER JOIN
dbo.Invoice_Items ON dbo.Flower_Names_and_Prices.FLONO = dbo.Invoice_Items.FLONO INNER JOIN
dbo.Invoices ON dbo.Invoice_Items.INVCNUM = dbo.Invoices.INVCNUM ON dbo.Stores.[Store Number] = dbo.Invoices.storenum
WHERE (dbo.Invoices.RECDATE BETWEEN { fn NOW() } - 7 AND { fn NOW() })
ORDER BY dbo.Invoices.RECDATE, dbo.Invoice_Items.DESCRIPTIO, dbo.Invoice_Items.VARIETY


I would like to develop a field that would display the larger of minprice or the result from the calculation and then display that field on my Data Access Page. In the printed report I am able to do this using the IIF function in Access. I would also like to use a rounding function I developed based on the value of the calculation. Should I used a user defined function for this? If so, how would I execute this inside the stored procedure to return the desired result?

Thank you in advance for your help. Once I get the calculations done I can put this into production.

 

[SQLSister]

look at the Case function to replace IIF.

Rounding function is goos as a user defined function.

Here's an example where we call a function (LatLonDistance) is some of our code:
Insert Into #tmpHotelsDistance
(HotelID,
[Name],
City,
MarketingLevel,
Distance)
Select HotelID, Name, City, MarketingLevel, dbo.LatLonDistance(@Latitude, @Longitude, @LatHotel, @LonHotel) as Distance
from #tmpHotels

 

[FrankPV]

Thank you. I will look into the CASE function. Appreciate your advice.