User Defined Query Function Not Working with Multiple Criteria
in [Queries]
|
From: Wayne on 7 Apr 2010 11:18 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?
From: Jeff Boyce on 7 Apr 2010 11:38 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. "Wayne" <handyman1600(a)gmail.com> wrote in message news:ed7d5e81-de0c-4d30-8f09-f43453ba9752(a)5g2000yqj.googlegroups.com... > 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?
From: John Spencer on 7 Apr 2010 11:46 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 wrote: > 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?
From: Wayne on 7 Apr 2010 12:12 On Apr 7, 8:38 am, "Jeff Boyce" <nonse...(a)nonsense.com> wrote: > 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. > > "Wayne" <handyman1...(a)gmail.com> wrote in message > > news:ed7d5e81-de0c-4d30-8f09-f43453ba9752(a)5g2000yqj.googlegroups.com... > > > > > 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?- Hide quoted text - > > - 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
From: vanderghast on 7 Apr 2010 13:22
As criteria? try: MyFunction( ) as computed expression and IN( "Open", "Close") as criteria, or = "Open" OR "Close" Vanderghast, Access MVP "Wayne" <handyman1600(a)gmail.com> wrote in message news:ed7d5e81-de0c-4d30-8f09-f43453ba9752(a)5g2000yqj.googlegroups.com... > 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? |