searching all fields in a record

[billcam]

I have a table with 36 fields that may contain a value from a 20 item code list or be null. I need a report that tells how many times each code appears in all fields on a monthly basis. Any ideas on how I should go about this?

 

[billcam]

If nobody knows this, then is there a way I can copy many fields from one table into a new table with just one field so I can run a report grouping on that field?

 

[gwinn7]

Answer to first question:

Yes, something like the following...

Dim IsDataEnabled as Boolean
Dim ctl as control
Dim icount as Integer

For Each ctl In Me.Controls

Select Case ctl.ControlType
Case Is = acTextBox ' 109
IsDataEnabled = True
Case Is = acListBox ' 110
IsDataEnabled = True
Case Is = acComboBox ' 111
IsDataEnabled = True
Case Is = acCheckBox ' 106
IsDataEnabled = True
End Select

If IsDataEnabled Then
If InStr(1, ctl.ControlSource, "=&quot > 0 Then
If ctl.value = "My Value" then
icount = icount + 1
End if
End if
End If

Next ctl

Caution, the above code will most likely need debugging. This is completely ad-hoc.

Gary
gwinn7

 

[PaulF]

create a table to store the results in (tblCounts), and one to list all of the codes to search for (tblCodes)
In tblCounts have one field for each possible code to search for, and name the field the same as the code
In tblCodes have one field only, and add a record for each code, name this field CodeField.
Then use this code, only change the "YourTableName" to the actual name of your table you want searched, and the "[DateField]" to the name of the Date/Time field in this table
This code only check for the current month and year, so you'd have to change that to reflect other months, but it should give you a starting point.

Private Sub GetCounts_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * From tblCounts&quot
DoCmd.SetWarnings True
Dim db As DAO.database, rstTable As DAO.Recordset
Dim rstResults As DAO.Recordset, rstCodes As DAO.Recordset
Dim tdf As DAO.TableDef, fld As DAO.Field
Dim intCount As Integer, strCode As String, strFldName As String
Set db = CurrentDb
Set rstResults = db.OpenRecordset("tblCounts&quot 'table with results
Set rstCodes = db.OpenRecordset("tblCodes&quot ' table with codes to search
Set tdf = db.TableDefs("YourTableName&quot ' table you want searched
Set rstTable = db.OpenRecordset("YourTableName&quot
If rstTable.RecordCount > 0 Then
With rstTable
.MoveFirst
Do While Not .EOF
intCount = 0
If Month(rstTable![DateField]) = Month(Now()) And Year(rstTable![DateField]) = Year(Now()) Then
With rstCodes
.MoveFirst
Do While Not .EOF()
strCode = rstCodes!CodeField
For Each fld In tdf.Fields
strFldName = Nz(rstTable(fld.Name), " &quot
If InStr(strFldName, strCode) > 0 Then
intCount = intCount + 1
End If
Next fld
With rstResults
If .RecordCount = 0 Then
.AddNew
Else
.MoveFirst
.Edit
End If
'add count to code field
'this code assumes that the field name is the same as the code
rstResults(strCode) = intCount
.Update
End With
intCount = 0
.MoveNext
Loop
End With
End If
.MoveNext
Loop
End With
End If
MsgBox "Done"
ExitHere:
Set db = Nothing
Set rstTable = Nothing
Set rstCodes = Nothing
Set rstResults = Nothing
Set tdf = Nothing
Set fld = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & " : ", vbCritical, "Error While Processing "
GoTo ExitHere
End Sub

PaulF

 

[Databaseguy]

Paul beat me to it. I was going to loop throught the fields of the tableDef object and store the results in a new table.

 

[JoeMiller]

You could also do a union query to query the table and base your group count query on the union query. Then you don't have any VBA code and no temporary tables.

SELECT Field1
FROM MyTable
UNION ALL SELECT Field2
FROM MyTable
UNION ALL SELECT Field3
FROM MyTable
UNION ALL SELECT Field4
FROM MyTable
... <=== field5 thru field35 repeating the pattern
UNION ALL SELECT Field36
FROM MyTable

It's long and drawn out, but it will work..

HTH Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

All to the good.

So Far.

The question appears to indicate the db is poorly designed/laid out. Thirty plus fields which all contain more or less the same information strongly suggests that the table / db is far from mormalized and should be reviewed.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over