Help with Do...Loop command using dates as parameter

[jstaggs39@gmail.com]

I have a form that requires a start date and an end date as input for
the parameters then runs the form which open queries which are designed
to populate certain tables. As it stands now, i can only run the form
one month at a time, so i would enter the first of the month, say
01/01/2002 and the end of the month 01/31/2002 and it would run the
form with january data. If i enter a range of dates from say 01/01/2002
to 06/30/2002, it will only run the query based on the end date being
june 30th and it will ignore the start date. therefore the tables will
only be populated with june data, not january, february, etc. as
expressed by the parameters.
Therefore, what i need to do is create a loop function that will run
the form month by month until all the queries have been run and all the
tables have been populated. I have an idea how to do this, but i can't
seem to get the loop statement correct. it's still only running the
form for only one month based on the end date. is there anyone that can
point me in the right direction? thanks.
Here is pretty much the code that i have thus far:

Dim intDuration As Integer
Dim strInterval As String
intDuration = 1
strInterval = "m"

DoCmd.SetWarnin gs False
Do Until 'this is where i want it to loop the statements month by month
CalcCum "qryCalcCumYTDB M"
DoCmd.OpenQuery "qryYTDReturnsB M", acNormal, acEdit
CalcCum "qryCalcCum QBM"
DoCmd.OpenQuery "qryQReturnsBM" , acNormal, acEdit
CalcCum "qryCalcCum6mBM "
DoCmd.OpenQuery "qry6MReturnsBM ", acNormal, acEdit
CalcCum "qryCalcCum12mB M"
DoCmd.OpenQuery "qry12MReturnsB M", acNormal, acEdit
CalcCum "qryCalcCumCumB M"
DoCmd.OpenQuery "qryCumReturnsB M", acNormal, acEdit
CalcCum "qryCalcCum24mB M"
DoCmd.OpenQuery "qry24MReturnsB M", acNormal, acEdit
Loop
MsgBox "All Calculations Completed"

[jstaggs39@gmail.com]

Re: Help with Do...Loop command using dates as parameter

I have created an expression which, to me, looks like it should work.
but if i enter the dates january to march, instead of running those 3
months thru the queries, it runs the same month (march) 3 times. here
is the code if anyone could give me some suggestions on what to fix.
thanks.

Dim StartDate As Date
Dim strInterval As String
Dim Number As Integer
strInterval = "m"
StartDate = Me.Form!txtFrom
EndDate = Me.Form!txtTo
Number = 1

Do While StartDate <= EndDate
CalcCum "qryCalcCumYTDB M"
DoCmd.OpenQuery "qryYTDReturnsB M", acNormal, acEdit
etc.,
StartDate = DateAdd(strInte rval, Number, StartDate)
Loop
MsgBox "All Calculations Completed"

[pietlinden@hotmail.com]

Re: Help with Do...Loop command using dates as parameter

Something's missing here... Does your query point to your form to grab
this value that you're updating? Of course, there's no way for it to
"see" this value, because it's in CODE and not in a control on your
form... you could create an unbound textbox on your form, slap the
value into it, and then maybe your code would work.

Do While StartDate <= EndDate
CalcCum "qryCalcCumYTDB M" <== is "CalcCum" a FUNCTION you wrote?
'--are you passing your textbox value to this? How?
DoCmd.OpenQuery "qryYTDReturnsB M", acNormal, acEdit

etc.,
StartDate = DateAdd(strInte rval, Number, StartDate)
Loop

if qryYTDReturnsBM has a parameter that points at your open form, then
this should work.

SELECT... MyTable!Price * Forms!MyOpenFor m!txtMyQty As MyExpression
FROM MyTable
WHERE MyTable!Invoice No=Forms!MyOpen Form!InvoiceNo

might work.

[jstaggs39@gmail.com]

Re: Help with Do...Loop command using dates as parameter

thanks for thee suggestion, but the query runs off of a table, not a
form (i don't know if this makes a difference). the value for january
should be picked up from the table and ran thru the queries, then the
value from february, then march, and so on. i figured there was no need
to create an unbound text box because the date parameters have already
been input and the return values (if that is the value you are
referring to entering) can't be input because there are many values for
each date due to the fact that there are many funds. any suggestions?
thanks.

[jstaggs39@gmail.com]

Re: Help with Do...Loop command using dates as parameter

the problems with the SELECT expression is I keep receiving an error
message "Compile Error; Expected:Case". any solutions to fix this in
the code?