iif and date range - microsoft.public.access.queries

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,

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]

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, >

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]

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] >

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, > > > >

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)

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)

Similiar Articles:

iif and date range - microsoft.public.access.queries
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...

Date comparison in IIF statement in a query - microsoft.public ...
iif and date range - microsoft.public.access.queries I have following criteria for my query. I wanted different date range if it is Friday or not.

IIf function, & Date(), & AND, & > & < ...
iif and date range - microsoft.public.access.queries IIf function, & Date(), & AND, & > & < ... iif and date range - microsoft.public.access.queries Randbetween function ...

Group by Range in Crosstab Query - microsoft.public.access.queries ...
IIF([Ref To Consult (Days)]>=0 and [Ref To Consult (Days)]<15,"0-14 Days" ,IIF ... I have a crosstab query in which I am prompting for a date range (Between [Enter Start ...

Truble with Date in Query and IIF - microsoft.public.access ...
iif and date range DataBase - DataBase Discussion List Tuesday ... I have following criteria for my query. I wanted different date range if it is Friday or not.

Return a date range based on today's date - microsoft.public ...
iif and date range - microsoft.public.access.queries I wanted different date range if it is Friday or not. ... in which the day of the month is between today's day of ...

Date Range using Between AND - microsoft.public.access.queries ...
iif and date range - microsoft.public.access.queries I have following criteria for my query. I wanted different date range if it is Friday or not. IIf(Weekday(Date())=6 ...

Query Date Range - microsoft.public.access
iif and date range - microsoft.public.access.queries I have following criteria for my query. I wanted different date range if it is Friday or not. IIf(Weekday(Date())=6 ...

Query Based on Time Rane and Date Range - microsoft.public.access ...
iif and date range - microsoft.public.access.queries I have following criteria for my query. I wanted different date range if it is Friday or ... Using Date Range, Based ...

IIF and Like? - microsoft.public.access.queries
iif and date range - microsoft.public.access.queries I am trying to Sum the revenue each tenant provides in a given date range, to give a ... My Expression looks like this ...

iif and date range DataBase - DataBase Discussion List Tuesday ...
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

iif and date range
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

Nested IIF Statement (and Select Case) with Date Ranges ...
Nested IIF Statement (and Select Case) with Date Ranges. Microsoft Access / VBA Forums on Bytes.

Access Help and How-to - Microsoft Office Sum IIF Between a range ...
ms access iif between, sum values for a date range in access, iif date is between, sum access, access range value, criteria access range of values, IIF with date range ...

iif and date range - microsoft.public.access.queries | Microsoft ...
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...

7/26/2012 12:35:11 PM