Re: Do While Loop code

Tech-Archive recommends: Fix windows errors by optimizing your registry

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")


JWhitehead wrote:
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

--
Jim Burke

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200907/1

.

Relevant Pages

  • Re: Lookup Date Table for Mising Date
    ... Each day has multiple transactions with the same date and ... Each time the query is run it is for the past 30 days. ... is a missing date. ... Dim strMsg As String ...
    (comp.databases.ms-access)
  • Re: Continuing before values returned from Query?
    ... one account can have many transactions. ... The other is a query that is a summary of all the transactions. ... >>Dim mParm As Parameter ...
    (microsoft.public.access.formscoding)
  • Re: counting records in select query
    ... You can execute a SELECT COUNTwith the appropriate WHERE clause to ... This means it will cost nearly twice as much as executing the query ... For simple queries counting is fine--but it just ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Why is my aggregator query to count records giving me the result x
    ... It is probably because the field you are counting on contains a NULL value. ... Access ignores NULL values when performing aggregate functions. ... instead of counting on a field, just enter a 1 in the Field row of the query ... >I know I'm forgetting something obvious. ...
    (microsoft.public.access.queries)
  • Very Slow character counting in Word 2003
    ... I am writing a character counting application which needs to go ... through hundreds of Word documents, ... awfully slow - like 10-20 characters per second. ... Dim UnderlineState As Boolean ...
    (microsoft.public.word.vba.general)