Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Need help counting records

    Hi,

    I need some help counting the number of records in a field, but I'm not sure where to go after I've created the recordset.

    What I have already is:-

    Code:
    Dim db1 As Database
    Dim rst1 As DAO.Recordset
    Dim sSQL1 As String
    
    Set db1 = CurrentDb()
    sSQL1 = " SELECT DISTINCT CourseCode, Answer" & _
            " FROM dbo_tblEvaluation" & _
            " WHERE CourseCode ='" & [Forms]![frmShort_Courses]![frmEvaluation].[Form].[CourseCode] & "'"
    
    
    Debug.Print sSQL1
    Set rst1 = db.OpenRecordset(sSQL1)
    For the field Answer I will have one of the following four values:

    Poor
    Average
    Good
    Excellent
    How do I go about counting the number of records with the value of "Good" in the Answer field?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why not do it in the SQL?

    Code:
    sSQL1 = " SELECT Answer, Count(*) AS TheNumber" & _
            " FROM dbo_tblEvaluation" & _
            " WHERE CourseCode ='" & [Forms]![frmShort_Courses]![frmEvaluation].[Form].[CourseCode] & "'" & _
            " GROUP BY Answer"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Fantastic, just what I need, didn't know you could do that.

    I'm trying to produce a report that creates a percentage for each answer value, so need all four answer values to be counted.

    At the moment it brings back a count for all four answers, which is what I need it to do anyway, but how would I go about just getting a count for one of the answers like "Good".

    Also does anyone know how to assign a value to an unbound textbox using the format 'Percentage', because it doesn't seem to have a value property.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by KevCB226
    At the moment it brings back a count for all four answers, which is what I need it to do anyway, but how would I go about just getting a count for one of the answers like "Good".
    ....where clause?....

    Quote Originally Posted by KevCB226
    Also does anyone know how to assign a value to an unbound textbox using the format 'Percentage', because it doesn't seem to have a value property.
    If you aren't using the value for anything you could just stick it into a string (e.g. Me.Textbox = cstr(MyNumber) & "%")
    Otherwise I think you have to bugger about with the format proprty (I tend not to use the % format so can't help you beyond that - check Help).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    ....where clause?....


    If you aren't using the value for anything you could just stick it into a string (e.g. Me.Textbox = cstr(MyNumber) & "%")
    Otherwise I think you have to bugger about with the format proprty (I tend not to use the % format so can't help you beyond that - check Help).
    Textboxes in reports do not have the Value porerty exposed ... They would need to set the assignment within the Detail Section of the report ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    ....where clause?....


    If you aren't using the value for anything you could just stick it into a string (e.g. Me.Textbox = cstr(MyNumber) & "%")
    Otherwise I think you have to bugger about with the format proprty (I tend not to use the % format so can't help you beyond that - check Help).
    Textboxes in reports do not have the Value property exposed ... They would need to set the assignment within the Detail Section of the report ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •