iif and between

  • Thread starter Thread starter Justin T
  • Start date Start date
J

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.
 
Justin T said:
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(.
Click to expand...


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)
 
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
 
Hello everyone,
I'm new to the forum and I have very irritating question.

I want to run IIF statement that includes calculation as part of the expr.

First I want to chek if the Item name contian "SHS" or "SCS" then / 200
and if the [Item] contains "S260" or "SFS" then / 170

I know I'm not clear, but when I run the query it only validate the 1st part, and divide all by 200

Can someone help please, it will be very appreciated.
 
Back
Top