Visual Basic :: Count Number Of Records In Recordset

Count The Number Of Records In The Dbf File
hi

I need the program that counts the number of records in the DBF file. The file may contain more than 75000 records but it could not be opened in Excel. After 65536 records the other records gets truncated.so The file has to be opened in Access and calculated.i havent have no idea how to do using acess so please help me.Advance thanks for helping me out.

How To Get Total Number Of Records Of Records Return By Recordset?
I wanna get the total number of records returned by the record set. I used RecordCount, but it always return -1.
some 1 plz help me out.

Dim cnn as ADODB.Connection
Dim ars as ADODB.Recordset
set cnn = new ADODB.Connection
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:MapData.mdb;Persist Security Info=false"
.Open
End With

Dim SQL as string
SQL = " Select * from Education"
set ars = cnn.Execute(SQL)

MsgBox ars.RecordCount 'NOR WORKING???

...---.....
Hassan Zia
Hassan_Zia@mathtech-pk.com

Count The Number Of Records In An Access Database
Hi

I am creating an Access database, and I need to put the information in some fields in my form into an array so I can perform some searches. I need to be able to count the number of records in my database (I think) so that I can declare the array my_array(numberofrecords, 8). Can anyone help me with the code to do this?

I have an auto number for each record as its primary key. Maybe this will make the coding easier, but I'm not sure.

I need the details to be updated each time a record is saved. Is this possible? I can't find any good sample code anywhere.

Thanks

Any Ideas On How Can I Count The Number Of Records In A SubForm?
I have a Form called FormProtocolo, and a SubForm object "Filho8" inside, referring to a SubForm: SubFormProtocolo. How could I refer to this subform?

Ways To Count Number Of Records Returned
In vb6, you can count the records returned by

Recordset.recordcount

but how do you do it in VB.NET? What I want to do is to know how many records returned from my SQL then loop through that many records.

Thanks.

