dynamic criteria being ignored by query

#1

Hello all;
Access 2000 sql server backend.

I am trying to implement dynamic criteria into a query instead of hard-
coding a range. It is a date/time field. I want the query to retrieve
a range of data based on what day of the week it is. I tried the
following in the criteria of this date/time field:

IIf(Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday")

Quote:

=Date()-5,IIf(Format(Now(),"DDDD")in ("Thursday","Friday")
=Date()-3))

The query ignores it and retrieves all the data.

If I hard code it, it will work, like the following: >=Date()-5

Anybody know why my "IIF" doesn't work in the query criteria?

What can I do to make it work?

Thanks in advance for any assistance.

Tony

#2

How are you calling your query? From vba code?
Can you use:

If Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday") Then
DoCmd.RunSQL "Select....[your stuff]...>=#" & Date()-5 & "#"
Else
DoCmd.RunSQL "Select....[your stuff]...>=#" & Date()-3 & "#"
Endif

Fred

On Apr 5, 5:16*am, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:

Quote:

Hello all;
Access 2000 sql server backend.

I am trying to implement dynamic criteria into a query instead of hard-
coding a range. It is a date/time field. I want the query to retrieve
a range of data based on what day of the week it is. I tried the
following in the criteria of this date/time field:

* * *IIf(Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday")
* * *>=Date()-5,IIf(Format(Now(),"DDDD")in ("Thursday","Friday")
* * *>=Date()-3))

The query ignores it and retrieves all the data.

If I hard code it, it will work, like the following: >=Date()-5

Anybody know why my "IIF" doesn't work in the query criteria?

What can I do to make it work?

Thanks in advance for any assistance.

Tony

#3

On Apr 5, 10:52*am, "zuckerm... (AT) gmail (DOT) com" <zuckerm... (AT) gmail (DOT) com>
wrote:

Quote:

How are you calling your query? From vba code?
Can you use:

If Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday") Then
* *DoCmd.RunSQL "Select....[your stuff]...>=#" & Date()-5 & "#"
Else
* *DoCmd.RunSQL "Select....[your stuff]...>=#" & Date()-3 & "#"
Endif

Fred

On Apr 5, 5:16*am, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:

Hello all;
Access 2000 sql server backend.

I am trying to implement dynamic criteria into a query instead of hard-
coding a range. It is a date/time field. I want the query to retrieve
a range of data based on what day of the week it is. I tried the
following in the criteria of this date/time field:

* * *IIf(Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday")
* * *>=Date()-5,IIf(Format(Now(),"DDDD")in ("Thursday","Friday")
* * *>=Date()-3))

The query ignores it and retrieves all the data.

If I hard code it, it will work, like the following: >=Date()-5

Anybody know why my "IIF" doesn't work in the query criteria?

What can I do to make it work?

Thanks in advance for any assistance.

Tony- Hide quoted text -

- Show quoted text -

Thanks for the reply. I am running this from a query object--utilizing
the criteria "box" in the field column. That is the way I am trying to
do it because most of the access programs here don't utilize vba
modules

#4

You can try the following in a query.

Field: SomeDateField
Criteria: >= Date() - IIF(WeekDay(Date(),1) in (2,3,4),5,3)

If you insist on using the format function. Then

Criteria: >= Date() - IIF(Format(Now()),"DDDD") IN
("Monday","Tuesday","Wednesday"),5,3)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 4/5/2011 10:57 AM, Tony_E wrote:

Quote:

On Apr 5, 10:52 am, "zuckerm... (AT) gmail (DOT) com"<zuckerm... (AT) gmail (DOT) com
wrote:
How are you calling your query? From vba code?
Can you use:

If Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday") Then
DoCmd.RunSQL "Select....[your stuff]...>=#"& Date()-5& "#"
Else
DoCmd.RunSQL "Select....[your stuff]...>=#"& Date()-3& "#"
Endif

Fred

On Apr 5, 5:16 am, Tony_E<bluestealt... (AT) hotmail (DOT) com> wrote:

Hello all;
Access 2000 sql server backend.

I am trying to implement dynamic criteria into a query instead of hard-
coding a range. It is a date/time field. I want the query to retrieve
a range of data based on what day of the week it is. I tried the
following in the criteria of this date/time field:

IIf(Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday")
=Date()-5,IIf(Format(Now(),"DDDD")in ("Thursday","Friday")
=Date()-3))

The query ignores it and retrieves all the data.

If I hard code it, it will work, like the following:>=Date()-5

Anybody know why my "IIF" doesn't work in the query criteria?

What can I do to make it work?

Thanks in advance for any assistance.

Tony- Hide quoted text -

- Show quoted text -

Thanks for the reply. I am running this from a query object--utilizing
the criteria "box" in the field column. That is the way I am trying to
do it because most of the access programs here don't utilize vba
modules

#5

On Apr 5, 11:36*am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:

Quote:

You can try the following in a query.

Field: SomeDateField
Criteria: >= Date() - IIF(WeekDay(Date(),1) in (2,3,4),5,3)

If you insist on using the format function. *Then

Criteria: >= Date() - IIF(Format(Now()),"DDDD") IN
("Monday","Tuesday","Wednesday"),5,3)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 4/5/2011 10:57 AM, Tony_E wrote:

