loop form for report

[jasn_78]

Hey guys i have a form called frmCOMPARISON (Screenshot attached for a better idea)

this form and its subform contain a series of comboboxes and txtboxes that i want to use as filters on a query to compare data from various periods and other options.

what i am wondering is how do i get it so that i can use the same form over and over again for various periods but record in the same query e.g. i will have a query called qryCOMPARISON where i want to store both a day number which is using DateDiff(startdate,date in htrxtbl) and a seperate column for each periiod containing the amount for that day.

what i have below is my logic of how to do this but im not sure how i keep P as the new value each time i re-open my form

any suggestion on what i am trying to do would be great

Dim P as integer

‘set p to be 1 at the opening of the form for the first time
P = 1

‘sets the caption of the form to “SALES COMPARISON PERIOD (and the number of the period)
frmComparison Caption = "SALES COMPARISON PERIOD" & P

' if user press next period button do this

If cmdNExt Selected

start at period(P)
‘DayNo is the datediff of the startdate and that day
‘Period(P)Amt is the sum of total sales for that day applying all filters off frmComparison
qryCOMPARISON= (SELECT DayNo:DateDiff() "Period"& P &"Amt":(Sum HTRX_VALUE) FROM HTRXTBL WHERE "frmcomparison filters applied") 


‘adds 1 for the next period if next selected
add 1 to P

close form
clear filters(dept etc)
re openform

' repeat until one of the below chosen

'if command button exit selected clear all queries and filters
if cmdexit selected clear all

'if cmdprint selected view excel graph
if print selected export to excel for "qrySALESCOMPARISON"

 

[boblarson]

Don't know if this sample will help but you can see:

http://downloads.btabdevelopment.com/Samples/dates/ReportAndForm.zip

 

[jasn_78]

bob thanks but that isnt really what i am after am more trying to figure a way to create a query of values which will be Day=Datediff(fromdate,trxdate),Value(i) where i is the amount of times that form has been run for that report.

So say i had run the report 3 times with each date range being 30 days i would have 30 rows of data with Day 0 to 29 then i would also have another 3 columns called value(0), value(1), value(2) which for each day value would contain a sum value for that day in the record.

eg.
day,value0,value1,value2
0,a,b,c
1,d,e,f
2,g,h,i
3,j,k,l

hope this make sense