Well, it took a while but I fully understand it all now. Will take the Date into consideration.

After running 10,000 records for the Federal Permits, I have a consistent error (null returned) in the function. But, it shouldn't be too hard to identify and fix. There is a line added to check for all Nulls in 4 columns. In reality, I only had to check for 3 columns because there will never be an "expiration date" by itself. The forms on the front-end application enforce many rules.

CREATE FUNCTION [dbo].[VRE_APD_Trump](

@SubmitDate DATETIME,

@ApprovedDate DATETIME,

@ExpirationDate DATETIME,

@ReturnedDate DATETIME

)

RETURNS VARCHAR(22)

AS

BEGIN

DECLARE @FunctionResult as VARCHAR(22); -- Business Condition returned

--set @timeNow = = SYSDATETIME() -- gets error! why?

set @FunctionResult =

-- Just trying Date 1,2,4 (will evaluate Date 2,3 next)

CASE WHEN @SubmitDate IS Null AND

@ReturnedDate IS Null AND

@ApprovedDate IS NULL THEN 'No Permit'

WHEN COALESCE(NULLIF(@ApprovedDate, '19000101'), NULLIF(@ExpirationDate, '19000101'), NULLIF(@ReturnedDate, '19000101')) IS NULL OR

@SubmitDate > @ApprovedDate AND

@SubmitDate > @ReturnedDate THEN 'Submit'

WHEN @ApprovedDate > ISNULL(@SubmitDate, '19000101') AND

@ApprovedDate > isnull (@ReturnedDate, '19000101') AND

@ExpirationDate > GETDATE() THEN 'Approved'

WHEN @ApprovedDate > ISNULL(@SubmitDate, '19000101') AND

@ExpirationDate < GETDATE() AND

@ApprovedDate >ISNULL(@ReturnedDate, '19000101') THEN 'Approved but Expired'

WHEN @ReturnedDate > ISNULL(@SubmitDate, '19000101') AND

@ReturnedDate > ISNULL(@ApprovedDate, '19000101') THEN 'Return'

END

return @FunctionResult

END;

Just the Fed Permits have three tables. The latest record for each Federal Permit Submitted, Approved, Expired, Returnd show the top (last date for each) using a view:

SELECT *

FROM (SELECT *, RowID = ROW_NUMBER() OVER (PARTITION BY ID_Wells

ORDER BY RE_24DTSub DESC)

FROM vre_apdfedreturn) AS MyDateOrder

WHERE RowID = 1

There are views that each join 3 tables (Submit, Approved, Returned) to obtain the 4 fields.

Each view is the same structure / field name. They are named for Federal Permits, State Permits, and Local Permits.

Each table has the same field name so this function can be used on each type of permit.

The query for the view for a linked table to Access is:

SELECT ID_Wells, Well_Name, WellTypeID, ClassificationID, ID_State, R_OverRideRuleCheck, SubmitDate, ApprovedDate, ExpirationDate, ReturnedDate,

(SELECT dbo.VRE_APD_Trump(dbo.VRE_APD_FED_TOP.SubmitDate, dbo.VRE_APD_FED_TOP.ApprovedDate, dbo.VRE_APD_FED_TOP.ExpirationDate,

dbo.VRE_APD_FED_TOP.ReturnedDate) AS RE_APD_FedResult) AS APD_FedResult

FROM dbo.VRE_APD_FED_TOP

There is a lot of processing going on a dedicated SQL Server. The 10,000 records now takes around 1 second (or less) for Federal.

The Access record-set process for this part was about 43 seconds. Now, I can delete a lot of code and temp tables.

In normal use, the users only call 1 specific record at a time, not 10,000.

This is a structured Metadata system for a Rule Engine to manage Regulatory Compliance.

The help is a huge break-through for me.

Thanks very much.