Do While Loop code

[JWhitehead]

I have a menu type form, and on it there is a spot to count how many
outstanding transactions there are. What I am trying to do is get a total
count, how many are 30 days past due, and 60 days past due. I have gotten it
so that it will show the total number of outstanding, but not the 30 & 60
days. If I step through the code, it will calculate, but if I don't step
through, then it will not calculate. Any suggestions?

The code is:
Private Sub Form_Load()
Dim Days30 As Integer
Dim Days60 As Integer
Dim Counting As Integer
Dim Days As Integer

Counting = txtCount

Do While Counting > 0
Days = Now() - DateRequested
If Days >= 60 Then
Days60 = Days60 + 1
ElseIf Days >= 30 And Days < 60 Then
Days30 = Days30 + 1
End If
Counting = Counting - 1
DoCmd.GoToRecord , "", acNext
Loop

txtTotal = "Number of Incomplete Tasks: " & txtCount 'txtTotal and
txtCount are fields on form
txt30Days = Days30 & " are over 30 days old" 'txt30Days is field on form
txt60Days = Days60 & " are over 60 days old" 'txt60Days is field on form
End Sub

 

[JWhitehead]

Sorry, forgot to add, this is in Access 2007. And the results shows as 14
outstanding (txtTotal), 0 over 30 days, and 0 over 60 days. It does show the
text part, but not the value.

 

[JimBurke via AccessMonster.com]

From what I can gather, this form is bound to a table that consists of
transactions, and you merely need to count the number of them as well as the
ones 30 and 60 days past due. Is that the case? If so, you should just do it
through a query. This should work if I'm right about what you're doing

SELECT Count(*) AS MyCount, _
Sum(IIf(Now()-[DateRequested]>=30,1,0)) AS Past30, _
Sum(IIf(Now()-[DateRequested]>=60,1,0)) AS Past60
FROM yourTableName

I would just create a stored query from that SQL.

Then you can either open a recordset on that query and retrieve the values
thru the recordset or else do DLookups:

transCount = DLookup("myCount","thatQueryName")
past30 = DLookup("Past30","thatQueryName")
past60 = DLookup("Past60","thatQueryName")

 

[JimBurke via AccessMonster.com]

After thinking about it, this will include all transactions past 60 days due
in the number for past 30 days. Not sure if you would want that or not. If
not then you would want to change the one for past 30 to be

Sum(IIf(Now()-[DateRequested]>=30 and Now()-[DateRequested]<=59 ,1,0)) AS
Past30

From what I can gather, this form is bound to a table that consists of
transactions, and you merely need to count the number of them as well as the
ones 30 and 60 days past due. Is that the case? If so, you should just do it
through a query. This should work if I'm right about what you're doing

SELECT Count(*) AS MyCount, _
Sum(IIf(Now()-[DateRequested]>=30,1,0)) AS Past30, _
Sum(IIf(Now()-[DateRequested]>=60,1,0)) AS Past60
FROM yourTableName

I would just create a stored query from that SQL.

Then you can either open a recordset on that query and retrieve the values
thru the recordset or else do DLookups:

transCount = DLookup("myCount","thatQueryName")
past30 = DLookup("Past30","thatQueryName")
past60 = DLookup("Past60","thatQueryName")

I have a menu type form, and on it there is a spot to count how many
outstanding transactions there are. What I am trying to do is get a total

[quoted text clipped - 28 lines]

txt60Days = Days60 & " are over 60 days old" 'txt60Days is field on form
End Sub

 

[JWhitehead]

Thank you for your help. It has been sooo long since I've had to do anything
in SQL statement! It worked exactly like I wanted it to.

Thank you again for your help Jim Burke

After thinking about it, this will include all transactions past 60 days due
in the number for past 30 days. Not sure if you would want that or not. If
not then you would want to change the one for past 30 to be

Sum(IIf(Now()-[DateRequested]>=30 and Now()-[DateRequested]<=59 ,1,0)) AS
Past30

From what I can gather, this form is bound to a table that consists of
transactions, and you merely need to count the number of them as well as the
ones 30 and 60 days past due. Is that the case? If so, you should just do it
through a query. This should work if I'm right about what you're doing

SELECT Count(*) AS MyCount, _
Sum(IIf(Now()-[DateRequested]>=30,1,0)) AS Past30, _
Sum(IIf(Now()-[DateRequested]>=60,1,0)) AS Past60
FROM yourTableName

I would just create a stored query from that SQL.

Then you can either open a recordset on that query and retrieve the values
thru the recordset or else do DLookups:

transCount = DLookup("myCount","thatQueryName")
past30 = DLookup("Past30","thatQueryName")
past60 = DLookup("Past60","thatQueryName")

I have a menu type form, and on it there is a spot to count how many
outstanding transactions there are. What I am trying to do is get a total

[quoted text clipped - 28 lines]

txt60Days = Days60 & " are over 60 days old" 'txt60Days is field on form
End Sub