.RecordCount issue in VB6

[Perilous1]

I know how to get the RecordCount of a Access table in vb6, but is there a way to get the RecordCount of a specific field only within that table?

I have three fields within the table (machine, device and misc). Just doing the basic .RecordCount command will return the record count for just the first field (machine). How do I specify that I want the record count of the 2nd or 3rd field?

 

[gmmastros]

Your question doesn't make sense. Tables are rectangular.... meaning... every row has the same number of columns, and every column has the same number of rows.

So.... since you appear to be expecting different values for each column, can you explain why you expect the RecordCount to be different? Perhaps you want a count of distinct values in a particular column, or perhaps a count where there is actually data.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Perilous1]

Separate fields within a table can, and often do, have differing amounts of rows containing data. I am not sure why a record count of rows containing no data would ever be desired, but that is immaterial.

Field 1 within my table has 37 rows that contain data. Field 2 has 14 rows that contain data. I would like to be able to do a recordcount of field 2 that returns a value of 14 based on the number of rows that contain data within that field.

 

[Andrzejek]

Try:

[tt]Select * From MyTable
WHERE Field2 IS NOT NULL[/tt]

That should give you 14 in rst.RecordCount

But I am with gmmastros - your explanation does not make sense...

Have fun.

---- Andy

 

[PWise]

open a recordset

Select count(*) as Reccnt 
from tablename 
Where fieldname is not null

if the default is 0 then

Where fieldname >0

 

[gmmastros]

Ah... rows containing data. That's the key.

Try this:

Select Count(*) As TotalNumberOfRows,
       Count(Machine) As TotalNumberOfMachines,
       Count(Device) As TotalNumberOfDevices,
       Count(Misc) As TotalNumberOfMisc,
       Count(Distinct Machine) As CountOfDistinctMachines,
       Count(Distinct Device) As CountOfDistinctDevices,
       Count(Disintct Misc) As CountOfDistinctMisc
From   YourTableNameHere

The Count aggregate function ignores NULL's when counting the rows. This code assumes that the data will be NULL if there is no machine, device, or misc. If you are storing an empty string instead of null, the above query will not work.

SQL Server has a NullIf function that will return NULL if the first argument matches the 2nd one.

Example: Select NullIf(ColumnName, '')

This would return NULL if there is an empty string the data. I know that Access has a function similar to NullIf, but I don't know what it's called. The SQL Server version would look like this:

Select Count(*) As TotalNumberOfRows,
       Count([!]NullIf([/!]Machine[!],'')[/!]) As TotalNumberOfMachines,
       Count([!]NullIf([/!]Device[!],'')[/!]) As TotalNumberOfDevices,
       Count([!]NullIf([/!]Misc[!],'')[/!]) As TotalNumberOfMisc,
       Count(Distinct [!]NullIf([/!]Machine[!],'')[/!]) As CountOfDistinctMachines,
       Count(Distinct [!]NullIf([/!]Device[!],'')[/!]) As CountOfDistinctDevices,
       Count(Disintct [!]NullIf([/!]Misc[!],'')[/!]) As CountOfDistinctMisc
From   YourTableNameHere

Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tedsmith]

Count" counts records whether they have data or not.

To count records with data it can be easier to run a separate query for each column to count fields only with data, depending on the type of data.
Eg If columns are numerical -
ColName(1)="Machine"
ColName(2)="Device"
Set MyDb = OpenDatabase(MyTable)
For Col =1 to 2
Criteria="Select " & ColName & " From MyTable where " & ColName(Col) & " > 0"
set MyRec = MyDb.OpenRecordset(Criteria, dbOpenSnatshot)
MyRec.Movelast
CountOfColumns(Col)=MyRec.RecordCount
Next
in your case the result is -
CountofColumn(1)=34
CountofColumn(2)=14

 

[gmmastros]

"Count" counts records whether they have data or not.

According to

http://office.microsoft.com/en-us/access-help/count-function-HA001231438.aspx

Although expr can perform a calculation on a field, Count simply tallies the number of records. It does not matter what values are stored in the records.

The Count function does not count records that have Null fields unless expr is the asterisk (*) wildcard character.

