If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
iif and between
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. |
#2
|
|||
|
|||
iif and between
|
#3
|
|||
|
|||
iif and between
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. |
Thread Tools | |
Display Modes | |
|
|