sql count

  • Thread starter Thread starter learning_codes
  • Start date Start date
L

learning_codes

Hi,

I try to create a box (text27) on the form and gather total number
from the query. I get an error. I hope you can help me to correct
if I did wrong.


Dim sql_count as string


sql_count = "SELECT Count(Table1.EmployeeID) AS CountOfEmployeeID FROM
Table1;"


Me.text27 = sql_count


When I run the form and the box (text27) shows "SELECT Count
(Table1.EmployeeID) AS CountOfEmployeeID FROM Table1;"

Your help would be much appreciated.

Thanks
 
Hi,

I try to create a box (text27) on the form and gather total number
from the query. I get an error. I hope you can help me to correct
if I did wrong.

Dim sql_count as string

sql_count = "SELECT Count(Table1.EmployeeID) AS CountOfEmployeeID FROM
Table1;"

Me.text27 = sql_count

When I run the form and the box (text27) shows "SELECT Count
(Table1.EmployeeID) AS CountOfEmployeeID FROM Table1;"

Your help would be much appreciated.

Thanks
Click to expand...

1) A control source cannot display the results of a SQL statement.

2) All you have done is set the control source of [Text27] to a string
.... "Select Count ... etc".

3) If you just wish to count how many records are returned from a
Table or Query, all you need is, as control source of an unbound
control:

=DCount("*","TableOrQueryName")

where "TableOrQueryName" is the name of the table or query that you
wish to count records of.
 
Well, you can't run a form, you open a form.

You can't get the record count that way.

Try this:

' ------start of code----------------
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

'create the select statement
sSQL = "SELECT Count(Table1.EmployeeID) AS CountOfEmployeeID"
sSQL = sSQL & " FROM Table1;"

'open recordset
Set rs = db.OpenRecordset(sSQL)

'go to the last record
rs.MoveLast

'set control to recordcount
Me.text27 = rs.RecordCount

'close recordset
rs.Close
Set rs = Nothing
Set db = Nothing
'-------------end of code ------


HTH
 
Back
Top