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".
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".
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.
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)
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.
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.
Not that I'm aware of. Like I said, it's not normalized. It would be simple if they were records.
What I was trying to do was something like this: (which of course I've found isn't supported in SQL)
I'll write a little function that will accomplish the task.Code:DCount("[Chg*]", "QMMT", "MEMID = " & Me.MemID & "And [Chg*] = " & True)
Thanks,
Bill
Did you get it sorted Bill, or did the snow gum up the works?
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
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.
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?
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
COOL. I'll post my new code when I get a few minutes to tweak the control tags.
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