VBA Functions in SQL Statement
Don,
Quote:
>I would like to write a function in VBA that acts like an Oracle
>function and can be used in SQL statement. A trivial example would be a
>function that returns a name when given an id.
>the query - select name(id) from emps;
>the function name is - select name into name_string from table where id
>= id_in; return name_string;
>If it is possible to do this in VBA, does anyone have an example or a
>place to point me for resources? Specifically how do I pass a value in
>from a query and return a value.
First of all, you can probably use dlookup() for this. But in general,
this is the technique:
Public Function GetNameFromID(lngID As Long) As Variant
GetNameFromID = DLookup("Name", "yourtable", "yourid=" & lngID)
End Function
(Instead of doing dlookup here you could do something more
interesting....)
Then use it in a SQL statement like so:
select GetNameFromID(table1.id) as NameID, ID from table1
-- Jim Ferguson, FMS
http://www.fmsinc.com
