Forms and SQL

[scotti2hotti43]

I am trying to write the value of an sql statement into a text box.

When the code below runs the statement itself is put into the textbox,
i guess this is because of the quotes, but if i remove them i get an
error message.

CODE
--------------------
strWhere = "SELECT COUNT(*) FROM [Waiting List]"
Me.TextField = strWhere
--------------------

How can i store the value of my select statement into a variable then
put that value into a form textbox?

Thanks

[Rick Brandt]

sc************@yahoo.co.uk wrote:

I am trying to write the value of an sql statement into a text box.

When the code below runs the statement itself is put into the textbox,
i guess this is because of the quotes, but if i remove them i get an
error message.

CODE
--------------------
strWhere = "SELECT COUNT(*) FROM [Waiting List]"
Me.TextField = strWhere
--------------------

How can i store the value of my select statement into a variable then
put that value into a form textbox?

Thanks

Me.TextField = DCount("*", "Waiting List")
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Scott]

Thank you Rick, it worked great

[Bob Bridges]

A more complicated way of doing it may also be more useful, if what you're
trying to get isn't available via a built-in function. For example, here I
want to pull a value from a particular record in my dataset:

Dim rs As Recordset
Set rs = db.OpenRecordset("Select left(Dept,4) From [" & TblNam & "]" &
_
"Where Left(RecTyp, 7) = 'Grants-'", dbOpenSnapshot)
MaxPg = rs.Fields(0).Value 'The department prefix
Set rs = Nothing

This assumes that there is only one record that fits my WHERE criterion, of
course.

<sc************@yahoo.co.ukwrote in message
news:11**********************@64g2000cwx.googlegro ups.com...

>I am trying to write the value of an sql statement into a text box.

When the code below runs the statement itself is put into the textbox,
i guess this is because of the quotes, but if i remove them i get an
error message.

strWhere = "SELECT COUNT(*) FROM [Waiting List]"
Me.TextField = strWhere

How can i store the value of my select statement into a variable then
put that value into a form textbox?