Re: Do While not Statement

From: Ray at <%=sLocation%> [MVP] (%=sLocation%)
Date: 05/05/04 

Date: Wed, 5 May 2004 13:53:06 -0400

How about just doing a count query or queries?

intMale = odoCon.Execute("SELECT COUNT(gender) FROM tblUsers WHERE
gender='male'").Fields.Item(0).Value
intFemale = odoCon.Execute("SELECT COUNT(gender) FROM tblUsers WHERE
gender='female'").Fields.Item(0).Value
intBasePaper = odoCon.Execute("SELECT COUNT(referral) FROM tblUsers WHERE
referral='Base Paper'").Fields.Item(0).Value

(Some would argue against the methods above, however.)

For sexes, you could also do "SELECT gender,COUNT(gender) FROM tblUsers
GROUP BY gender ORDER BY gender" to gram male and female at once.

Ray at work

"Ben" <Ben.DunlapHAHA@langley.af.mil> wrote in message
news:%23jImA9rMEHA.3572@tk2msftngp13.phx.gbl...
> I have two 'Do While Not' statements, that are getting information from
> the same recordset. If I comment out the first one I can get the results
> for the second one, and vice-versa. Why is this happening? Is it because
> it's already at the EOF? If so how do I get it back to the BOF for the
> 2nd 'Do While Not' statement?
>
> '----------------------------------------
> 'Create an ADO recordset object
> Set rs_Report = Server.CreateObject("ADODB.Recordset")
>
> 'Initialise the strSQL variable with an SQL statement to query the
> database
> strSQL = "SELECT gender, referral, youthmember, age FROM tblUsers WHERE
> base = '" + Replace(chosenBase, "'", "''") + "'"
>
> 'Open the recordset with the SQL query
> rs_Report.Open strSQL, adoCon
>
> '-----Gets the total number of male and female users
> intMale = 0
> intFemale = 0
> Do While NOT rs_Report.EOF
> If rs_Report("gender") = "male" then
> intMale = intMale + 1
> Else
> intFemale = intFemale + 1
> End If
> rs_Report.MoveNext()
> Loop
> '-----Figures out how they heard about the program
>
> Do While NOT rs_Report.EOF
> If rs_Report("referral") = "Base Paper" then
> intBasePaper = intBasePaper + 1
> End IF
> rs_Report.MoveNext()
> Loop
> ----------------------------------------------
> --Sample Output--
> Male = 11
> Female = 4
>
> Base Paper =__ "nothing is returned here"
>
> ----------------------------------------------
> Like I said before If I comment out the first do while not statement I
> can get the answers for the second one. If I don't it doesn't even get
> inside of the second do while not statement.
>
> I know I can fix it by just having a seperate SQL statement to the
> database and get my information seperately, but it seems like a waste of
> time. Thanks for any help.
>
> -=Ben
> -=To email me take out the joke.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

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 Loop code
    ... in SQL statement! ... I would just create a stored query from that SQL. ... Then you can either open a recordset on that query and retrieve the values ... outstanding transactions there are. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... Tools - references - tick Microsoft Outlook object library. ... in the criteria cell in the graphical query builder. ... the same filtered recordset every time. ... normal form to allow the normal filtering process and viewing of the ...
    (microsoft.public.access.formscoding)