By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,307 Members | 1,585 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,307 IT Pros & Developers. It's quick & easy.

IIF Cirtiera in a qury

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
jo***********@socal.rr.com (Josh Armstrong) wrote in
news:b6**************************@posting.google.c om:
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


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

Nov 12 '05 #2

P: n/a
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 <bq******@generation.net> wrote in message news:<6a******************************@news.terane ws.com>...
jo***********@socal.rr.com (Josh Armstrong) wrote in
news:b6**************************@posting.google.c om:
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


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

Nov 12 '05 #3

P: n/a
jo***********@socal.rr.com (Josh Armstrong) wrote in
news:b6*************************@posting.google.co m:
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?
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

Bob Quintal <bq******@generation.net> wrote in message
news:<6a******************************@news.terane ws.com>...
jo***********@socal.rr.com (Josh Armstrong) wrote in
news:b6**************************@posting.google.c om:
> 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
>


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


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.