Groups | Search | Server Info | Login | Register

Groups > comp.databases.ms-access > #818

Re: dynamic criteria being ignored by query

Show all headers | View raw

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...@gmail.com"<zuckerm...@gmail.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...@hotmail.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

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

dynamic criteria being ignored by query Tony_E <bluestealth94@hotmail.com> - 2011-04-05 05:16 -0700
  Re: dynamic criteria being ignored by query "zuckermanf@gmail.com" <zuckermanf@gmail.com> - 2011-04-05 07:52 -0700
    Re: dynamic criteria being ignored by query Tony_E <bluestealth94@hotmail.com> - 2011-04-05 07:57 -0700
      Re: dynamic criteria being ignored by query John Spencer <JSPENCER@Hilltop.umbc> - 2011-04-05 11:36 -0400
        Re: dynamic criteria being ignored by query Tony_E <bluestealth94@hotmail.com> - 2011-04-05 08:46 -0700
  Re: dynamic criteria being ignored by query "David-W-Fenton" <NoEmail@SeeSignature.invalid> - 2011-04-06 17:16 +0000
    Re: dynamic criteria being ignored by query "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> - 2011-04-07 17:29 -0400
      Re: dynamic criteria being ignored by query "David-W-Fenton" <NoEmail@SeeSignature.invalid> - 2011-04-09 21:22 +0000
        Re: dynamic criteria being ignored by query "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> - 2011-04-10 09:12 -0400

csiph-web