quick question about sql queries

[jlabre01]

Hi,

I just wanted to know what the proper format was for setting the control source for a text box to an sql statement was?

jlabre01

 

[KenHigg]

Are you trying to insert a single value from a table field in the textbox?

 

[jlabre01]

yes, i'm searching for one value

 

[KenHigg]

Then using dlookup() maybe an option - ?

 

[jlabre01]

well the query needs to check a couple things, to return the value

 

[KenHigg]

You can do that with a domain function like dlookup().

 

[jlabre01]

thanks, i appreciate the help

 

[KenHigg]

Just out of curiosity I tried putting a sql statement in for the control source in the properties and using vba and couldn't get it to work. That's the essence of a domain function...

 

[LPurvis]

The closest you'll get to a SQL source in a controlsource if a function which returns a value (much like the Domain Lookup does).
For example
=CurrentDb.OpenRecordset("SELECT FieldName FROM TableName WHERE FieldX = 1")(0)

Obviously untidy and unadvisable. Just re-enforcing a point really.
(And certain expressions are all over the place in Acc2007).

You'd have to write your own SQL statement resolving function to have real control over the execution.
But alot is possible with just the humble DLookup too - as already mentioned.