DBQuery and SQL function COUNT not working

[DingDong]

Hello,

Database Table Clients has 4 records.
I run the following script and the return result is 1.
I would expect 4.
Is there a problem with the DBQuery function or the script?

Thankyou!

//Connect to Datasource
//
Let>str=Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\sample.mdb;Uid=Admin;Pwd=toto;
DBConnect>str,dbRep
//
//Perform SELECT query
//
Let>SQL=SELECT COUNT(ClientID) FROM Clients
DBQuery>dbRep,SQL,TabCLIENTS,NumRecs,NumFields
//
Message>%NumRecs%
//
//Close database connection
DBClose>dbRep

[Marcus Tettmar]

That's correct. You're looking at the number of records returned. Your SQL will return ONE row. So what your message box shows you is correct.

You want to look at the VALUE INSIDE the data returned. The data you want is in:

TabCLIENTS_1_1

The ONE row returned contains the count of ClientIDs.

[adroege]

There is another possibility....


That is DingDong expects the count to return 4 and it returns the number 1.

So.... the possibility exists that the column clientid contains null in 3 of the 4 rows. To have the count function give you the row count and ignore any nulls it encounters do the following:

count(*)


Use the asterisk instead of giving it a column name in the table.

[DingDong]

Thank you mtettmar and adroege for your reply.

By changing the statement to:

With the following

Code: Select all

Let>SQL=SELECT ClientID FROM Clients

The result show in the message is 4.

With the COUNT(*) function the result is still 1.

The ClientID field is an indexed field and null value is not permitted.

[Marcus Tettmar]

Of course. Your second SQL returns ALL records, so NumRecs will be the number of records returned.

Your first SQL returns **ONE** row, so NumRecs will return ONE. In your first SQL the count value is INSIDE the data returned. The SQL returns one field in one record, so TabCLIENTS_1_1 will hold the count.

[DingDong]

Thank you mtettmar,

Could you tell me how will I retreive the data inside the row bring by the first statement and show it in a message.

[Marcus Tettmar]

MessageModal>TabCLIENTS_1_1

[DingDong]

mtettmar,

I understand now, thank you for your patience and your eplanations.

DingDong