Running balance with prior/fwd date

[princessdmb]

Hello,

I'm having issues with my database, I want to run a balance for my subquery for which it will not calculate the Disregard prior 4/1/2000, and calculate it 4/1/2000 forward.

This is what I currently have

SELECT [UAP Balance Tbl].CaseID, [UAP Balance Tbl].[Month/Year], [UAP Balance Tbl].Grant, [UAP Balance Tbl].Disregard, [UAP Balance Tbl].[Pass-On], [UAP Balance Tbl].Excess, [UAP Balance Tbl].[Current Recoup], [UAP Balance Tbl].[Arrears Recoup], [UAP Balance Tbl].Other, [Grant]-[DisrDate]-[Current Recoup]-[Arrears Recoup]+[Other] AS UAP, DSum("UAP","UAP Bal qry","[Month/Year]<=" & [Month/Year]) AS [UAP Bal], IIf([Month/Year]>=#4/1/2000#,[Disregard],0) AS DisrDate
FROM [UAP Balance Tbl];

Its under UAP Bal qry, the BLUE is my issue.

You will see on the UAP DataEntry form, that the CaseID are link.

Your help will be much appreciated!!!!

-Denise ♥

 

[Taruz]

hi..

that part of use in this way..:

UAP Bal: DSum("UAP";"UAP Bal qry";"cdbl([Month/Year])<=" & cdbl([Month/Year]))

.

 

[princessdmb]

Hello,

Thank you so much Taruz, the balance is now showing (Yay!!!) .... however its not showing correctly based on the CaseID. But were a step ahead now

CaseID#1 start month is 03/2010 and its continues its balacing from CaseID# 2 start month 02/2000.

I need for each CaseID to show its own balance. Please help ... Im going cucu here :S

 

[Taruz]

Hi Denis..

UAP Bal: DSum("UAP";"UAP Bal qry";"cdbl([Month/Year])<=" & CDbl([Month/Year]) & " and CaseID=" & [CaseID])

You can add this to the relevant area..

Greetings..

 

[princessdmb]

Omg!!!! Yay!!!! It works ... Im so happyyyyyy :d

thank you soooo much for all your help!!!!!

you are truly an awesome person taruz!!!! ♥