[AccessD] Why doesn't this work?

Exactly what I was attempting to say, but you did it better, Drew.

A.

On 7/22/07, Drew Wutka <DWUTKA at marlow.com> wrote:
>
> The problem is that IIF returns a value, and >1 isn't a value.  So when
> the statement is true, ServOption() is returning a value that is being
> used as a criteria.
>
> Ie, ServOption() returns 3 then the WHERE clause looks like this:
>
> WHERE YourField=3
>
> When it returns 0, your getting
>
> WHERE YourField=">1"
>
> And nothing will fit that criteria.
>
> There's two ways you can fix this.  One, change your query to this:
>
> SELECT Table1.SomeNumber
> FROM Table1
> WHERE SomeNumber Like IIf(ServOption()=0,"*",ServOption())
>
> The other option is to have ServOption actually check the field for you.
> The above solution is probably best if ServOption only returns one
> value, but if you want ServOption to return more then one value, the
> next option would work better.  Change ServOption to accept the field as
> an argument, and have it return true or false:
>
> SELECT Table1.SomeNumber
> FROM Table1
> WHERE ServOption([SomeNumber])=True
>
> Drew
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert
> Sent: Sunday, July 22, 2007 1:27 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Why doesn't this work?
>
> Arthur,
> Ok let me try and clear some things up... :-)
>
> IIf(ServOption()=0,>1,ServOption()) is in the criteria area of the saved
> query it's in.  The query is a simple select query that returns records
> of
> past services.
>
> "ServOption()" returns the type of service rendered, (a long value,
> representing of the type of service and it's value can be from 2 to
> whatever.) which the user selected on a form.
>
> There is an option to show all service types or a selected type. The
> selected type works perfectly, but when I need to display all (i.e. >1)
> the
> query displays no records. If I hard code >1 it works fine, showing all
> records...
>
>
> The service type field in the query is numeric..
>
>
> Clear as mud eh... :-)
>
>
> WBR
> Robert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
> Sent: Sunday, July 22, 2007 2:07 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Why doesn't this work?
>
> Just a wag, but that query makes no sense to me. This could be the
> absence
> of information, of course, but what you wrote appears non-logical. Maybe
> you
> mis-typed, but given what you supplied and breaking it down, I end up
> with
> this:
>
> IIf(
>     ServOption()=0,
>     >1,
>     ServOption()
> )
>
> Perhaps that was one accidental keystroke (the ">") -- that's the part
> that
> makes no sense IMO.
>
> If ServOption() is a static function, then something like this might
> possibly work:
>
> (Assume ServOption() currently returns either zero or three, just to
> choose
> some value)
>
> SELECT * FROM mytables
> WHERE ServeOption() = 0
> OR <whatever serveoption refers to> = ServeOption()
>
> Only one of these ORs can exist so I think that covers the bases. I
> could be
> more specific if I knew exactly what criteria ServeOption() was testing,
> but
> that's the general idea.
>
> hth,
> Arthur
>
> On 7/22/07, Robert <robert at servicexp.com> wrote:
> >
> >
> > IIf(ServOption()=0,>1,ServOption())
> >
> > This is the criteria in a query.
> >
> > ServOption is a function that returns a long. The query works when
> > ServOption returns a specific option. However if ServOption returns 0,
>
> > all the records are to be displayed, but doesn't
> >
> >
> >
> >
> > WBR
> > Robert
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> The information contained in this transmission is intended only for the
> person or entity to which it is addressed and may contain II-VI Proprietary
> and/or II-VI BusinessSensitve material. If you are not the intended
> recipient, please contact the sender immediately and destroy the material in
> its entirety, whether electronic or hard copy. You are notified that any
> review, retransmission, copying, disclosure, dissemination, or other use of,
> or taking of any action in reliance upon this information by persons or
> entities other than the intended recipient is prohibited.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>