MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### iif and date range

• Follow

```I have following criteria for my query.

I wanted different date range if it is Friday or not.

IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between
Day(Date()) And Day(Date()+1))

I tested bith criteria are working, but it does not work when I added the iif.

Are there anything wrong when I add iif to specify different date range?

```
 0

```Souris wrote:

>I have following criteria for my query.
>
>I wanted different date range if it is Friday or not.
>
>IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between
>Day(Date()) And Day(Date()+1))
>
>I tested bith criteria are working, but it does not work when I added the iif.
>
>Are there anything wrong when I add iif to specify different date range?

The problem is that you can not use an operator (in this
case Between ... And ,,,) conditionally.  I.e operators must
be outside the IIf(...)

Try this:

Between Day(Date()) And
Day(Date()+IIf(Weekday(Date())=6,2,1)

--
Marsh
MVP [MS Access]
```
 0

```What exactly are you trying to do, and to what field do you wish to apply
the criteria?  If you are applying the criteria to a time/date field you may
need to do something like:
IIf(Weekday(Date())=6,Day([SomeField]) Between Day(Date()) And
Day(Date()+2),Day([SomeField]) Between Day(Date()) And Day(Date()+1))
If today is Friday this will look for all records in which the day of the
month is between today's day of the month and the day of the month two days
from now; otherwise add one day to the date.  This being February 7, you are
looking for all records in which the day of the month is between 7 and 8.
If you want to specify a date range, leave out the Day function, which
returns the day of the month.

"Souris" <Souris@discussions.microsoft.com> wrote in message
news:8B518813-95FF-4244-A2D3-0E84C41BD9CA@microsoft.com...
>I have following criteria for my query.
>
> I wanted different date range if it is Friday or not.
>
> IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between
> Day(Date()) And Day(Date()+1))
>
> I tested bith criteria are working, but it does not work when I added the
> iif.
>
> Are there anything wrong when I add iif to specify different date range?
>
> Your help is great appreciated,
>

```
 0

```I had thought that was the case with Between...And, but I checked Access
2003 help, which shows this example for the Between...And Operator topic:
SELECT IIf(PostalCode Between 98101 And 98199, "Local", "Nonlocal")
Is it that the operator does not work for the "Then" and "Else" parts of the
IIf, but it's OK for the logical test part?
It seems to me in any case that the test as it stands needs to be applied to
an integer field, but then I sort of assumed a date field, which may not be
the case.

"Marshall Barton" <marshbarton@wowway.com> wrote in message
news:qddmq39j7tnvk5149qmjhjpstnplaak8sp@4ax.com...
> Souris wrote:
>
>>I have following criteria for my query.
>>
>>I wanted different date range if it is Friday or not.
>>
>>IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between
>>Day(Date()) And Day(Date()+1))
>>
>>I tested bith criteria are working, but it does not work when I added the
>>iif.
>>
>>Are there anything wrong when I add iif to specify different date range?
>
>
> The problem is that you can not use an operator (in this
> case Between ... And ,,,) conditionally.  I.e operators must
> be outside the IIf(...)
>
> Try this:
>
> Between Day(Date()) And
> Day(Date()+IIf(Weekday(Date())=6,2,1)
>
> --
> Marsh
> MVP [MS Access]

```
 0

```Thnaks millions for helping,

"Marshall Barton" wrote:

> Souris wrote:
>
> >I have following criteria for my query.
> >
> >I wanted different date range if it is Friday or not.
> >
> >IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between
> >Day(Date()) And Day(Date()+1))
> >
> >I tested bith criteria are working, but it does not work when I added the iif.
> >
> >Are there anything wrong when I add iif to specify different date range?
>
>
> The problem is that you can not use an operator (in this
> case Between ... And ,,,) conditionally.  I.e operators must
> be outside the IIf(...)
>
> Try this:
>
> Between Day(Date()) And
> Day(Date()+IIf(Weekday(Date())=6,2,1)
>
> --
> Marsh
> MVP [MS Access]
>
```
 0