On Apr 5, 10:52 am, "zuckerm... (AT) gmail (DOT) com"<zuckerm... (AT) gmail (DOT) com
wrote:
How are you calling your query? From vba code?
Can you use:

If Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday") Then
* * DoCmd.RunSQL "Select....[your stuff]...>=#"& *Date()-5& *"#"
Else
* * DoCmd.RunSQL "Select....[your stuff]...>=#"& *Date()-3& *"#"
Endif

Fred

On Apr 5, 5:16 am, Tony_E<bluestealt... (AT) hotmail (DOT) com> *wrote:

Hello all;
Access 2000 sql server backend.

I am trying to implement dynamic criteria into a query instead of hard-
coding a range. It is a date/time field. I want the query to retrieve
a range of data based on what day of the week it is. I tried the
following in the criteria of this date/time field:

* * * IIf(Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday")
* * * >=Date()-5,IIf(Format(Now(),"DDDD")in ("Thursday","Friday")
* * * >=Date()-3))

The query ignores it and retrieves all the data.

If I hard code it, it will work, like the following:>=Date()-5

Anybody know why my "IIF" doesn't work in the query criteria?

What can I do to make it work?

Thanks in advance for any assistance.

Tony- Hide quoted text -

- Show quoted text -

Thanks for the reply. I am running this from a query object--utilizing
the criteria "box" in the field column. That is the way I am trying to
do it because most of the access programs here don't utilize vba
modules- Hide quoted text -

- Show quoted text -

Thank you very much, that worked!

#6

Tony_E <bluestealth94 (AT) hotmail (DOT) com> wrote in
news:5c35353f-1827-4170-bc4e-57826cf3522c (AT) d28g2000yqf (DOT) googlegroups.co
m:

Quote:

I am trying to implement dynamic criteria into a query instead of
hard- coding a range. It is a date/time field. I want the query to
retrieve a range of data based on what day of the week it is. I
tried the following in the criteria of this date/time field:

IIf(Format(Now(),"DDDD")in ("Monday","Tuesday","Wednesday")
=Date()-5,IIf(Format(Now(),"DDDD")in ("Thursday","Friday")
=Date()-3))

The query ignores it and retrieves all the data.

Are there any OR's in the WHERE clause? If so, you might need to use
parentheses to get the right result. For instance:

WHERE condition1 OR condition2 AND condition 3

....will be unpredictable as to what it will retrieve. It could
alternatively be interpreted in either of these two ways:

WHERE (condition1 OR condition2) AND condition 3
WHERE condition1 OR (condition2 AND condition 3)

The parentheses are essential to insure that it gets interpreted in
the way you want it to be interpreted.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#7

According to the Help file, the precedence of boolean operators is Not, And,
Or, Xor, Eqv, Imp

Doesn't that mean that

WHERE condition1 OR condition2 AND condition 3

will always be evaluated at

WHERE condition1 OR (condition2 AND condition 3)

?

"David-W-Fenton" wrote in message
news:Xns9EBF871C0F996f99a49ed1d0c49c5bbb2 (AT) 74 (DOT) 209.136.92...

Are there any OR's in the WHERE clause? If so, you might need to use
parentheses to get the right result. For instance:

WHERE condition1 OR condition2 AND condition 3

....will be unpredictable as to what it will retrieve. It could
alternatively be interpreted in either of these two ways:

WHERE (condition1 OR condition2) AND condition 3
WHERE condition1 OR (condition2 AND condition 3)

The parentheses are essential to insure that it gets interpreted in
the way you want it to be interpreted.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#8

"Douglas J Steele" <NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote in
news:inlab8$jk6$1 (AT) dont-email (DOT) me:

Quote:

According to the Help file, the precedence of boolean operators is
Not, And, Or, Xor, Eqv, Imp

Doesn't that mean that

WHERE condition1 OR condition2 AND condition 3

will always be evaluated at

WHERE condition1 OR (condition2 AND condition 3)

I don't know. I wouldn't take a chance. I am very much against
relying on implicit behaviors when you can specify what you want
explicitly and then be entirely certain what will happen.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#9

No argument from me on that!

"David-W-Fenton" wrote in message
news:Xns9EC2B0AB8E45Bf99a49ed1d0c49c5bbb2 (AT) 74 (DOT) 209.136.89...

I am very much against
relying on implicit behaviors when you can specify what you want
explicitly and then be entirely certain what will happen.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

dynamic criteria being ignored by query

comp.databases.ms-access comp.databases.ms-access

dynamic criteria being ignored by query - 04-05-2011 , 07:16 AM

Re: dynamic criteria being ignored by query - 04-05-2011 , 09:52 AM

Re: dynamic criteria being ignored by query - 04-05-2011 , 09:57 AM

Re: dynamic criteria being ignored by query - 04-05-2011 , 10:36 AM

Re: dynamic criteria being ignored by query - 04-05-2011 , 10:46 AM

Re: dynamic criteria being ignored by query - 04-06-2011 , 12:16 PM

Re: dynamic criteria being ignored by query - 04-07-2011 , 04:29 PM

Re: dynamic criteria being ignored by query - 04-09-2011 , 04:22 PM

Re: dynamic criteria being ignored by query - 04-10-2011 , 08:12 AM