Results 1 to 13 of 13

Counting in an Access report

  1. #1
    clrockwell15 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    15

    Counting in an Access report

    I have created a report in ms access 2010 that is based on birthdates of members that were born in the 1920's. This report lists the fields with their name and birthday fields. I created a Text Box (Textbox1) obtain their age by inserting the formula "=(Now()-[Birthdate])/365". In the report footer, I created a Text Box that I want to count those birthdates in my report that are greater than 89. The formula I built is "=Count(IIf([Text1>89],0,0))". However, when I run the report, I get a total count of all records including those less than 90. Any help would be appreciated.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,819
    ??? Now is Date and Time -----I think you want the Date function

    ?? What do you think this is doing? =Count(IIf([Text1>89],0,0))

    Is it textbox1 or text1?

    We don't necessarily need a lot of data. Just enough to try to make it work.
    Perhaps you could post a copy of your database --remove anything confidential, and attach as a zip file.

    Good luck with your project

  3. #3
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,005
    What do you think will be returned from this formula?>>
    Code:
    IIf([Text1>89],0,0)
    Wouldn't
    Code:
    IIf([Text1>89],1,0)
    be better??


    I wouldn't use the Count() function, Sum() might be better.
    Code:
    Sum(IIf([Text1>89],1,0))

    To calculate the age, I use a function
    Code:
    Function GetAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
        'Purpose:   Return the Age in years.
        'Arguments: varDOB = Date Of Birth
        '           varAsOf = the date to calculate the age at, or today if missing.
        'Return:    Whole number of years.
        Dim dtDOB As Date
        Dim dtAsOf As Date
        Dim dtBDay As Date  'Birthday in the year of calculation.
    
        GetAge = Null          'Initialize to Null
    
        'Validate parameters
        If IsDate(varDOB) Then
            dtDOB = varDOB
    
            If Not IsDate(varAsOf) Then  'Date to calculate age from.
                dtAsOf = Date
            Else
                dtAsOf = varAsOf
            End If
    
            If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
                dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
                GetAge = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
            End If
        End If
    End Function
    The usage would be:
    The Text Box (Textbox1) control source would be "=GetAge([Birthdate])".
    or "=GetAge([Birthdate], #1/1/2017#)"
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,619
    I doubt the field name is [Text1>89], probably should be [Text1]>89. If so, remove the [ ] or use them correctly, use Sum, and one of those 0's should be a 1.

    =Sum(IIf([Text1]>89,1,0))

    or

    =Count(IIf([Text1]>89,1,Null))
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    clrockwell15 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    15

    Still looking for an answer

    Okay. what you wrote corrects me as far as how I should use the [] marks. Still didn't answer my question. My report is to give me a total number of seniors that are above 89 years old. The query used to create the report is a list of those birthdays that are in the 1920's. My report give me a total of 15 seniors that are born in the 1920's. I just want to count the number of seniors that are in their 90's. =Count(IIf([Text1]>89,1,Null)) doesn't work. Is there a way I can do this?

    Quote Originally Posted by June7 View Post
    I doubt the field name is [Text1>89], probably should be [Text1]>89. If so, remove the [ ] or use them correctly, use Sum, and one of those 0's should be a 1.

    =Sum(IIf([Text1]>89,1,0))

    or

    =Count(IIf([Text1]>89,1,Null))

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,619
    Ooops, I should have caught that you are referencing a textbox, not a field. Cannot reference controls in aggregate functions, only fields. Options:

    1. construct a field in query with expression to calculate the age then reference that field in the aggregate calc

    2. repeat the age calculation in the aggregate expression

    =Sum(IIf((Now()-[Birthdate])/365>89,1,0))

    or

    =Count(IIf((Now()-[Birthdate])/365>89,1,Null))
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    clrockwell15 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    15

    Still looking for an answer

    Let's try this again. First, I created a report in access 2010 to give me a list of members that were born in the 1920's. That report shows their name and birthdate. Since their birthdate is made up of three fields (BDMth, BDDay, BDYear), I created an unbound textbox that puts them together (=[BDMth]&"/"&[BDDay]&"/"&[BDYear]). I created another textbox that made that a date =Datevalue(Text21]) . I then created another unbound textbox to give me the age of each of these members =DateDiff("yyyy",[Text19],Now()) . Works great. Love it! Of those members born in the 1920's, I want to count those members are age 90 and above (some of them are in their late 80's). So in the report footer, I am trying to count using things such as =Count(IIf([Text21]>89,1,Null)) or =Count(IIf((Now()-[Text21])/365>89,1,Null)). All to no avail. I get a dialog box asking me to type in Text21 and when I hit enter, the count is 0. Can anyone help me on this?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,619
    As I said, aggregate functions cannot reference controls, only fields.

    The options I suggested still relevant.

    1. Do those intermediate calcs in a query and use query as report RecordSource so you have field that can be referenced by aggregate function.

    or

    2. Repeat the calc sequence in the aggregate expression
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    clrockwell15 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    15

    Counting in an Access Report

    Quote Originally Posted by June7 View Post
    As I said, aggregate functions cannot reference controls, only fields.

    The options I suggested still relevant.

    1. Do those intermediate calcs in a query and use query as report RecordSource so you have field that can be referenced by aggregate function.

    or

    2. Repeat the calc sequence in the aggregate expression

    Okay. Got it. Understand it finally. Now, here is my expression: =Count(IIf(((Now()-[Birthdate])/365)>89,0)). I have a list of twenty-two (22) members that were born in the twenties (1920's). What I want the expression to do is to give me those that are 90 and over. The expression in the previous sentence gives me 18. Don't understand that.

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,207
    Maybe try using Sum:

    =Sum(IIf(((Now()-[Birthdate])/365)>89,1,0))

    IIf(expr , truepart , falsepart )
    In your expression if the age is >89, it returns a 1, if not returns a 0. Changed Count to Sum as you don't want to count the zeros records.

  11. #11
    clrockwell15 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    15
    That expression "=Sum(IIf(((Now()-[Birthdate])/365)>89,1,0))" still gives me a count of 18 when it should be 15. Don't know what it is counting. I have a total number of 20 records of those born in the 1920's. There are only 15 that are 90+ years old. The expression you gave me counts to 18. ???

  12. #12
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,207
    Maybe that calculation is not counting correctly if it counts some that are say 89 and 4 months and not 90+, maybe try >=90:

    =Sum(IIf(((Now()-[Birthdate])/365)>= 90,1,0))

    Or can try using DateDiff function:

    =
    Sum(IIf(DateDiff ("yyyy", [Birthdate], Date())>=90,1,0))




  13. #13
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,005
    That expression "=Sum(IIf(((Now()-[Birthdate])/365)>89,1,0))" still gives me a count of 18 when it should be 15. Don't know what it is counting. I have a total number of 20 records of those born in the 1920's. There are only 15 that are 90+ years old. The expression you gave me counts to 18. ???
    Age can be many different numbers. It depends on how you do the calculations and what you expect the age to be.

    Given a birthdate of 9/14/1919, results of the calculations:
    Calculation
    Result
    (Date()-#9/14/1919#)/365 97.9835616438356
    (Date()-#8/14/1919#)/365 98.0684931506849
    Int((Date()-#9/14/1919#)/365) 97
    DateDiff ("yyyy",#9/14/1919#, Date()) 98
    Int((Date()-#8/14/1919#)/365) 98
    2017-1919 98

    So it depends on if you want to take into account the month/day in the calculation.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Counting records in report from a select field
    By LaughingBull in forum Access
    Replies: 5
    Last Post: 08-23-2015, 11:09 AM
  2. Replies: 7
    Last Post: 06-02-2015, 08:36 AM
  3. Group Counting in a Report
    By Huddle in forum Access
    Replies: 21
    Last Post: 11-07-2012, 02:30 PM
  4. Trouble counting items in a report
    By Walt Stypinski in forum Access
    Replies: 2
    Last Post: 06-13-2011, 07:21 PM
  5. Report Help: counting and average
    By alliandrina in forum Reports
    Replies: 0
    Last Post: 03-08-2011, 12:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums