How to count the records with a specific criteria

[rbasram]

All I want to do is to count the number of records which meet a specefic criteria from a table. I have done some part of the code but it doesn't work. Please help...

Set dbMyDB = CurrentDb
Set rsMyRS = dbMyDB.OpenRecordset(" Select [Product] from Bulletins group by [product];&quot

'If there are no records in the table then the function exits
If rsMyRS.RecordCount <= 0 Then Exit Function

'Assign the selected item in the listbox as a criteria
strstring = Me!lstNumeric.Column(intCurrent)

'specify criteria for the find method
strCriteria = &quot;[Product]= ' &quot; & strstring & &quot;'&quot;

'Return an accurate recordcount
rsMyRS.MoveLast
lngRecCount = rsMyRS.RecordCount


'To start the process move to the first record of the table
rsMyRS.MoveFirst
rsMyRS.FindFirst strCriteria

Do Until rsMyRS.EOF
rsMyRS.FindNext strCriteria
Count = Count + 1

'If no match is found then exit the loop
If rsMyRS.EOF = True Then Exit Do

Loop

'Assign the counter to the total sequence
Forms!AddRec!txtSequence = Count

 

[THoey]

You know, you could do it that way, but there is an easier way. Why not just create a query like:

SELECT COUNT(*)
FROM MyTable
WHERE MyField = strstring

You are already build a query string and then running it against the table. Sounds like a lot of wasted effort to duplicate a SQL function.

Hope that helps... Terry M. Hoey

 

[jebry]

Hi!

Add this to your select statement:

Count([YourPKField]) As ProductCount

Then, after you set the strCriteria, use the following code:

rsMyRS.FindFirst strCriteria

If rsMyRS.NoMatch = True Then
Some error message stating that no matches were found
Else
Forms!AddRec!txtSequence = rsMyRS!ProductCount
End If

hth
Jeff Bridgham

 

[rbasram]

Thanks for your help but someone told me that an sql statement only gives you a table but not a single count but anyways i tried to use the dcount funtion do you think it is a better idea.. Really confused I tried this but doesn't work either...

dim total as integer

total = dcount(&quot;[number]&quot; , &quot; mytablename&quot;, &quot;[number]=&quot; myformfieldtxt)

txtsequence = total

What am I doing wrong...