User Defined Query Function Not Working with Multiple Criteria
in [Queries]
|
From: Wayne on 7 Apr 2010 13:59 On Apr 7, 10:22 am, "vanderghast" <vanderghast(a)com> wrote: > As criteria? try: > > MyFunction( ) as computed expression and > IN( "Open", "Close") as criteria, or = "Open" OR "Close" > > Vanderghast, Access MVP > > "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 - 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.
From: John Spencer on 7 Apr 2010 14:12 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 wrote: > 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
From: Wayne on 7 Apr 2010 17:13
On Apr 7, 11:12 am, John Spencer <spen...(a)chpdm.edu> wrote: > 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 wrote: > > 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- Hide quoted text - > > - Show quoted text - Yes John the filter is the way to go. Thanks much. |