[AccessD] Why doesn't this work?

Guys,
 Thanks a million for your help, Drew your suggestion worked perfectly..


WBR
Robert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Sunday, July 22, 2007 2:44 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Why doesn't this work?

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