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,482
    Provided Answers: 11
    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

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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