Member Avatar for Naveed_Cn

Hi Everyone:*
I am working on Database project using DAO. Now I got a problem in counting all the data from one Field:-/ . Please help Me in this problem.
:confused: And Tell Me how can I get the total of any field by using SQL STATEMENT?
Thanks In Advance

  • 3 Contributors
  • 3 Replies
  • 100 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by AV Manoharan

Dani AI

Generated

— pointed you toward doing the work in SQL and reminded you to name the returned column. Below is a compact, practical pattern for VB6 + DAO that runs a single aggregate query and pulls the single returned value reliably, plus a few common pitfalls to check.

Use this DAO workflow (fill the placeholders for your table/field and choose the aggregate you need):

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim totalValue As Variant
Dim aggFunc As String

Set db = DBEngine.Workspaces(0).Databases(0)
aggFunc = "<AGGREGATE_FUNCTION>" ' e.g. a count or a total
sql = "SELECT " & aggFunc & "([FieldName]) AS TotalValue FROM [TableName];"
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

If Not rs.EOF Then
totalValue = rs!TotalValue
If IsNull(totalValue) Then totalValue = 0
Else
totalValue = 0
End If

rs.Close
Set rs = Nothing
Set db = Nothing

Troubleshooting and tips:

  • Add a reference to the Microsoft DAO library (References) before compiling.
  • If the result comes back Null, check for all-Null rows in the source field; handle with an IsNull check (shown above).
  • If you need one total per group, use a GROUP BY query instead of a single aggregate.
  • If your data sits in MySQL, DAO is not the right client API — use ADO/ODBC (or the MySQL ODBC driver) to run the same aggregate SQL and fetch the scalar result.
  • Avoid using DISTINCT unless you explicitly want only unique values counted; it changes the meaning.

This pattern reads the computed value by alias (rs!TotalValue) so your code stays clear even if column names change.

Recommended Answers

All 3 Replies

Member Avatar for QVeen72

Hi,

Use This Query:
For Distinct Count:
Select Distinct Count(myfield) From Mytable

For Number Of Rrcords:
Select Distinct Count(*) From Mytable

For Sum:
Select Distinct Sum(amt_field) From Mytable


Regards
Veena

Member Avatar for QVeen72

Hi,

Sorry, Remove Distinct In last 2 statements.

Member Avatar for AV Manoharan

Hi,

Sorry, Remove Distinct In last 2 statements.

It may also give error.

For totalling a field always use a place holder using the key word AS

eg.

"SELECT SUM(myField) AS TtldField from mytable;"

regards
AV Manoharan

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.