```Thnaks millions for helping,

"BruceM" wrote:

> What exactly are you trying to do, and to what field do you wish to apply
> the criteria?  If you are applying the criteria to a time/date field you may
> need to do something like:
> IIf(Weekday(Date())=6,Day([SomeField]) Between Day(Date()) And
> Day(Date()+2),Day([SomeField]) Between Day(Date()) And Day(Date()+1))
> If today is Friday this will look for all records in which the day of the
> month is between today's day of the month and the day of the month two days
> from now; otherwise add one day to the date.  This being February 7, you are
> looking for all records in which the day of the month is between 7 and 8.
> If you want to specify a date range, leave out the Day function, which
> returns the day of the month.
>
> "Souris" <Souris@discussions.microsoft.com> wrote in message
> news:8B518813-95FF-4244-A2D3-0E84C41BD9CA@microsoft.com...
> >I have following criteria for my query.
> >
> > I wanted different date range if it is Friday or not.
> >
> > IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between
> > Day(Date()) And Day(Date()+1))
> >
> > I tested bith criteria are working, but it does not work when I added the
> > iif.
> >
> > Are there anything wrong when I add iif to specify different date range?
> >
> > Your help is great appreciated,
> >
>
>
```
 0

```What I said was context specific to the OP's criteria
expression.  More complete would be that the operands and
operator must all be inside the IIf or only the operands can
be inside an IIf.

Another way to say it that you can not use syntax
conditionally.
--
Marsh
MVP [MS Access]

BruceM wrote:
>I had thought that was the case with Between...And, but I checked Access
>2003 help, which shows this example for the Between...And Operator topic:
>SELECT IIf(PostalCode Between 98101 And 98199, "Local", "Nonlocal")
>Is it that the operator does not work for the "Then" and "Else" parts of the
>IIf, but it's OK for the logical test part?
>It seems to me in any case that the test as it stands needs to be applied to
>an integer field, but then I sort of assumed a date field, which may not be
>the case.
>
>"Marshall Barton"  wrote
>> Souris wrote:
>>
>>>I have following criteria for my query.
>>>
>>>I wanted different date range if it is Friday or not.
>>>
>>>IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between
>>>Day(Date()) And Day(Date()+1))
>>>
>>>I tested bith criteria are working, but it does not work when I added the
>>>iif.
>>>
>>>Are there anything wrong when I add iif to specify different date range?
>>
>>
>> The problem is that you can not use an operator (in this
>> case Between ... And ,,,) conditionally.  I.e operators must
>> be outside the IIf(...)
>>
>> Try this:
>>
>> Between Day(Date()) And
>> Day(Date()+IIf(Weekday(Date())=6,2,1)
```
 0

```OK, thanks for clarifying.

"Marshall Barton" <marshbarton@wowway.com> wrote in message
news:ndhmq3ppoimfokd43328m6np0cnbedna62@4ax.com...
> What I said was context specific to the OP's criteria
> expression.  More complete would be that the operands and
> operator must all be inside the IIf or only the operands can
> be inside an IIf.
>
> Another way to say it that you can not use syntax
> conditionally.
> --
> Marsh
> MVP [MS Access]
>
>
> BruceM wrote:
>>I had thought that was the case with Between...And, but I checked Access
>>2003 help, which shows this example for the Between...And Operator topic:
>>SELECT IIf(PostalCode Between 98101 And 98199, "Local", "Nonlocal")
>>Is it that the operator does not work for the "Then" and "Else" parts of
>>the
>>IIf, but it's OK for the logical test part?
>>It seems to me in any case that the test as it stands needs to be applied
>>to
>>an integer field, but then I sort of assumed a date field, which may not
>>be
>>the case.
>>
>>"Marshall Barton"  wrote
>>> Souris wrote:
>>>
>>>>I have following criteria for my query.
>>>>
>>>>I wanted different date range if it is Friday or not.
>>>>
>>>>IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between
>>>>Day(Date()) And Day(Date()+1))
>>>>
>>>>I tested bith criteria are working, but it does not work when I added
>>>>the
>>>>iif.
>>>>
>>>>Are there anything wrong when I add iif to specify different date range?
>>>
>>>
>>> The problem is that you can not use an operator (in this
>>> case Between ... And ,,,) conditionally.  I.e operators must
>>> be outside the IIf(...)
>>>
>>> Try this:
>>>
>>> Between Day(Date()) And
>>> Day(Date()+IIf(Weekday(Date())=6,2,1)

```
 0

7 Replies
320 Views

Similiar Articles:

7/26/2012 12:35:11 PM