Microsoft Access VBA Macro Programming

If Asc(Mid(Target, n, 1 )) >= 97 Then

CheckUpperCase = False

Exit Function

End If

Next n

CheckUpperCase = True

End Function

This was then incorporated into a Select query:

SELECT iif(CheckUpperCase(Company),0,ValueField) AS Rslt

FROM MyTable

The iif statement was used to interpret the Boolean return value and display zero or the

field value accordingly. This example assumes that MyTable had fields called Company and

ValueField in it.

This was one of these situations that when it was discussed at a meeting sounded totally

impossible, but by careful use of VBA a solution was provided.

If you use a custom function within a SQL query, close the VBE window when you run it.

If you do not do this, the VBE window will be refreshed as the SQL query is running. This

adds a large overhead, and if the query is returning many records, it will take longer to run.

164 Microsoft Access 2010 VBA Macro Programming