Re: Do While Loop code

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

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


--
Jim Burke

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

.

Relevant Pages

  • RE: Display queried records with Null values (null recordcount)
    ... tables related to Assets and Transactions tables in the query, ... since AssetID from tblAssets is a foreign ... The query would return multiple rows per account, ...
    (microsoft.public.access.gettingstarted)
  • RE: Display queried records with Null values (null recordcount)
    ... "...It might work as a single query with an outer join between the assets ... tables related to Assets and Transactions tables in the query, ... The query would return multiple rows per account, ...
    (microsoft.public.access.gettingstarted)
  • Re: User Select Top Value Percentage
    ... I don't think you can use a variable parameter with the TOP option can you? ... your suggestion of a ranking query gave me an idea of how it can be ... FROM Transactions AS T1 ...
    (microsoft.public.access.queries)
  • Re: Design Question - Accounts/Transactions
    ... The indexed view can't return anything about individual transactions. ... reason is that the indexed view contains only one row per account. ... you want an index that will help this query. ... One of the most important thing to get right is the clustered index. ...
    (microsoft.public.sqlserver.programming)
  • Re: User Select Top Value Percentage
    ... "Ken Sheridan" wrote: ... "John Spencer" wrote: ... your suggestion of a ranking query gave me an idea of how it can be ... FROM Transactions AS T1 ...
    (microsoft.public.access.queries)