How to create a table, choose between percentage rates based on Date range

[debq]

I have a make table query that applies a pass fail percent of 40% (0.395) to audits. One of the tables used in the query creates the percent to apply based on various criteria. I now need to apply

the 40% (0.395) rate to audits less than or equal to =< 08/31/2012 and a 30% (0.295) greater than or equal => 09/01/2012. In the future the rate will drop to 20% (0.195). So I need to be flexible

in my new method.

I want to create a VBA Module to create this table and based on the date criteria choose the appropriate rate.

The current table query SQL is:

SELECT Q09d1_amount_summary.[Audit Review Number], IIf([Q09d1_amount_summary]![SumOfBilled_Amount]=0,0,[Q09d1_amount_summary]![SumOfVariance]/[Q09d1_amount_summary]![SumOfBilled_Amount]) AS 

[Percent], IIf([Percent]>0.395 Or [Percent]<-0.395,"Yes","No") AS [Follow-up], Q09b_Total_Cases.[Total Cases], Q09c_Cases_Reviewed.[Cases Reviewed], [Total Cases]-[Cases Reviewed] AS Difference, 

Q09d1_amount_summary.Ready, Q09d1_amount_summary.Audit_Type, IIf([Q09d1_amount_summary]![SumOfOld_Billed_amount]=0,0,[Q09d1_amount_summary]![SumOfOld_Varience]/[Q09d1_amount_summary]!

[SumOfOld_Billed_amount]) AS Old_Percent
FROM (Q09d1_amount_summary INNER JOIN Q09b_Total_Cases ON Q09d1_amount_summary.[Audit Review Number] = Q09b_Total_Cases.[Audit Review Number]) INNER JOIN Q09c_Cases_Reviewed ON 

Q09d1_amount_summary.[Audit Review Number] = Q09c_Cases_Reviewed.[Audit Review Number]
WHERE (((Q09d1_amount_summary.Ready)="Yes"));

I am new to VBA and would appreciate any help and guidance provided.

 

[SkipVought]

hi,

What application. If MS Access, then try posing in forum705.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[debq]

Thanks so much!! Will do..