SQL COUNT Question

[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?

 

[Marshall Barton]

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?

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.

 

[Pete Beatty]

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.

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?

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.

 

[Marshall Barton]

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]

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.

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?

"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.