Home-grown Functions: Use In Queries?

[LarryP]

As far as I I've been able to ascertain, if I write a
global function it can be used in forms, reports and VBA
procedures, but not in queries because of SQL's aversion
to such "unknown" critters. Am I mistaken? Is there in
fact a way to use such functions in the Query Builder
rather than having to do it with imbedded SQL in a VBA
procedure?

 

[LarryP]

I stand corrected, I guess. I'll try it out again.
Thanks.

 

[Douglas J. Steele]

There is a slight caveat to what MDW said, though. You can use user-defined
functions in queries, but only if you're running the queries from within
Access. If you're trying to run a query from outside of Access (say from VB,
or in ASP code), you can't use them. This is because when you run the
queries from outside of Access, your only interface with the database is
through the Jet Engine, and the Jet Engine doesn't know anything about
user-defined functions.

 

[Guest]

Ah, that must be where I've encountered the problem. So a
DoCmd.OpenQuery in VBA will fail if it involves a function
I wrote myself?

 

[Douglas J. Steele]

I said "You can use user-defined functions in queries...if you're running
the queries from within Access."

When you say you're using "DoCmd.OpenQuery in VBA", is this in Access, or in
some other application? If it's from within Access, you should be able to do
it.

Have you tried? What happened? If you got an error message, what was it?