SQL COUNT Question

  • Thread starter Thread starter Pete Beatty
  • Start date Start date
P

Pete Beatty

I am attempting to use the sql version of count. I have the count query
working. However, I am at a lose about referencing the variable where sql
stores that data. How do I reference "Test_Count" in following query:

select count(*) as Test_Count from units having ([unit]=0)

I want to use this in by code and do not understand, or am missing,
information on this subject. If I create the query in a string variable.
how do I execute it ? do I use RUNSQL or OPENRECORDSET? How do I retrieve
count value?
 
Pete said:
I am attempting to use the sql version of count. I have the count query
working. However, I am at a lose about referencing the variable where sql
stores that data. How do I reference "Test_Count" in following query:

select count(*) as Test_Count from units having ([unit]=0)

I want to use this in by code and do not understand, or am missing,
information on this subject. If I create the query in a string variable.
how do I execute it ? do I use RUNSQL or OPENRECORDSET? How do I retrieve
count value?
Click to expand...

Several ways. The most complicated, but most versatile, is
to open a recordset.

You could also use the DLookup function to run the query and
return the result.

The easiest way is to skip the query altogether and just use
DCount to calculate the value:

DCount("*", "units", "unit=0")

which could be used directly in a text box expression by
preseeding it with an = sign.

BTW, you should not be using a HAVING clause in that query,
it should be a WHERE clause.
 
Thanks for the reply.
I would use DCOUNT. However, there is big performance hit when you are
running against a remote DB and SQL is the preferred way.


Marshall Barton said:
Pete said:
I am attempting to use the sql version of count. I have the count query
working. However, I am at a lose about referencing the variable where sql
stores that data. How do I reference "Test_Count" in following query:

select count(*) as Test_Count from units having ([unit]=0)

I want to use this in by code and do not understand, or am missing,
information on this subject. If I create the query in a string variable.
how do I execute it ? do I use RUNSQL or OPENRECORDSET? How do I
retrieve
count value?
Click to expand...

Several ways. The most complicated, but most versatile, is
to open a recordset.

You could also use the DLookup function to run the query and
return the result.

The easiest way is to skip the query altogether and just use
DCount to calculate the value:

DCount("*", "units", "unit=0")

which could be used directly in a text box expression by
preseeding it with an = sign.

BTW, you should not be using a HAVING clause in that query,
it should be a WHERE clause.
Click to expand...
 
I don't see why DCount would be particularly slow. It just
runs a simple query and returns the result. Have you done a
performance test?

If you prefer, then use a recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngUnitCount As Long
Set db = CurrentDb()
Set rs = OpenRecordset("Select Count(*) From units Where
unit=0")
lngUnitCount = rs.Fields(0)
rs.Close : Set rs = Nothing
Set db = Nothing
--
Marsh
MVP [MS Access]



Pete said:
Thanks for the reply.
I would use DCOUNT. However, there is big performance hit when you are
running against a remote DB and SQL is the preferred way.

Pete said:
I am attempting to use the sql version of count. I have the count query
working. However, I am at a lose about referencing the variable where sql
stores that data. How do I reference "Test_Count" in following query:

select count(*) as Test_Count from units having ([unit]=0)

I want to use this in by code and do not understand, or am missing,
information on this subject. If I create the query in a string variable.
how do I execute it ? do I use RUNSQL or OPENRECORDSET? How do I
retrieve
count value?
Click to expand...
"Marshall Barton" wrote
Several ways. The most complicated, but most versatile, is
to open a recordset.

You could also use the DLookup function to run the query and
return the result.

The easiest way is to skip the query altogether and just use
DCount to calculate the value:

DCount("*", "units", "unit=0")

which could be used directly in a text box expression by
preseeding it with an = sign.

BTW, you should not be using a HAVING clause in that query,
it should be a WHERE clause.
Click to expand...
Click to expand...
 
Back
Top