My answer will include terms you might wish to look up in Access Help if you are not sure about them.
Part I: Using SQL from VBA
You can do several things from VBA that are directly SQL-related.
First, open a recordset based on SQL using the .OpenRecordset method
Second, execute an SQL action query using the .RunSQL method. (Or is that the .ExecuteSQL method ... I sometimes get confused.)
Third, use functions such as DLookup, DMax, DAvg - which IMPLY an SQL SELECT statement for which you supply the (single) field name to be selected, the table name, and the sometimes optional WHERE clause.
Part II - using VBA in SQL
Any SQL query (well... almost any query) can include VBA references in the SELECT and WHERE clauses. For VBA that implies underlying SQL, using that code runs the risk of self-blocking and of otherwise depressing performance, since VBA code is pseudo-compiled in Access. (Differs from VB compiler, which actually DOES produce machine code...)
To make this work, create public functions in general modules. The code you write has to be PUBLIC - so that SQL can see it. Function - because SQL requires a value. In a general module - because at the time SQL is running, it is not directly in the same context as class modules (associated with forms and reports). But a general module is in context for as long as Access is open.
When writing VBA functions for use in SQL, remember that the returned value must be consistent. I.e. if it is an INTEGER function, it DAMN well must not ever return a DOUBLE value. And it should always return something no matter what kind of error it encounters. I.e. good error trapping.
