I need to know at list if this is possible

[senseidan]

I need to view a report between two dates and using parameters in my query does not work because I have a running sum.
Ex.: start value=100; total for day1 =30; end of day1 =100+30; total for day2 = 17; end of day2 =130+17; total for day3=2; end of day3=147+2 etc. Obviously, end of day3 is correct only if I start from the beginning.
I have tried until now to "filter" only the dates in the report (where condition) but I begin to have doubts that this is the path to take .
I feel like there must be another solution but I can't see it . Can someone point me in the right direction?

 

[theDBguy]

Hi. Perhaps you could add an unbound control to your report to calculate the beginning balance for your date range. You can then use this value for each day included in your report.

 

[senseidan]

Hello theDBguy. I have such control, problem is that still I need all previous data to calculate. Maybe if I write every day result in a table and use it from there… that could be a solution. I hope however that I can do what I need in a more straightforward way.

 

[theDBguy]

Right. I was thinking the unbound textbox would contain a calculation from all previous records; hence, the idea to call it "beginning balance." Have you tried using DSum()?

 

[JHB]

Show how you want the report and some example data.

 

[senseidan]

for JHB: I will describe the situation as clear as I can. I have a Table with 3 columns: Date, Income, Payments. I have a report for every day which show the sum of Payments for that day, the sum of Income for that day, the sum of Payments until that day, the sum of Income until that day and a total which consists in sumOfIncomeUD-sumOfPaymentsUD(UD=until that day). This is what I already have. Now, I need to view this report between 2 dates, similar to a query with 2 parameters: startDate, endDate. If I use a query, it will cut all my payments/income before of startDate and, consequently, will ruin sumOfIncome/PaymentsUD.
ex:
day1: income=1,2,3 ;payment:1
day1: sumOfIncome=6 ;sumOfPayment:1
day1: sumOfIncomeUD=6 ;sumOfPaymentUD:1
TOTAL day1=5

day2: income=4,5,6 ;payment:3,2
day2: sumOfIncome=15 ;sumOfPayment:5
day2: sumOfIncomeUD=21 ;sumOfPaymentUD:6
TOTAL day2=15

day3: income=10,20,30 ;payment:15
day3: sumOfIncome=60 ;sumOfPayment:15
day3: sumOfIncomeUD=81 sumOfPaymentUD:21
TOTAL day3=60

PROBLEM- if I want to view only day 2 and 3:

day2: sumOfIncomeUD=15 ;sumOfPaymentUD:5
TOTAL day2=10

day3: sumOfIncomeUD=75 sumOfPaymentUD:20
TOTAL day3=55

 

[senseidan]

to theDBguy: Sure thing, DSum() was my first choice but, if I remember well, I found that is somehow slow even for not so big sets of data (2000 -3000records) Anyway, my report is working very well as it is but what is showing is correct only if it runs from the beginning(day1).:banghead:

 

[JHB]

If you've a query that gives a correct result, why do you not pick out the days (date) you want from that query?