User Defined Query Function Not Working with Multiple Criteria

[Wayne]

Using Access 2007 I created a user defined function used in a query
that works fine on single criteria but not two or more.

These work fine:
MyFunction="Open"
MyFunction="Closed

These don't work:
MyFunction="Open Or Closed"
MyFunction="'Open' Or 'Closed'"

What do I need to type to get this to work?

 

[Jeff Boyce]

Unless you let us know something specific about the function itself, it's
going to be difficult to diagnose why it isn't working.

Consider posting the function ... (and wouldn't the function spell out how
many parameters it requires? If it calls for one, it shouldn't work when
you feed it two...)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

Care to post your function?

Normally, if you want to pass multiple values into a function you will use a
paramArray to get the values and then step through the array of values

If you have done that then you would call the function with
MyFunction("Open","Closed")


For example, this function returns the average of the values passed in.

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least one number in the group of values
fRowAverage = dblSum / intElementCount

Else 'No number in the group of values
fRowAverage = Null
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Wayne]

Unless you let us know something specific about the function itself, it's
going to be difficult to diagnose why it isn't working.

Consider posting the function ... (and wouldn't the function spell out how
many parameters it requires?  If it calls for one, it shouldn't work when
you feed it two...)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.









- Show quoted text -

From a form I choose which items (Open, Closed or Open & Closed) I
want to display. I then click the cmdPrintReport_Click button which
determines what items to view.

Private Sub cmdPrintReport_Click()

Dim stDocName As String

' get Option Button value
MyOption = Me.frameMyOption.Value

' run MyFunction
MyFunction

DoCmd.OpenReport "rptMyReport", acViewNormal

End Sub


Public Function MyFunction()
' get option from form

In the query Status field is the function MyFunction()

If MyOption = 1 Then
MyFunction = "Open Or Closed"
ElseIf MyOption = 2 Then
MyFunction = "Open"
Else
MyFunction = "Closed"
End If

End Function

 

[vanderghast]

As criteria? try:

MyFunction( ) as computed expression and
IN( "Open", "Close") as criteria, or = "Open" OR "Close"



Vanderghast, Access MVP

 

[Wayne]

As criteria?   try:

MyFunction( )      as computed expression and
IN( "Open", "Close")   as criteria, or    = "Open" OR "Close"

Vanderghast, Access MVP









- Show quoted text -

Yes, IN("Open","Closed") , "Open" Or "Closed" work if typed directly
in the query but I want to choose from a form which one to show.

 

[John Spencer]

It seems as if you want something along the lines of

Private Sub cmdPrintReport_Click()

Dim stDocName As String
Dim stFilter as String

' get Option Button value
SELECT CASE Me.frameMyOption.Value
Case 1
stFilter = "[SomeFieldName] in ('Open','Close')"
Case 2
stFilter = "[SomeFieldName] = 'Open'"
Case 3
stFilter = "[SomeFieldName] = 'Close'"

END SELECT

DoCmd.OpenReport "rptMyReport", acViewNormal,,stFilter

End Sub

Remove the criteria from the report's source query.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Wayne]

It seems as if you want something along the lines of

Private Sub cmdPrintReport_Click()

Dim stDocName As String
Dim stFilter as String

' get Option Button value
SELECT CASE Me.frameMyOption.Value
   Case 1
      stFilter = "[SomeFieldName] in ('Open','Close')"
   Case 2
      stFilter = "[SomeFieldName] = 'Open'"
   Case 3
       stFilter = "[SomeFieldName] = 'Close'"

END SELECT

   DoCmd.OpenReport "rptMyReport", acViewNormal,,stFilter

End Sub

Remove the criteria from the report's source query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County



- Show quoted text -

Yes John the filter is the way to go. Thanks much.