iif and between

[Justin T]

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.

 

[Marshall Barton]

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)

 

[John Spencer]

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