Count Number of Records of a RecordSet Meeting Certain Criteria

[mfaisalwarraich]

Hi Everybody,

I am using the following code to get the recordset of an external database.

Dim dbPatients As Database
 
        Dim rsCountPatients As Recordset ' to count number of patients in a table        
 
        Const strDBPath = "C:\Patients.mdb" ' external database
        Set dbPatients = OpenDatabase(strDBPath, False, True, ";PWD=abc")
 
        Set rsCountPatients= dbNavy.OpenRecordset("SELECT  * FROM Admissions", dbOpenSnapshot) 'Admissions is a table where records of admitted patients is entered

Now, how i can count the number of records in Patients table after meeting some criteria. for example i wana count the number of patients who admitted yesterday and belong to New York city and name is John etc. Can i use DCount function here? if yes then please tell me how i can use it. because i have to manipulate too many records based on criteria and then displaying them into a report. i can achieve this by making a sperate query (in external database) for each field of a report (where i wana show the results) but its very time taking and lenghty.

i will be thankful if anybody please tell me about this.

thanking you.

Regards

[nico5038]

You can expand the opening of the recordset with a WHERE clause to get the filtered records like:

Set rsCountPatients= dbNavy.OpenRecordset("SELECT * FROM Admissions WHERE FirstName like 'John*'", dbOpenSnapshot)
 

Now using:

IF rsCountPatients.eof and rsCountPatients.bof then
   ' Nothing found action
else
  ' force reading all
   rsCountPatients.movelast
 ' show result
  msgbox "Record found: " & rsCountPatients.recordcount
endif
 

Will give the number.

Nic;o)

[mfaisalwarraich]

You can expand the opening of the recordset with a WHERE clause to get the filtered records like:

Set rsCountPatients= dbNavy.OpenRecordset("SELECT * FROM Admissions WHERE FirstName like 'John*'", dbOpenSnapshot)
 

Now using:

IF rsCountPatients.eof and rsCountPatients.bof then
   ' Nothing found action
else
  ' force reading all
   rsCountPatients.movelast
 ' show result
  msgbox "Record found: " & rsCountPatients.recordcount
endif
 

Will give the number.

Nic;o)

This is absolutely great :) thank you so much Nico im really grateful to you. it really made my work easy and more precise. thank u again.