hello

i have a set of numbers 1-52 in a field. when i query criteria ...between 
[weekvar] and [weekvar]-13....i get the answer i need with the query: a set 
of numbers.

However, when i put this same condition within an iif statement in the query 
criteria, it doesn't work and comes up with no results.

iif([weekvar]>13, between [weekvar] and [weekvar]-13, 100).

The "100" answer will work, but the "between" will not query anything.

What i am trying to do is to return a set of numbers while using iif(.

I don't understand why it'll work when alone in the criteria, but not when 
inside an iif(.

Forgive me for my ignorance, I am an amateur.

Justin T <Justin T@discussions.microsoft.com> wrote:
>i have a set of numbers 1-52 in a field. when i query criteria ...between 
>[weekvar] and [weekvar]-13....i get the answer i need with the query: a set 
>of numbers.
>
>However, when i put this same condition within an iif statement in the query 
>criteria, it doesn't work and comes up with no results.
>
>iif([weekvar]>13, between [weekvar] and [weekvar]-13, 100).
>
>The "100" answer will work, but the "between" will not query anything.
>
>What i am trying to do is to return a set of numbers while using iif(.
>
>I don't understand why it'll work when alone in the criteria, but not when 
>inside an iif(.


It won't work because you have part of the syntax for the
expression inside the IIf and Access can not parse the
expression.  You need to keep the complete expression in one
place.  And don't forget that a criteria must evaluate to
True or False

iif([weekvar]>13, thefield between [weekvar] and
[weekvar]-13, True)

-- 
Marsh
MVP [MS Access]

The following is the equivalent of what you posted.  I'm not sure it is what 
you want, since if you enter any number greater than 13 you will get NO 
records returned if the values in the field range from 1 to 52.

Between IIF([WeekVar]>13,[Weekvar]-13,100) and IIF([WeekVar]>13,[WeekVar],100)

Perhaps what you want is
     Somefield >=IIF([WeekVar]>52,[Weekvar]-13,0)
     and SomeField <=IIF([WeekVar]>52,[WeekVar],100)
which will limit records to a 13 number range or return all records that have 
a value in the field.

If you want all records even if the value is null then you might enter 
criteria like the following
((Between [WeekVar]-13 and [Weekvar]) or [WeekVar]>52)

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

Justin T wrote:
> hello
> 
> i have a set of numbers 1-52 in a field. when i query criteria ...between 
> [weekvar] and [weekvar]-13....i get the answer i need with the query: a set 
> of numbers.
> 
> However, when i put this same condition within an iif statement in the query 
> criteria, it doesn't work and comes up with no results.
> 
> iif([weekvar]>13, between [weekvar] and [weekvar]-13, 100).
> 
> The "100" answer will work, but the "between" will not query anything.
> 
> What i am trying to do is to return a set of numbers while using iif(.
> 
> I don't understand why it'll work when alone in the criteria, but not when 
> inside an iif(.
> 
> Forgive me for my ignorance, I am an amateur.