Syntax Error with Custom VBA Function in Query 1

[corycrum]

Hi,

I have a custom VBA function to constrain values to a user-specified minimum and maximum. The syntax is BAND(Value, Min, Max). If Value < Min, BAND returns Min. If Value > Max, BAND returns Max. If Value is between Min and Max, BAND returns Value. The function code is provided further below.

When i call this function in the VBA immediate window, it seems to work correctly. E.g. ?BAND(20, 10, 15) returns 15. When i call this function from a simple MS Access select query, e.g. BAND_TEST: BAND(20, 10, 15), it generates an error message as soon as i tab out of the field builder. The error reads "The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier."

What am i missing here? I'm new to coding VBA formulas in Access although i have coded quite a few in Excel. Any guidance is very much appreciated.

Thanks,
Cory


Option Compare Database
Option Explicit

Function BAND(varValue As Variant, varMin As Variant, varMax As Variant) As Variant
On Error Resume Next
If IsError(varValue) Then
BAND = Null
Exit Function
End If

If varValue > varMax Then
BAND = varMax
Exit Function

ElseIf varValue < varMin Then
BAND = varMin
Exit Function

Else
BAND = varValue
End If

End Function

 

[strongm]

BAND is a Jet 4 reserved word.

When you try and use the query, you are using Jet. When you run from the immediate window, you are not. Rename it, and it should work.