get query into a label

I need to get the result of the following query into a label called lblTotalDatabaseReceived - I can't get it to seem to work. I can get an msflex grid to populate w/ the correct answer but not the label. What would the syntax be?

Private Sub Form_Load()

cboMonth = frmPendingPSR.cboMonth
cboYear = frmPendingPSR.cboYear
If cboMonth = "January" Then
cboMonth = "1/31"
End If

If cboMonth = "February" Then
cboMonth = "2/29"
End If

If cboMonth = "March" Then
cboMonth = "3/31"
End If

If cboMonth = "April" Then
cboMonth = "4/30"
End If

If cboMonth = "May" Then
cboMonth = "5/31"
End If

If cboMonth = "June" Then
cboMonth = "6/30"
End If

If cboMonth = "July" Then
cboMonth = "7/31"
End If

If cboMonth = "August" Then
cboMonth = "8/31"
End If

If cboMonth = "September" Then
cboMonth = "9/30"
End If

If cboMonth = "October" Then
cboMonth = "10/31"
End If

If cboMonth = "November" Then
cboMonth = "11/30"
End If
If cboMonth = "December" Then
cboMonth = "12/31"
End If

cboYear = Mid(cboYear, 3, 2) 'get 94 from 1994
strStartDate = Format("10/4/94", "mm/dd/yy")
strEndDate = Format(cboMonth & "/" & cboYear, "mm/dd/yy")

MsgBox strStartDate
MsgBox strEndDate

Data2.RecordSource = "Select COUNT (*) From Master WHERE DATE_OPENED BETWEEN #" & strStartDate & "# and #" & strEndDate & "#"

sql = Data2.RecordSource
frmRptTotals.Data2.RecordSource = sql

End Sub

Instead of using a data object I would use code:

Dim conn as new RDO.rdoConnection
Dim objConn As Connection
Dim objCmd As Command
Dim objReturnRst As Recordset

Private Sub Form_Load()
'Make Connection to SQL
With Conn
.Connect = "ODBC;DATABASE=dbname;UID=userid;PWD=password;DSN=dsnname;"
.CursorDriver = rdUseODBC
.EstablishConnection rdDriverCompleteRequired
End With



objCmd.CommandText = "Select COUNT (*) As Total From Master WHERE DATE_OPENED BETWEEN #" & strStartDate & "# and #" & strEndDate & "#"
Set objReturnRst = objCmd.Execute(,,1)
nFK_Total = objReturnRst("Total")
lblTotalDatabaseReceived.Caption = "" & nFK_Total & ""

I hope this helps. Good Luck

John

The whole program has already been designed using the databound controls; is it possible to use a combination. Because I have updates and other queries being done using the databound contol

thanks

Code:

---

Label1.Caption = Data2.Recordset(0)

---

user defined-type not defined

what does this mean using this code

Dim conn As New RDO.rdoConnection
Dim objConn As Connection
Dim objCmd As Command
Dim objReturnRst As Recordset

Im using an Access 97 database if that changes the code at all too!

Thanks

It means that you didn't set Reference to a library.
For RDO -Microsoft Remote Data Object 2.0 Library.
If you are using ADO - Microsoft ActiveX Data Object 2.1 Library

thanks

Label1.Caption = Data2.Recordset(0)

when i try using this it doesn't work unless i put

Label1.Caption = sql

and then it only gives me the sql statement in the caption box instead of the result for example instead of a total I get: Select COUNT (*) From Master WHERE DATE_OPENED BETWEEN #10/04/94# and #03/31/97#

and i should get 4700 ; what would cause this

and this is using the code above

lblTotalDatabaseReceived.Caption = "Select COUNT (*) From Master WHERE DATE_OPENED BETWEEN #" & strStartDate & "# and #" & strEndDate & "#"


why can't i do something like above still returns the sq1 statement and not count.

the lblfield should it be connected to the database in it's properties?

As i am using databound control

When you are doing this:
lblTotalDatabaseReceived.Caption = "Select COUNT (*) From Master WHERE DATE_OPENED BETWEEN #" & strStartDate & "# and #" & strEndDate & "#"

you just passing a string and nothing else.
Just modify this code

Code:

---

Dim sql As String
sql = "select count(*) from products "
Data1.RecordSource = sql
Data1.Refresh
Label1.Caption = Data1.Recordset(0)

---

That works; I guess my main problem was figuring out how to get the recordset into the equation and also using the refresh.

If I have 21 different sql statements that need to go into different labels on a form; could you recommend some proper coding naming conventions?

Thank you for all your help and suggestions would be greatly appreciated!!! you helped me out a lot w/ that last bit.

You have to refresh data control every time you change sql statement. Do you have your sql statements on a different events?

Im not sure what you mean; new to database. But each sql is different for instance on the same form I need a sql statement that gets the date between for DATE_OPENED but I also need to get INQ_BY="T" in the same statement:
Also i had to take refresh out for second sql or i get an error.

this is not working:

Dim sqlTotalDataBasePending As String
Dim sqlTotalDataBaseTelephone As String
Dim sqlTotalDatabaseLetter As String

sqlTotalDataBaseReceived = "Select COUNT (*) From Master WHERE DATE_OPENED BETWEEN #" & strStartDate & "# and #" & strEndDate & "#"
Data2.RecordSource = sqlTotalDataBaseReceived
Data2.Refresh
lblTotalDatabaseReceived.Caption = Data2.Recordset(0)

sqlTotalDataBaseTelephone = "Select COUNT (*) From Master WHERE DATE_OPENED BETWEEN #" & strStartDate & "# and #" & strEndDate & "#" & INQ_BY = "T"
Data2.RecordSource = sqlTotalDataBaseTelephone
lblTotalDatabaseTelephone.Caption = Data2.Recordset(0)

OKAY GOT IT

sql clauses needed to be separated by and and not &

Also putting the refresh in the right place makes all the difference; it must be before the label statement to get the right result!!!!

Thanks for everything!!!

You are very welcome.