This is why I suggested writing the query the way I did. It will be faster to query the data once to return multiple counts than it is to query the data multiple times.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tedsmith]

There's always something new to learn even in an obsolete language!
I had never heard about the * in this matter

 

[strongm]

Er ... I don't think SQL is obsolete just yet, Ted ...

 

[tedsmith]

I was referring to vb6

 

[strongm]

So, what new thing have you learned about VB6, Ted?

Not the Count thing, as that is a SQL function.

 

[tedsmith]

I was referring to the interpretation and use of:-
<The Count function does not count records that have Null fields unless expr is the asterisk (*) wildcard character.>
in a VB6 recodset query.

 

[strongm]

nothing to do with Vb6

 

[tedsmith]

I beg to differ.
I am using a collection of words that represents a Criteria Statement for a vb6 recordset as covered in VB6 help. It happens to be a notation of a type of SQL and in this I view it as a tool of vb6.

To me this is a vb6 issue. I am not knowingly using any application with the words SQL in it although there could unknown to me be a dll used with SQL in it's name.

This is similar to me as using a Winsock in vb6 or any other control common to other languages.

What interests me in this forum is how I use in in a vb6 application and any differences between the usage of SQL and other applications like MsAccess.
So I consider it primarily a vb6 issue not a SQL issue but others may view it differently if they choose to do so.

Hence, I view this as something new I have learned about to use in vb6 and it may or may not also be a usable feature in other applications that use SQL.

However if you can provide evidence to the contrary, I will willingly change my view.

 

[strongm]

You can view it as you like. You are, however, wrong. You live in a strange world. Winsock is no more a part of VB6 than SQL is. And Count is one step further away from being a VB6 feature than either of those. (oh, and by the way, there is no such thing as 'a winsock'. Winsock is short for Windows Sockets API. What yi are referring to is a Winsock control, which is merely a (partial)wrapper for winsock so that VB6 can use the Windows Sockets API - it does not make winsock a native part of VB

But OK, let's pretend that the DAO library is, on some bizarre world, a part of the VB6 language. But it is the library that can use SQL, it is SQL has the COUNT function. This removes it at least one step from being a VB6 feature. Let's look at it from a slightly different perspective: I can use VB6's shell command (Shell is definitely native to VB6) to run a commandline command such as ping. Does this make ping a part of VB6?

>I am not knowingly using any application with the words SQL in it

I can use the Shell command mentioned above to run a whole bunch of DOS commands. But it hasn't got the word DOS in it, and none of the DLLs it uses have the word DOS in them. That doesn't mean that I must conclude that they are in fact VB6 commands rather than DOS commands

>It happens to be a notation of a type of SQL

No 'happens to be' about it. It is SQL (if no other factors are applying then it is Microsoft Jet SQL - you can look that up if you like). Let's check out the much-vaunted 'VB6 documentation' that you like to bang on about (actually the DAO documentation) for OpenRecordset:

A String specifying the source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement that returns records

Note that SQL is an ISO standard, and that COUNT (and its behaviour) are explicitly defined in that standard. THEY ARE NOTHING TO DO WITH VB6

 

[tedsmith]

Yes, I think we agree on the facts but have a different way of describing it.

I understand completely what you are saying but I consider your last sentence the equivalent of saying the brakes in my car are not part of my car.

An obsolete Ford and a new Toyoto can use exactly the same model and brand of brakes but to me they are part of the car.

When I go to buy a new car I don't usually specify different brands of internal mechanical parts however I can retrofit other brands once I have purchased it. (like I can use other 'sockets' in vb6)

My original statement was equivalent to me as saying "I found a new way of using the brakes on my obsolete car by using my hand instead of my foot." This does not make brakes as such obsolete.
The brakes are not obsolete, only the car.

Is this still strange?

 

[strongm]

Um ... if you are going to try a car allegory for this, I think you'd be better off thinking about the road rather than the brakes.

 

[tedsmith]

Yes I agree, I am afraid that the vb6/Microsoft road may be leading nowhere.
I am waiting for someone to invent vb6 for Androids.

 

[strongm]

>I am waiting for someone to invent vb6 for Androids
Have you had a look at NS Basic?

http://www.nsbasic.com/app/