Results 1 to 9 of 9

Thread: Sum of

  1. #1
    Join Date
    Apr 2013
    Posts
    7

    Unanswered: Sum of

    i have an unbound cell on a form footer which i would like to return the number of True statements in the Form detail "Sport" cell. The data i have is

    Surname (Text)
    Title (Text)
    Sport (yes/no)
    Rest (yes/no)
    Notes (text)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    select count(Sport) as NoSportRows from MyTable
    WHERE sport = true
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2013
    Posts
    7
    Im sorry im a little new to this. where would i attach the code. i am happy with attaching code to event procedures.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    SELECT... is a query
    how you use it is up to you
    you could place it in a query itself
    you could place it in the form/report and run it as docmd. r u n sql('SELECT...')

    another approach, as you are usign a report is to put some VBA behind the report
    declare a variable at the very top of the report
    DIM NoTrueInSport as integer
    dim NoTrueInRest as integer

    in the reports group header
    NoTrueInSport = 0
    NoTrueInRest =0

    in the reports on detail event
    NoTrueInSport = NoTrueInSport + 1

    in the reports group foooter
    say your control is called tbNoSport
    tbNoSport.text = NoTrueInSport
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2013
    Posts
    7
    i have tried attaching it to an unbound cell with an onOpen event. i seem to be doing something wrong as i get a blank return. the code i am trying to use is:

    Private Sub Text28_OnOpen()
    On Error GoTo Err_Text28_OnOpen
    Dim stDocName As String
    stDocName = "Muster List"
    DoCmd****nSQL('SELECT Count(Person.Sport) AS OnBooks From Person WHERE Person.Sport=True')

    Exit_Text28_OnOpen:

    Exit Sub

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    docmd. runsql returns no values, its meant to be used to update a rowe, change the underlying database structure

    instead you need to
    use DLOOKUP
    OR
    EXECUTE
    OR
    open a recordset

    also the code (probably) needs to run in the forms on current event
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2013
    Posts
    7
    I think i need to reduce the number of Count Queries i have as i am trying to attach too much to the bottom of my Report.

    how do i (if i can) combine all of the follwing into one query?

    SELECT Count(Person.Live) AS OnBooks
    FROM Person
    WHERE (((Person.Live)=True));

    SELECT Count(Person.Sport) AS Sport
    FROM Person
    WHERE (((Person.Sport)=True));

    SELECT Count(Person.Muster) AS Muster
    FROM Person
    WHERE (((Person.Muster)=True));

    SELECT Count(Person.Muster) AS NotAttending
    FROM Person
    WHERE Person.Muster=False;

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you think that is a problem
    are you experiencing any performance issues?
    given your current state of knowledge I'd stick with dlookup or dcount

    OR use the VBA code in a report as suggested earlier
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Try a crosstab query

    That will do it one query
    Last edited by myle; 04-11-13 at 06:38. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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