get query into a label

[Bebe]

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

[Bebe]

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

[LG]

Label1.Caption = Data2.Recordset(0)

[Bebe]

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

[Bebe]

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

Thanks

[LG]

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

[Bebe]

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

[Bebe]

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

[LG]

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

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

[Bebe]

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.

[LG]

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

[Bebe]

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)

[Bebe]

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!!!

[LG]

You are very welcome.