Count Number Of Rows(records) In An Excel Sheet
I am trying to loop through the records in an excel sheet. I have attempted it and all I can achieve is count the whole worksheet((65536 rows). Do I have to use an ADODB recordset to achieve this ? Here is what I have so far:

Code:
Public Sub RowCount()
Dim counter As Double
For Each rw In Worksheets("brutto").Rows
counter = counter + 1
Next rw
MsgBox counter
End Sub

[Edit...] Sorry I should have looked a bit more. I found a faq here.

A VB turned PHP geek

Edited by - paCkeTroUTer on 8/3/2003 8:03:26 PM

Get Record Count And Current Row Number ADODB Recordset
How do i get the record count and current row number from an ADODB recordset?

Thanks
GK

Best Way To Get The Number Of Records In A File With Millions Records
Hi all,

I have searched the threads looking for a way to get the total number of records in a file in one shot.

It looks like Split function into an array and using UBound is the answer.

However, I have a file that contains millons of records and that may be a problem.

Question: Is there another way of geting the total number of records in a file in one shot? Note that each line is delimited by vbLF.

Thanks in advance for any help.

Count Records
i am querying a table and would like a msg box to appear after the query has run telling the user how many record are in the table.

but i dont know how to do it has any one got any ideas that would help.

cheers

stuart

Recordset BOF & EOF Both True Thou' Recordset Contains Records
I have a recordset with at least one record in it, it is triggering an 'EOF or BOF is True' error when using .MovePrevious or .MoveNext.

Putting the IDE in break mode and analising the values of EOF and BOF in the Immediate Window show them both to be True.

There are records in the recordset and I have confirmed this by analising the Database file directly.

How can BOF and EOF both show True other than when the RecordSet of Empty?

I am using ADO with the Jet Engine 3.41.

Please help, I can't figure out what is going on.

Trying To Count The # Of Records And Then Use That # To Do Calculations
I have a lot of data that I am sorting into similar areas. After this data is sorted, I want to count the total number of records in the text file. Next, I want to count the number of records that was sorted (the data that I want to see).

Now after I have a count of total records and a count of sorted records I want to take those 2 numbers and perform division and multiplication (mathematical operations) with them.

So…..
How do you count the number of total records (rows of data) in your file???

And

How do you perform mathematical operations on those numbers???

Here is an example of what I am trying to do (I have attached a sample data file):

In the attached sample data file there are a total of 17 flights: how do I get VB6 to count these 17 records???

Now after sorting those you get 3 flights that have the same 3 fields: ‘100’ and ‘NYNY’: how do I get VB6 to count just these 3 records???

Finally, how do I take the 17 and divide that by 3 then multiply that answer by 100???

Thanks
bhowerton

Count Null Records
I'm using VBA code from a comand button to try to count the null records in a database. When I run the code from the click event I get a "Type Mismatch" error. I know that I have null records in the field(Logout) that I'm specifying so I'm not sure if I'm not accessing the database correctly or if there's something in my logic that's wrong.

Private Sub_Command12_Click()
Dim rec As Recordset
Dim counter As Integer

counter = 0

Set rec = CurrentDb.OpenRecordset("Select Login, Logout tblMain where Logout is null;"

Do While Not rec.EOF
counter = counter + 1
rec.MoveNext
Loop
rec.Close

End Sub

Count Record And Top 20 Records
I need someone to help me build a query
I know how to do count record, but I don't know how to build a query that returns the count from the top 20 records

For example: I have a table that stored 42 students's grade(BTW, I have some blank grades). I want to count how many Cs or below from the first 20 records.
For some reason, it always returns the wrong result

select Top 20 count(grade) as MyGradeCount from table where grade <>'A' or grade <>'B' or grade <>''

Does anybody know how to fix my problem?

Wrong Count Records
I have write the following to count records from table1.
Msgbox display 70 records but i know that the records is 72.
Why display wrong number of records?
Thanks

Dim dbs As Database, rst As Recordset
Dim NumRec As Long
Set dbs = OpenDatabase("c:programsvb6data.mdb")
Set rst = dbs.OpenRecordset("Table1")
NumRec = rst.RecordCount
MsgBox NumRec
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

HOW Do I COunt How Many Records Are In My Database.
HI

I want a Variable with the number of Records in my database!See,this is what i want to Do:

I want to Generate a Reference Number for example:

Record 1:

Name:
Surname:
Tel:
Reference Number: 1 <--------- Becuase it's record 1

So Record 2 will have a Reference of the number 2.

I want a Count = Count + 1 Thing that wil count my Records,and if i Click on ADD NEW CUSTOMER then the Count = Count + 1 must be executed.

I Am Using a Adodc Control with a Database Access File.
Please help me with this.!!

Cheers!!

[vb6] Count Records With X In Field Y?
I am trying to count how many entries have the word system in the Field RcaOwner, iv been trying this code

Code:
Private Sub Command1_Click()
Dim mynumber As Integer

ConnectDb
DbDataTable

If Not rsEmc.EOF Then rsEmc.MoveFirst
Do While Not rsEmc.EOF
If rsEmc!RcaOwner = System Then
mynumber = mynumber + 1
End If
Loop

CloseDb
MsgBox mynumber
End Sub

It just seems to time out and does not do anything. The database has over 800 records, would this make a difference?

If anyone can point me in the right direction i would be greatful.

Regards

PsYiOn

Help!! Count Of Records In Datareport
Im trying to show the occurrencies of some fields in a table (count records). But I get the error "Can't find the datafield 'total'".
Here is the code.

Code:
DbFile = App.Path & "vendors.mdb"
Set confaltas = New ADODB.Connection
confaltas.CursorLocation = adUseClient
confaltas.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DbFile & ";" & _
"Persist Security Info=False"
confaltas.Open

SQLstmt = "select vendedor,count(vendedor) as [Total] from ficha group by vendedor"

Set rsfaltas2 = New ADODB.Recordset

rsfaltas2.CursorType = adOpenKeyset
rsfaltas2.LockType = adLockOptimistic
rsfaltas2.Open SQLstmt, confaltas, adOpenStatic, adLockOptimistic, adCmdUnknown

with DataReport1.Sections("Sección1")
.Controls("txtvendedor").DataField = "vendedor"
.Controls("txtTotal").DataField = "Total" 'here is the error'
End With

DataReport1.Show

End Sub

Thanks in advance.

CyberSandokan

Count Records In Subform
How can I count records in a subform? not in the whole table, only in the current record...

Ex: The CD form has a subform (apps). This CD has 7 apps. the apps are 7 records in the apps subform. When navigating the CD form the apps subform updates as the CD ID changes.

For the current CD record, I would like to show the total number of apps...

thanks

How To Count Duplicated Records????
Hello,
Iam a vb beginner could you help me

How to count the number of duplicated records.
For ex.in a form1 there is 1 textbox when iam entering 3 similar names say shanp, id in another textbox by command button, like this...
1.shanp id is 2
2.shanp id is 5
3.shanp id is 7

form2 contains one textbox and one listbox.
This form1 textbox name should be entered in form2 textbox
in a listbox 3 dates should be listed as per order.
how to list as per order in a listbox???????

I tried here.

Dim ws As Workspace
Dim db As Database
Dim rs As Recordset
Dim RSLOCAL As Recordset
Dim strsql As String
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:WINDOWSPATS.mdb")
strsql = "SELECT * FROM EMP WHERE ENAME = '" & Text1.Text & "'"
Set rs = db.OpenRecordset(strsql, dbOpenDynaset)
Set RSLOCAL = db.OpenRecordset(strsql, dbOpenSnapshot)
If RSLOCAL.BOF And RSLOCAL.EOF Then
rs.AddNew
rs.Fields("ENAME").Value = Text1.Text
rs.Fields("EMPID").Value = Val(Text2.Text)
rs.Update
rs.Close
Set rs = Nothing
Else
MsgBox "You already have that name in the database"
form2.Text1.Text = Form1.Text1.Text

I do not know how to list in listbox

Form2.List1.List(J) = Form1.Text2.Text
Form2.Show

__________________
ppr

Count Records In A Database
I would like to be able to count how many records are in a "random" database file. Length of each record is about 1000 spaces.

Is there a fast and easy way to count them?

Thanks for reading this.

Count NULL Records
Hi,

I have a form where I display some stats from what zone in country are the customers from, but I want to count the records where I don't have information too, that are empty.
How can I do that? I was trying this code, but it doesn't work.

ClientesRS.Open "SELECT * From Clientes WHERE c_distrito=''", m_oConn
txtSemDistrito.Text = ClientesRS.RecordCount

Thanks in advance.

Count Returned Records
Hi,
I am using VB6 with an access97 database. I use this code to count records returned from a SQL statement. .recordsource = "SLQ Select"
This works fine when I select all of the records in the table but when I use filters, I get an error. Any suggestions?

Issue Regarding The Records Count
Hi

I wants to populate records only if the count(somefield) is less than the value entered by the parameter field.

Ex:
say i have CDR table which has field Phone,now i wants to show only those records whose count(phoneNo) in CDR table is less than 3 (value entered in parameter) during specified period.

How shall i do this,if i try to use Count in the record select it says
The Function can't be used b'coz it must be evaluated later

Help Needed!!!!!

Regards
Rupali

Count Records In Data Report
Hi Buddies
I have a report that displays data from a recordset.I need to number the records.How do i do this? I need your assistance urgently.But how do i dsiplay the count on the data report.
Regards
Justus Njoroge

SQL/Duplicate Records Count Question
I am wanting to take every record from a table and copy it to another table, but i also want to copy a 'count' variable to the new table which tells if there is any duplicate records in the table.

So, for example,

if the table is
co1 col2
--- ---
1 2
1 2
3 4
1 2
3 4
5 6

the new table will have the data

col1 col2 count
---- ----- ------
1 2 3
3 4 2
5 6 1

i know i can do this with a recordset and looping with visual basic, however with large tables this gets to be pretty time consuming because it has to loop through all the records.
I was wondering if there was a way i can just do this in SQL, without having to loop through and manipulate the recordset in visual basic.

thanks for the help.

Count No. Of Records In Which A Specified String Is Found?
hi all, i am using dao to create a recordset. For example, if i want to search a specified string throughout all the rows, how do i count the no. of records in the table whenever the string is found on the 1st occurence in the row as 1 record? i need help on this .. thx..

Find Count Of Records And Insert?
Greetings,

How can I find the count of records in a table and then insert that value into a record in a different table?

Thanks,
J

Count Original Records From Array??
I have an array that sorts and returns coordinates... I have the number of coordinates in each file, ulim... this, however includes those coordinates which are listed multiple times... I need the number of coordinates in this file that are original (those that don't show up twice). How do I go about doing this? Thanks in advance...

HELP Please! Count Records According To Date Entered
Hi everyone,

I have form with (2) two textboxes. The first is the start date and the second is the end date enter by user. It will count the records in the recordset according to the start and end date entered.

Private Sub Command1_Click()
Dim sql As String
Dim a As String
Dim b As String

a = "#11/20/2004#"
b = "#12/30/2004#"

'a = Text1.Text
'b = Text2.Text

sql = "Select * from sample1 where Date between " & a & " and " & b
Data1.RecordSource = sql
Data1.Refresh

MsgBox (Data1.Recordset.RecordCount)

End Sub

I have this code but the msgbox value is only one (1) record which i have 3 records
Please help me. Thank you very much in advance.

Edited by - Jam_10 on 11/30/2004 12:03:39 AM

Count Unique Records In Datagrid
I'm having trouble counting the unique records in a datagrid. Like you would do with a sql distinct command.
I've written some code and placed it in the movecomplete of the adodc control, but it crashes the whole VB IDE. If I place the same code on a button... it works...
Can anybody help me out here?

This is my code:

Code:

Private Sub adoData_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
On Error GoTo errormove
sql = "SELECT * FROM orders WHERE type = " & adoData.Recordset.Fields("requesttype")
adoData2.RecordSource = sql
adoData2.Refresh

txtTotalOrders = adoData2.Recordset.RecordCount

If txtTotalOrders = 0 Then
MsgBox "No orders for this request type!", 48, "Message"
Exit Sub
End If

DataGrid1.Row = 0
DiffCust = 1
custno = DataGrid1.Columns.Item("custno")
For I = 0 To txtTotalOrders - 1
DataGrid1.Row = I

If DataGrid1.Columns.Item("custno") <> custno Then

DiffCust = DiffCust + 1

End If

custno = DataGrid1.Columns.Item("custno")

Next I

txtDiffCust = DiffCust

Exit Sub
errormove:
If Err.Number = 3021 Then
adoData.Recordset.MoveFirst
Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Error"
End If
End Sub

Greetz from a vb6 newbie!

How To Count Records/Rows In A Text File
I want to use a For Next loop to process the info in a text file. To do that I think I need an upper limit number. So I want to count the number of records in the file. How can I do that. I thought something like the below would work but it didn't.

Option Explicit
Dim NumRecords As Integer

Private Sub cmdCountRecords_Click()
If Not EOF(1) Then
NumRecords = NumRecords + 1
Else
End If
picOutput.Print "Number of Records = "; NumRecords
End Sub

Private Sub Form_Load()
If Right(App.Path, 1) <> "" Then
Open App.Path & "ModifiedMIM.txt" For Input As #1
Else
Open App.Path & "ModifiedMIM.txt" For Input As #1
End If
End Sub

How Write A Access Query To Count Records!
Hello Coder,

I need to know the number of UserID who have Bought only one time. Two, I need a second query of the number of UserID who have Bought more than once.

i write this query but, its displaying all the rocords in the database:
[code]SELECT Count(UID) FROM tblContrib GROUP BY UID HAVING Count(UID)>1;
[code]
Help me please , its an urgent

Count Total # Of Records In A Text File
Modified from original..................

This code gives me count. But when I click on my rc button it doubles my count. So, first click it gives me 12 (and this is correct. i only have 12 records in my file), 2nd click gives me 24, 3rd click gives me 36 and then it breaks.

Code:
Private Sub cmdRC_Click()
Dim TextLine$, Filename$
Dim FileHandle As Integer

Filename$ = "c:Test.txt"

' Test if the file exists
If Len(Dir(Filename$)) = 0 Then

'If Dir(Filename$) = "" Then
Exit Sub
MsgBox "File Not Found", vbCritical
Else
FileHandle = FreeFile ' This is safer than assigning a number

Open Filename$ For Input As #FileHandle

Do While Not EOF(FileHandle) ' Loop until end of file
Line Input #FileHandle, TextLine$ ' Read line into variable
NumRecords = NumRecords + 1
Loop

MsgBox NumRecords

Close #FileHandle

End If

End Sub

Small Bit Of Code To Count The Records In A Txt Or .dat File
Hi I need to check that the number of lines produced in a .dat file is the same as the number of records in a access db table.

I just want to open the .dat file and count the lines. I tried this but it got stuck in a loop. Why ?
what did I do wrong.

Private Sub Command1_Click()
Dim filenum As Integer
Dim num As Long

filenum = FreeFile
num = 0
Open "c:storeimascons.dat" For Input As #filenum
Do Until EOF(filenum)
num = num + 1
MsgBox num
Loop
Close filenum
MsgBox num

End Sub

This didnt work , anyone ?

Thx locutus

Count And Display Records Based On Criteria
hi all,
I have a access database with two fields designation and dateofjoin.
Designation field contains doctors,nurses,wardboys etc. I want to print a data report which should count the number of doctors and nurses and disply yearwise. For example

1998 1999 2000

doctors 5 2 4
nurses 5 1 -

I want to use only one dataenvironment and command object.

Any help appreciated !

jai

How To Count Records/Rows In A Text File
I want to use a For Next loop to process the info in a text file. To do that I think I need an upper limit number. So I want to count the number of records in the file. How can I do that. I thought something like the below would work but it didn't.

Code:

Option Explicit
Dim NumRecords As Integer

Private Sub cmdCountRecords_Click()
    If Not EOF(1) Then
        NumRecords = NumRecords + 1
        Else
    End If
    picOutput.Print "Number of Records = "; NumRecords
End Sub

Private Sub Form_Load()
    If Right(App.Path, 1) <> "" Then
          Open App.Path & "ModifiedMIM.txt" For Input As #1
    Else
          Open App.Path & "ModifiedMIM.txt" For Input As #1
    End If
End Sub

Edited by - webmanoffesto on 10/6/2003 5:11:26 AM

Count Number
Hello, I do not understand how to count number ?

example:
if click Command1, will print Time text to Text1, but if need add Count, how to add it ?

1, PM 01:59:31
2, PM 01:59:31
3, PM 01:59:32
4, PM 01:59:36
5, PM 01:59:37
...etc...

Code:
Private Sub Command1_Click()

Text1.Text = Text1.Text & Time & vbCrLf
Text1.SelStart = Len(Text1.Text)

End Sub

can suggest me?
many thanks!

Count The Row Number
Hi,

I have a speedsheet in Excel. I need to write a VBA(macro) to count the row number for a column, for example colum A1

how to do this? would you privide some example code?

Thanks

Jack

How To Get Recordset Count
I am creating table at run time with each student name and saving in database.
I want to display on Tables in ListBox and when user click on any name, program will open that particular recordset/table.

I am using following code for creating tables.

Code:
res = InputBox("please Enter Student Name")
Set tdfNew = db1.CreateTableDef(res)

With tdfNew
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Grade", dbText)
.Fields.Append .CreateField("TeacherName", dbText)

db1.TableDefs.Append tdfNew

End With

my program now input different fields and save in above table.
How can I run for loop to get name of all existing table in database?

[vb]
cnt= db1.recordsets.count

do while cnt >=0

set rst=db1.openrecordset(cnt)
with rst(i)
.
.
end with

loop

[vb]
algorithm
Read Databse base.
Get total number of recordsets.
loop throgh recordset and get the name of the recordset.
display name in list box.

when click on list box, open that particular recordset

I am not sure how can I get above to work.

Please help

Thanks
No matter DAO, ADO or whatever works... job need to be done.

Getting The Count From A Recordset
Im using this sql statement to get the number of employees in a shift

Code:
rsOTLookup.Open "SELECT Count(*) FROM emp_employees WHERE Dept Like '" & strShiftToCall & "%' AND active = 1 ", vlDB, adOpenStatic, adLockOptimistic

However I can't figure out how to actually access the count from the recordset.

RecordSet And SQl Count....
Hey guys!

How do I work with the recordset that I opened with a SQL Count Command?

for example....

==============================================
dim rs as adodb.recordset

rs.open "Select Count(*) from Test"

***How do I retrieve the number that was found?*****

==============================================

Thanks guys!

Elminster

Count Of Recordset
Hi All,
I have another problem.
I get the data from database in a recordset name rsRecord and i want know how many records in the recordset i wrote this code

dim rsCount as string
rsCount = rsRecord.RecordsetCount

but it getting wrong number even it getting -1 in rsCount any one have any idea please let me know what is the correct code.

Alim

Count Of Value In A Recordset
Hello Folks,

I would appreciate your help with a problem I am having. I am using ASP and Access as the database. Code and Q is below:

--connection string goes here--
Code: SQLs="SELECT Title, AutoID, CreateDate, Category, Active" & _
" FROM Table1 WHERE" & _
" UserID = " & Session("User_ID") & " "

objRecordset.Open SQLs, objConnection, 3
intRecordCount = objRecordset.RecordCount

If objRecordset.EOF Then
Response.Write("<p><a href=""/norecords.asp"">Add</a></p>")
Else
'if there are records then loop through the fields
Response.Write "<table width=""100%"" border=""0"" cellspacing=""1"" cellpadding=""2"">"

Do While Not objRecordset.EOF
Response.Write "<tr>"
Response.Write "<td>" & objRecordset("Title") & "</td>"
Response.Write "<td><a href=""/details.asp"">Edit</a></td>"
Response.Write "<td><a href=""#"">Delete</a></td>"
Response.Write "<td>" & objRecordset("Active")
Response.Write "</td>"
Response.Write "</tr>"
'move on to the next record
objRecordset.MoveNext
Loop
Response.Write "</table>"
End If

--my question--
The Active field in the db is 1 or zero. If the recordset has all 0's in the Active field I want to display a link in one of the cells in the loop. How do I do that? How do I figure out if there are no Active records in the recordset?

I thought of doing a counter within the loop but realised that the counter won't tell true value until the loop has ended. I think that doing another loop would slow things down.

Any suggestions would be helpful.
Thanks

Edited by - xsoftdev on 10/1/2003 2:51:55 PM

Newbie Question: How Can I Find The Count Of Specific Records ...
i'm new to this, pls bear with me ...

how can i find the count of specific records on a field on an access db?

for example ... the gender

how will i find the count of females on the record?

thanks in advance

**RESOLVED** DCount - Need To Count Records With Date Criteria
I'm creating a textbox in Access for my database which deals with voucher dates. Since there are only a certain number of vouchers allowed per billing date, I'd like a quick and easy way to check how many vouchers have been entered for a certain date range. I'm not even sure if DCount is the appropriate expression, but it's the only one I've read a little about. I'm not sure how to put in my dates as criteria. This is what I have coded my textbox to show so far:

Code:
=DCount("[ForCount]","Voucher")

This returns the total number of records in my Voucher table
(note: ForCount is just the autonumber field that I am using ..yah know....for the count!)

if I wanted to count only the records with Voucher dates between Jan 1 2002 and Dec 31 2002, how could I specify that!? Any suggestions would be great!

Edited by - LisaRae007 on 1/8/2003 2:16:07 PM

Count Number Of Records In Recordset

Hello,How can I count the complete number of records that exist in a recordset?Thank in advance.

Hello,

How can I count the complete number of records that exist in a recordset?

Thank in advance.