iif and date range

[Souris]

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,

 

[Marshall Barton]

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)

 

[BruceM]

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.

 

[BruceM]

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.

 

[Souris]

Thnaks millions for helping,

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)

 

[Souris]

Thnaks millions for helping,

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.

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,

 

[Marshall Barton]

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.

 

[BruceM]

OK, thanks for clarifying.

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]

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