Re: Do While Loop code

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




"JimBurke via AccessMonster.com" wrote:

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: Viewing the contents of two tables using ASP and MS Access
    ... Number of fields in a recordset 255 ... Number of characters in a cell in the query design grid 1,024 ... Number of characters in an SQL statement approximately 64,000 ...
    (microsoft.public.inetserver.asp.db)
  • Re: Opening a query in MS Access with parameters using VB.net
    ... datasource to a SQL statement. ... using the stored query as the datasource. ... Dim rs As Recordset ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Bounded Columns vs Recordset
    ... This involves code to get and then create the Row Source List for the ... Recordset will be based on a query, so why not just use the query ... Combobox instead of having a SQL statement in the RowSource Property. ...
    (microsoft.public.access.externaldata)
  • Re: Do While not Statement
    ... How about just doing a count query or queries? ... intBasePaper = odoCon.ExecuteFROM tblUsers WHERE ... > the same recordset. ... > 'Initialise the strSQL variable with an SQL statement to query the ...
    (microsoft.public.inetserver.asp.general)
  • Re: Error using " in .open with ADODB.Recordset
    ... recordset but I'm getting hung up on the SQL statement. ... query that has a string as a condition. ... Dim mrk As String ...
    (microsoft.public.data.ado)