Q: returning "field expression" from recordset object?

[Mark]

Hi all,

Say I have a table called NAMELIST with two fields, NAME1 and NAME2. There's
only one record n the table, and NAME1=Bill, NAME2=Gates

I want to write a function where you enter the table name and the expression
of the fields you want. So, for example, you could enter NAME1 or NAME2 or
(and this is where I'm having trouble) NAME1 & " " & NAME2.

Getting a single field is straightforward. I have some dummy code below that
works for a single field. But I have no idea how to get it to return NAME1 &
" " & NAME2, ie "Bill Gates"

Any help greatly appreciated!
-Mark

Function GetVal(str As String, strexp As String) As String
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(str)
rs.MoveFirst

'This works if strexp is a single field.
'but how do I return "Name1 & Name2"?
GetVal = rs(strexp)
End Function

 

[Allen Browne]

You can read the combination of 2 fields like this:
DLookup("[Name1] & "" "" & [Name2]", "NAMELIST")

If you want to read a particular row from the table (e.g. where the NameID
field is 99), use:
DLookup("[Name1] & "" "" & [Name2]", "NAMELIST", "[NameID]=99")

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

If you want to write a function like DLookup() that opens the recordset to
get the fields, see:
ELookup() - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
You would call it the same way as for DLookup() above.

 

[Mark]

Thank you!

I use dlookup alot, but for some reason, I didn't think of using it here. I
think it was because I was connecting to an external db through a passthrough
query and figured it couldn't be done on a passthrough, but I just tried it
and it works just fine.

-Mark

You can read the combination of 2 fields like this:
DLookup("[Name1] & "" "" & [Name2]", "NAMELIST")

If you want to read a particular row from the table (e.g. where the NameID
field is 99), use:
DLookup("[Name1] & "" "" & [Name2]", "NAMELIST", "[NameID]=99")

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

If you want to write a function like DLookup() that opens the recordset to
get the fields, see:
ELookup() - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
You would call it the same way as for DLookup() above.

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

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

Hi all,

Say I have a table called NAMELIST with two fields, NAME1 and NAME2.
There's
only one record n the table, and NAME1=Bill, NAME2=Gates

I want to write a function where you enter the table name and the
expression
of the fields you want. So, for example, you could enter NAME1 or NAME2 or
(and this is where I'm having trouble) NAME1 & " " & NAME2.

Getting a single field is straightforward. I have some dummy code below
that
works for a single field. But I have no idea how to get it to return
NAME1 &
" " & NAME2, ie "Bill Gates"

Any help greatly appreciated!
-Mark

Function GetVal(str As String, strexp As String) As String
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(str)
rs.MoveFirst

'This works if strexp is a single field.
'but how do I return "Name1 & Name2"?
GetVal = rs(strexp)
End Function