By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Count Number of Records of a RecordSet Meeting Certain Criteria

100+
P: 194
Hi Everybody,

I am using the following code to get the recordset of an external database.
Expand|Select|Wrap|Line Numbers
  1. Dim dbPatients As Database
  2.  
  3.         Dim rsCountPatients As Recordset ' to count number of patients in a table        
  4.  
  5.         Const strDBPath = "C:\Patients.mdb" ' external database
  6.         Set dbPatients = OpenDatabase(strDBPath, False, True, ";PWD=abc")
  7.  
  8.         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
Mar 16 '08 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
You can expand the opening of the recordset with a WHERE clause to get the filtered records like:
Expand|Select|Wrap|Line Numbers
  1. Set rsCountPatients= dbNavy.OpenRecordset("SELECT * FROM Admissions WHERE FirstName like 'John*'", dbOpenSnapshot)
  2.  
Now using:
Expand|Select|Wrap|Line Numbers
  1. IF rsCountPatients.eof and rsCountPatients.bof then
  2.    ' Nothing found action
  3. else
  4.   ' force reading all
  5.    rsCountPatients.movelast
  6.  ' show result
  7.   msgbox "Record found: " & rsCountPatients.recordcount
  8. endif
  9.  
Will give the number.

Nic;o)
Mar 17 '08 #2

100+
P: 194
You can expand the opening of the recordset with a WHERE clause to get the filtered records like:
Expand|Select|Wrap|Line Numbers
  1. Set rsCountPatients= dbNavy.OpenRecordset("SELECT * FROM Admissions WHERE FirstName like 'John*'", dbOpenSnapshot)
  2.  
Now using:
Expand|Select|Wrap|Line Numbers
  1. IF rsCountPatients.eof and rsCountPatients.bof then
  2.    ' Nothing found action
  3. else
  4.   ' force reading all
  5.    rsCountPatients.movelast
  6.  ' show result
  7.   msgbox "Record found: " & rsCountPatients.recordcount
  8. endif
  9.  
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.
Mar 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.