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.

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

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

Now using:

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by nico5038

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

Expand|Select|Wrap|Line Numbers

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

Now using:

Expand|Select|Wrap|Line Numbers

 
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.

Count Number of Records of a RecordSet Meeting Certain Criteria

re: Count Number of Records of a RecordSet Meeting Certain Criteria

re: Count Number of Records of a RecordSet Meeting Certain Criteria

/bytes/development

/bytes/it

/bytes/about