Use of Wildcard "*" in Expression in Query in Access 2000

[jodphd]

Hello all,

I have a problem in which my original expression using the COUNT function in a Select Query worked but was too long to include all necessary fields.

For example, my original expression was:

EQCOLNegAcctsTotal: Count([EQCOLColAgency1])+Count([EQCOLColAgency2])+Count([EQCOLColAgency3])+Count([EQCOLColAgency4])+Count([EQCOLColAgency5])+Count([EQCOLColAgency6])+Count([EQCOLColAgency7])+Count([EQCOLColAgency8])+Count([EQCOLColAgency9])+Count([EQCOLColAgency10])+Count([EQCOLColAgency11])+Count([EQCOLColAgency12])+Count([EQCOLColAgency13])+Count([EQCOLColAgency14])+Count([EQCOLColAgency15])+Count([EQCOLColAgency16])+Count([EQCOLColAgency17])+Count([EQCOLColAgency18])+Count([EQCOLColAgency19])

This is the truncated version---I need to include 25 fields but this won't fit.

Can someone help me with the proper syntax to use a wildcard in my query expression? All the fields to be counted start the same---the only difference is the number 1 to 25.

Thanks in advance,
Jim

 

[Aivars]

Hi, Jim!

How I'm seeing you did create your tables incorrect root and branch.
For more easily work with data it's useful to create two or more related tables. In your case I would be split your table. I would be created separate table for Agencies,
e.g.
AgencyID
AgencyName
....

... and created relationchips one to many. But for this table you can try following VBA codes:

MyTotalAgencyCount=EQCOLNegAcctsTotal

Function EQCOLNegAcctsTotal() As Long
Dim tdf As TableDef
Dim fld As Field
Dim strSQL As String
Dim rst As Recordset

'Set tdf = CurrentDb.TableDefs("MyTable&quot '>>> I don't know why some times it doesn't work because:
For Each tdf In CurrentDb.TableDefs
If tdf.Name = "MyTable" Then
For Each fld In tdf.Fields
If InStr(1, tdf.Name, "EQCOLColAgency", vbTextCompare) > 0 Then
strSQL = "SELECT Count(MyTable." & fld.Name & &quot AS CountOfAgency FROM MyTable;"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not rst.EOF Then
EQCOLNegAcctsTotal = EQCOLNegAcctsTotal + rst(0)
End If
rst.Close
Set rst = Nothing
End If
Next fld
Exit For
End If
Next tdf
End Function

Aivars

 

[TTThio]

Hei,

I don't know about using wildcard to perform calculation, but I have another idea if you are familiar with recordset.

Build a public function in the module, exp:

Public Function Count()

Dim Total as integer
Dim A, B, C, etc (depends on how many fields) as integer
Dim db as Database
Dim rs as Recordset

Set db = currentDB
Set rs = db.OpenRecordset ("Table/query&quot

A = rs![EQCOLColAgency1]
B = rs![EQCOLColAgency2],etc...

Total = A + B + etc....

Count = Total

Set rs = nothing
End Sub

Then in the query ,
EQCOLNegAcctsTotal: Count


Good luck,
Tin Tin