Calling UDFs from ADO SQL

[jmcswain]

Can you call a UDF (in VB or inside a module in the Access Database) inside a SQL statement in ADO?

For example:


Dim rst As Recordset
rst.Open "SELECT GetRegion(tblMain.Zip) FROM tblMain;"

Public Function GetRegion(pvarZip as Variant) As Variant
' blah blah blah
End Function

[Serge]

This is weird. Are you trying to get a zip code from the table or you have table for each zip code? Because with the code like this I assume that you have table for ech zip code (which is really not a good sign of a database design).

But if you really meant that you want to get records from the tblMain for the specific zip code, then you don't need extra function (unless you're doing something specific):

Dim rst As Recordset
rst.Open "SELECT * From FROM tblMain WHERE Zip = " & varYourZipCodeVariable

[jmcswain]

A little clarification:

The UDF in question will return the ZipCode out of the table for all addresses except those in California. In California only, the UDF will return either "North" or "South" depending on a range of zip codes that may change. I could string immediate ifs together, but I have a need to do a couple other UDFs for other reasons, and just want to know if it can be done.

And no, there is not a table for each zip code. It's just a property info (address, city, state, zip, etc...) table. And the actual SQL statement has like 50 fields in it.