Using Operators in Query Criteria Function 1

[ejc00]

I'm trying to use a function to set the criteria for a query. When I use the following code, it works fine, where critType is my function:

critType = "D"

However, when I try to use any sort of operator, my query doesn't work:

critType = "Like '*'"
critType = "'D' OR 'H'"

Are there symbols that I need to use around operators in order to make them work when they are created in VBA? Any help would be GREATLY appreciated.

Thanks

 

[ejc00]

here is the code that i'm using for the creating the criteria from a listbox... the problem is in the "critType = critType & " OR " & ctl.Column(0, varCriteria)" part of it... for some reason the query isn't recognizing the way that i'm concatenating the string together using the OR operator:

Public Function critType() As String

Dim varCriteria As Variant
Dim i As Long

Set ctl = Forms!frm_YieldCurve!lstType

i = 0

If ctl.ItemsSelected.Count = 0 Then
critType = "*"
Else
For Each varCriteria In ctl.ItemsSelected
i = i + 1
If i = 1 Then
critType = ctl.Column(0, varCriteria)
Else
critType = critType & " OR " & ctl.Column(0, varCriteria)
End If
Next
End If

Set ctl = Nothing

End Function

 

[lespaul]

Where's the SQL you're putting the criteria into?

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[ejc00]

leslie... it's a saved query... here's the SQL behind it... basically, i'm using functions to try to avoid having to create a monstrous SQL statement in VBA... but it's looking like i may not be able to avoid it... unfortunately:

SELECT tbl_Model.Month, (Sum([tbl_Model].[Amount]))/(Sum([tbl_Model].[PDDollarAmount])) AS Rate, Sum(tbl_Model.PDDollarAmount) AS SumOfPDDollarAmount, Sum(tbl_Model.Amount) AS SumOfAmount
FROM tbl_Model
WHERE (((tbl_Model.Resell)=&quot;No&quot AND ((tbl_Model.Month)<=critMonths()) AND ((tbl_Model.Company) Like critCompany()) AND ((tbl_Model.ID) Like critName()) AND ((tbl_Model.TY) Like (critType())) AND ((tbl_Model.LOC) Like critLocation()))
GROUP BY tbl_Model.Month;

 

[lespaul]

I think if you change your &quot;LIKE&quot; to IN it may work, but I don't think you can use the * in a LIKE or IN because then its expecting to find data in the field that has a '*' in it and that's not what you are trying to do. But the

IN ('D', 'H') should work for that portion of it.

HTH

leslie