IIF Cirtiera in a qury

I have a option group(ogpLimiter) with 3 choices on my
form(SearchMaster), "All"=3, "Expired"=2 and "Valid"=1. I would like
to set it so that when the user selects one of the options it will
modify a query, to that on of my list boxes only shows what was
selected.

Ideally it would look like this,
IIf([forms]![SearchMaster]![ogpLimiter]="1",<Date(),IIf([forms]![SearchMaster]![ogpLimiter]="2",>Date(),IIf([forms]![SearchMaster]![ogpLimiter]="3",*,"")))

The field is it limiting is a date field called ExpDate.

If I try to us the above I get an error stating the query is to
complex

re: IIF Cirtiera in a qury

josharmstrong@socal.rr.com (Josh Armstrong) wrote in
news:b6d4ec56.0405071659.6cd9c46a@posting.google.c om:
[color=blue]
> I have a option group(ogpLimiter) with 3 choices on my
> form(SearchMaster), "All"=3, "Expired"=2 and "Valid"=1. I
> would like to set it so that when the user selects one of the
> options it will modify a query, to that on of my list boxes
> only shows what was selected.
>
> Ideally it would look like this,
> IIf([forms]![SearchMaster]![ogpLimiter]="1",<Date(),IIf([forms]
> ![SearchMaster]![ogpLimiter]="2",>Date(),IIf([forms]![SearchMas
> ter]![ogpLimiter]="3",*,"")))
>
> The field is it limiting is a date field called ExpDate.
>
> If I try to us the above I get an error stating the query is
> to complex
>[/color]

Try this instead. Create two expression fields in the query.

expired: datefield <= date()
Valid: datefield > date()

In the criteria row of the query, put the following expressions

for valid, put ([forms]![SearchMaster]![ogpLimiter] AND 1 = 1)
for Expired, it's ([forms]![SearchMaster]![ogpLimiter] AND 2 =2)


Explanation: the two calculated fields compare the datefield date
and return true or false, the criteria perform bitwise
comparisons of the numbers, so if ogplimiter is 1, valid passes
the test, if 2, expired does, and if three, both pass the test
and you get all the records.

Bob Quintal

re: IIF Cirtiera in a qury

I get an ODBC--Call Failed when I try to run it with the below items
Valid, put ([forms]![SearchMaster]![ogpLimiter] AND 1 = 1)
Expired, it's ([forms]![SearchMaster]![ogpLimiter] AND 2 =2)

Any Ideas?


Bob Quintal <bquintal@generation.net> wrote in message news:<6afcb4a5554c49af48700f225bc5210d@news.terane ws.com>...[color=blue]
> josharmstrong@socal.rr.com (Josh Armstrong) wrote in
> news:b6d4ec56.0405071659.6cd9c46a@posting.google.c om:
>[color=green]
> > I have a option group(ogpLimiter) with 3 choices on my
> > form(SearchMaster), "All"=3, "Expired"=2 and "Valid"=1. I
> > would like to set it so that when the user selects one of the
> > options it will modify a query, to that on of my list boxes
> > only shows what was selected.
> >
> > Ideally it would look like this,
> > IIf([forms]![SearchMaster]![ogpLimiter]="1",<Date(),IIf([forms]
> > ![SearchMaster]![ogpLimiter]="2",>Date(),IIf([forms]![SearchMas
> > ter]![ogpLimiter]="3",*,"")))
> >
> > The field is it limiting is a date field called ExpDate.
> >
> > If I try to us the above I get an error stating the query is
> > to complex
> >[/color]
>
> Try this instead. Create two expression fields in the query.
>
> expired: datefield <= date()
> Valid: datefield > date()
>
> In the criteria row of the query, put the following expressions
>
> for valid, put ([forms]![SearchMaster]![ogpLimiter] AND 1 = 1)
> for Expired, it's ([forms]![SearchMaster]![ogpLimiter] AND 2 =2)
>
>
> Explanation: the two calculated fields compare the datefield date
> and return true or false, the criteria perform bitwise
> comparisons of the numbers, so if ogplimiter is 1, valid passes
> the test, if 2, expired does, and if three, both pass the test
> and you get all the records.
>
> Bob Quintal[/color]

re: IIF Cirtiera in a qury

josharmstrong@socal.rr.com (Josh Armstrong) wrote in
news:b6d4ec56.0405102107.e171d26@posting.google.co m:
[color=blue]
> I get an ODBC--Call Failed when I try to run it with the below
> items Valid, put ([forms]![SearchMaster]![ogpLimiter] AND 1 =
> 1) Expired, it's ([forms]![SearchMaster]![ogpLimiter] AND 2
> =2)
>
> Any Ideas?
>[/color]
ODBC call???

OK, try this instead
expired: (datefield <= date() ) *-2
Valid: (datefield > date()) *-1
All: 3


Put the following in the criteria under each field,
each in a separate row
CInt([forms]![searchmaster]![ogpLimiter])

Bob Quintal


[color=blue]
>
> Bob Quintal <bquintal@generation.net> wrote in message
> news:<6afcb4a5554c49af48700f225bc5210d@news.terane ws.com>...[color=green]
>> josharmstrong@socal.rr.com (Josh Armstrong) wrote in
>> news:b6d4ec56.0405071659.6cd9c46a@posting.google.c om:
>>[color=darkred]
>> > I have a option group(ogpLimiter) with 3 choices on my
>> > form(SearchMaster), "All"=3, "Expired"=2 and "Valid"=1. I
>> > would like to set it so that when the user selects one of
>> > the options it will modify a query, to that on of my list
>> > boxes only shows what was selected.
>> >
>> > Ideally it would look like this,
>> > IIf([forms]![SearchMaster]![ogpLimiter]="1",<Date(),IIf([for
>> > ms]
>> > ![SearchMaster]![ogpLimiter]="2",>Date(),IIf([forms]![Search
>> > Mas ter]![ogpLimiter]="3",*,"")))
>> >
>> > The field is it limiting is a date field called ExpDate.
>> >
>> > If I try to us the above I get an error stating the query
>> > is to complex
>> >[/color]
>>
>> Try this instead. Create two expression fields in the query.
>>
>> expired: datefield <= date()
>> Valid: datefield > date()
>>
>> In the criteria row of the query, put the following
>> expressions
>>
>> for valid, put ([forms]![SearchMaster]![ogpLimiter] AND 1 =
>> 1) for Expired, it's ([forms]![SearchMaster]![ogpLimiter] AND
>> 2 =2)
>>
>>
>> Explanation: the two calculated fields compare the datefield
>> date and return true or false, the criteria perform bitwise
>> comparisons of the numbers, so if ogplimiter is 1, valid
>> passes the test, if 2, expired does, and if three, both pass
>> the test and you get all the records.
>>
>> Bob Quintal[/color]
>[/color]