Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Need a query to obtain a count


    I have a table with eleven (11) Yes/No fields within each record. I need a query that I can use with a DCount statement that will give me a count of the number of those eleven fields that are currently "True".

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Doesn't sound normalized. Within each record you can add them up and use the Abs() function to make the result positive. Over multiple records, apply Sum to that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    If I could use wild cards it would be a simple, like "WHERE "Chg*" = True" AND RecordID = x. All the eleven fields of interest have "Chg" as there field name prefix. (I'm only interested in one record at a time)

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could do that if they were records rather than fields. As is, I think you have to add them individually. You could create a function perhaps.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I seem to recall that maybe there's a way to obtain field names with a query using wildcards? Like "get a list of field names that begin with 'CHG' "? If I could get that I could quickly determine if any of those fields are currently set to TRUE.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not that I'm aware of. Like I said, it's not normalized. It would be simple if they were records.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    What I was trying to do was something like this: (which of course I've found isn't supported in SQL)

    Code:
    DCount("[Chg*]", "QMMT", "MEMID = " & Me.MemID & "And [Chg*] = " & True)
    I'll write a little function that will accomplish the task.
    Thanks,
    Bill

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you get it sorted Bill, or did the snow gum up the works?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I had to bite the bullet and code a list of the fields of interest. Associated with each of those fields is a companion Y/N field prefixed with "Chg", which is referenced in several different contexts, one of which is BackColor highlighting via Conditional Formatting. I wrote a simple function that answers the question have any of the fields of interested changed.

    Code:
    Public Function ChgCount() As Integer
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    '*  From an array of field names, count the number set to TRUE in the current record.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim ChgFlds() As String
    Dim I As Integer
    ChgFlds() = Split("LastName;FirstName;Spouse;Address;City;State;Zip;Homephone;Cell;Fax;EMA", ";")
    
    ChgCount = 0
    For I = 0 To UBound(ChgFlds)
        If Me("Chg" & ChgFlds(I)) Then ChgCount = ChgCount + 1
    Next I
    
    End Function

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working. Another option would have been to loop all controls, testing for the left 3 characters of the control name being "chg", or using the Tag property to identify the controls to be tested.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I would much prefer to use some kind of identifier in the controls bound to the fields of interest......... I hate hard-coding even when I have a static situation like I do here. I've never had an occasion to make use of a Tag property. How does that work? Enter a letter or number in the property field to essentially create a grouping within the controls collection? Can you give me an example of what the testing statement would look like?

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You enter anything you want in the Tag property, then test:

    Code:
      Dim MyForm                  As Form
      Dim ctl                     As Control
     
      Set MyForm = Forms!frmReservation
      For Each ctl In MyForm.Controls   
        If ctl.Tag = "Clear" Then
          ctl = Null
        End If
      Next ctl
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    COOL. I'll post my new code when I get a few minutes to tweak the control tags.

  14. #14
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Didn't get any hits testing for ctl.Tag = "HL?". I verified the list of controls in form "frmMMT" so I know that's not the problem. I also checked the controls where I added the "Tag" property "HL?". No idea what that problem is. One think of interest is that "ctl." didn't produce a property list that included "Tags", though the compiler didn't complain.

    Code:
    Public Function ChgCount() As Integer
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    '*  From an array of field names, count the number set to TRUE in the current record.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim ctl As Control
    Dim MyForm As Form
      
    Set MyForm = Forms!frmMMT
      For Each ctl In MyForm.Controls
          If ctl.Tag = "HL?" Then
            debut.Print ctl.Name
          End If
      Next ctl
    
    
    End Function

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Debut' should be Debug
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-05-2018, 04:45 PM
  2. Replies: 2
    Last Post: 06-28-2017, 06:16 PM
  3. Replies: 5
    Last Post: 02-08-2017, 05:52 PM
  4. Replies: 2
    Last Post: 06-30-2016, 06:38 PM
  5. Replies: 2
    Last Post: 04-15-2014, 01:59 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
  •  
Other Forums: Microsoft Office Forums