how to assign the result of query to a label's caption? Thanks

[xpengi]

Hi,

I have an existing query, named "query1", "select count(*) fro
students where amount IS NULL",

i will get "60" and want to sign it to a label's caption in a form
such as,

lblTitle.caption = "There are " & resultOfQuery (60) & " students."

how to do this?

i code as but not work.

lblTitle.Caption = "There are "
CurrentDb.QueryDefs("query0").ReturnsRecords & " students."

then lblTitle shows "There are TRUE students"

Thanks really.

Pete

 

[Jeff Boyce]

Another approach would be to use the DCount() function in that expression.

Good luck

Jeff Boyce
<Access MVP>

 

[Douglas J. Steele]

You can't refer to a recordset that way.

You could try referring to it as CurrentDb.QueryDefs("query0").Fields(0), or
you could rewrite your SQL as "select count(*) As TotalStudents from
students where amount IS NULL" and use
CurrentDb.QueryDefs("query0").Fields("TotalStudents")

However, it may be easier just to use:

lblTitle.Caption = "There are " & _
DCount("*", "students", "Amount Is Null") & " students."

If you want to avoid getting "There are 1 students", you could try:

Dim lngCount As Long

lngCount = DCount("*", "students", "Amount Is Null")
If lngCount = 1 Then
lblTitleCaption = "There is 1 student."
Else
lblTitleCaption = "There are " & lngCount & " students."
End If

 

[fredg]

Hi,

I have an existing query, named "query1", "select count(*) from
students where amount IS NULL",

i will get "60" and want to sign it to a label's caption in a form.
such as,

lblTitle.caption = "There are " & resultOfQuery (60) & " students."

how to do this?

i code as but not work.

lblTitle.Caption = "There are " &
CurrentDb.QueryDefs("query0").ReturnsRecords & " students."

then lblTitle shows "There are TRUE students"

Thanks really.

Peter

No need to run the query as long as it is a stored query.

In the Form's Load Event:
lblTitle.Caption = DCount("*","Query1")

 

[Ofer]

You don't need the query, instead use the dcount
lblTitle.caption = "There are " & dcount("*","students","amount = NULL") &
" students."

Change the

 

[xpengi]

it works, really thank