SQL Statement in Form?

  • Thread starter Thread starter Ian Chappel
  • Start date Start date
I

Ian Chappel

If I have a SQL clause that returns only a single value (1 column x 1 row),
is there a way of directly inserting this into a Form's TextBox or similar?

I find that sometimes DLookup is not elaborate enough, but creating a
standalone query seems overkill for a single result.
 
If I have a SQL clause that returns only a single value (1 column x 1 row),
is there a way of directly inserting this into a Form's TextBox or similar?

I find that sometimes DLookup is not elaborate enough, but creating a
standalone query seems overkill for a single result.
Click to expand...

Can't use an SQL as a control's control source.
Create the query.
Then, as the control's control source write:
=DLookUp("FieldName]","QueryName")
 
That's about what I thought

It just seems a shame there isn't a =SQL() function or similar, that would
enable you to enter an SQL clause direct into a control, perhaps returning
ERROR if the query produced more than a single column/row.

fredg said:
If I have a SQL clause that returns only a single value (1 column x 1
row),
is there a way of directly inserting this into a Form's TextBox or
similar?

I find that sometimes DLookup is not elaborate enough, but creating a
standalone query seems overkill for a single result.
Click to expand...

Can't use an SQL as a control's control source.
Create the query.
Then, as the control's control source write:
=DLookUp("FieldName]","QueryName")
Click to expand...
 
Write a function that uses that SQL statement and returns the value, then
use the function as the control's control source.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ian Chappel said:
That's about what I thought

It just seems a shame there isn't a =SQL() function or similar, that would
enable you to enter an SQL clause direct into a control, perhaps returning
ERROR if the query produced more than a single column/row.

fredg said:
If I have a SQL clause that returns only a single value (1 column x 1
row),
is there a way of directly inserting this into a Form's TextBox or
similar?

I find that sometimes DLookup is not elaborate enough, but creating a
standalone query seems overkill for a single result.
Click to expand...

Can't use an SQL as a control's control source.
Create the query.
Then, as the control's control source write:
=DLookUp("FieldName]","QueryName")
Click to expand...
Click to expand...
 
Thanks, but I'd still need to use DLookup wouldn't I, to retrieve the single
value from the query? Or is there a simpler way?

Douglas J. Steele said:
Write a function that uses that SQL statement and returns the value, then
use the function as the control's control source.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ian Chappel said:
That's about what I thought

It just seems a shame there isn't a =SQL() function or similar, that
would enable you to enter an SQL clause direct into a control, perhaps
returning ERROR if the query produced more than a single column/row.

fredg said:
On Sat, 4 Feb 2006 23:34:58 -0000, Ian Chappel wrote:

If I have a SQL clause that returns only a single value (1 column x 1
row),
is there a way of directly inserting this into a Form's TextBox or
similar?

I find that sometimes DLookup is not elaborate enough, but creating a
standalone query seems overkill for a single result.

Can't use an SQL as a control's control source.
Create the query.
Then, as the control's control source write:
=DLookUp("FieldName]","QueryName")
Click to expand...
Click to expand...
Click to expand...
 
Using DAO, something like:

Function ReturnValue(SQL As String) As Variant

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(SQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
ReturnValue = rsCurr.Fields(0)
End If

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

End Function

This will return the value of the first field in the first row returned by
the SQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ian Chappel said:
Thanks, but I'd still need to use DLookup wouldn't I, to retrieve the
single value from the query? Or is there a simpler way?

Douglas J. Steele said:
Write a function that uses that SQL statement and returns the value, then
use the function as the control's control source.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ian Chappel said:
That's about what I thought

It just seems a shame there isn't a =SQL() function or similar, that
would enable you to enter an SQL clause direct into a control, perhaps
returning ERROR if the query produced more than a single column/row.

On Sat, 4 Feb 2006 23:34:58 -0000, Ian Chappel wrote:

If I have a SQL clause that returns only a single value (1 column x 1
row),
is there a way of directly inserting this into a Form's TextBox or
similar?

I find that sometimes DLookup is not elaborate enough, but creating a
standalone query seems overkill for a single result.

Can't use an SQL as a control's control source.
Create the query.
Then, as the control's control source write:
=DLookUp("FieldName]","QueryName")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top