Using a User-Created Function in VBA to output criteria in access query

0
votes

In short: I have a user-created function (gettargetTemp(targetTemp_input) with one input that, upon function evaluation, I would like to be able to call the function in an Access query (design view) criteria field and have it represent the criteria string that I want evaluated when the query is run.

i.e. targetTemp_input = 1450 - this value is assigned from a form (and can vary)

Access Query

Field: Pad Temp
Criteria: gettargetTemp("targetTemp_input")

Criteria possibilities:
- Records within 100 degrees of the targetTemp_input value which
typically in Access query design is: Between 1350 and 1550 Or Is Null
- All of the records

The code that makes sense to me is: 

Public Function gettargetTemp(targetTemp_input)
    If Forms![Parameter Confirmation].tempCheck = True Then
        gettargetTemp = "Between " & (targetTemp_input - 100) & " AND " _  
        & (targetTemp_input + 100)  
    Else    
    End If
End Function

The outcome of this typically results in a data mismatch error from Access. Is there a way to accomplish what I'm trying to do? Or maybe do it better? I'm not a programmer, but I have a pretty good technical background.

1
functionvbams-accesscriteria

1 Answers

0
votes

At present, you're trying to have the VBA function form an expression that does the wanted test, rather than doing the test itself. You need to change that:

Function IsTempOK(Temp) As Boolean
  Dim ParamForm As Access.Form, TargetTemp As Long
  Set ParamForm = Forms![Parameter Confirmation]
  If ParamForm.tempCheck Then
    If IsNull(Temp) Then
      IsTempOK = True ' or False, if that's what you want
    Else
      TargetTemp = ParamForm.TargetTemp ' or wherever this is defined
      IsTempOK = (Temp >= TargetTemp - 100) And (Temp <= TargetTemp + 100)
    End If
  Else
    IsTempOK = True
  End If
End Function

In the query definition, the WHERE clause should now use IsTempOK, passing the Temp field as the parameter.