I'm not sure how to return the value using the SQL statement?

[Chris K]

I want set Town.Defaultvalue to query value:

Town.DefaultValue = SQL "SELECT TOP 1 JHPclients.County FROM JHPclients
GROUP BY JHPclients.County ORDER BY Count(JHPclients.County) DESC;"

but I'm not sure how to return the value using the SQL statement? Any ideas?

 

[Allen Browne]

A SQL statement cannot return a value.
You can DLookup() the field in a saved query.

Alternatively you can code like this ELookup() function:
http://allenbrowne.com/ser-42.html

 

[Chris K]

I tried Dlookup which seem to return the correct value, msgbox confirms the
right value ("Leics"), but when I start a new record the county field shows
#Name#

County.DefaultValue = DLookup("[county]", "[top county]")
MsgBox County.DefaultValue

Any thoughts?

 

[Chris K]

I dont know why it didn't work but I changed it to add the formula rather
than the value and it works

County.DefaultValue = "= DLookup('[county]', '[top county]')"

I tried Dlookup which seem to return the correct value, msgbox confirms
the right value ("Leics"), but when I start a new record the county field
shows #Name#

County.DefaultValue = DLookup("[county]", "[top county]")
MsgBox County.DefaultValue

Any thoughts?

A SQL statement cannot return a value.
You can DLookup() the field in a saved query.

Alternatively you can code like this ELookup() function:
http://allenbrowne.com/ser-42.html

 

[Ken Snell]

You need to wrap the value in " characters when setting the DefaultValue:

County.DefaultValue = Chr(34) & DLookup("[county]", "[top county]") &
Chr(34)

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I tried Dlookup which seem to return the correct value, msgbox confirms the
right value ("Leics"), but when I start a new record the county field shows
#Name#

County.DefaultValue = DLookup("[county]", "[top county]")
MsgBox County.DefaultValue

Any thoughts?

A SQL statement cannot return a value.
You can DLookup() the field in a saved query.

Alternatively you can code like this ELookup() function:
http://allenbrowne.com/ser-42.html

 

[Chris K]

I can see that now thanks

You need to wrap the value in " characters when setting the DefaultValue:

County.DefaultValue = Chr(34) & DLookup("[county]", "[top county]") &
Chr(34)

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I tried Dlookup which seem to return the correct value, msgbox confirms
the right value ("Leics"), but when I start a new record the county field
shows #Name#

County.DefaultValue = DLookup("[county]", "[top county]")
MsgBox County.DefaultValue

Any thoughts?

A SQL statement cannot return a value.
You can DLookup() the field in a saved query.

Alternatively you can code like this ELookup() function:
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I want set Town.Defaultvalue to query value:

Town.DefaultValue = SQL "SELECT TOP 1 JHPclients.County FROM
JHPclients GROUP BY JHPclients.County ORDER BY Count(JHPclients.County)
DESC;"

but I'm not sure how to return the value using the SQL statement? Any
ideas?