IIF statement in criteria?

  • Thread starter Thread starter Harmony
  • Start date Start date
H

Harmony

Is it possible to have a criteria in a query be
conditional? I want to limit my criteria dependent on a
value in a different field. Can I do this? If so, how?

Thanks!

Harmony
 
I am working with Loan Applications and I am trying to
only view the applications for the prior week. Mostly this
process is run on every Monday, but if the Monday is a
holiday, it is then done on Tuesday. This is the IIF
statement that I was putting in the criteria of the
Application_Date field, but I returned no results:

IIF(Weekday([System_Date])<>2,Between [System_Date]-9 and
[System_Date]-2,Between [System_Date]-10 and [System_Date]-
3)

What am I doing wrong?

Thanks!
 
You can't use Between in IIf statements.

Assuming you're doing this in the query builder grid, add a computed field
Weekday([System_Date]) to the grid. On one criteria row, put Between
[System_Date]-9 and [System_Date]-2 under Application_Date, and <> 2 under
the computed field. On another criteria row, put Between [System_Date]-10
and [System_Date]- 3 under Application_Date, and = 2 under the computed
field.

Another possibility (probably better in this case) is just to put Between
[System_Date]-(10 + Weekday([System_Date] <> 2) and [System_Date]- (3 +
Weekday([System_Date] <> 2)

This uses the fact that a boolean statement that is True is equal to -1,
whereas False is 0.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Harmony said:
I am working with Loan Applications and I am trying to
only view the applications for the prior week. Mostly this
process is run on every Monday, but if the Monday is a
holiday, it is then done on Tuesday. This is the IIF
statement that I was putting in the criteria of the
Application_Date field, but I returned no results:

IIF(Weekday([System_Date])<>2,Between [System_Date]-9 and
[System_Date]-2,Between [System_Date]-10 and [System_Date]-
3)

What am I doing wrong?

Thanks!




-----Original Message-----
Yes, what are you trying to do?

P





.
Click to expand...
Click to expand...
 
Try Criteria Like:

Between DateAdd("d",-6-WeekDay(Date()),Date()) AND
DateAdd("d",-0-Weekday(Date()),Date())

You may have to adjust the -6 and -0 depending on which day of the week you
start and end the week on. This should give you Sunday to Saturday.
I am working with Loan Applications and I am trying to
only view the applications for the prior week. Mostly this
process is run on every Monday, but if the Monday is a
holiday, it is then done on Tuesday. This is the IIF
statement that I was putting in the criteria of the
Application_Date field, but I returned no results:

IIF(Weekday([System_Date])<>2,Between [System_Date]-9 and
[System_Date]-2,Between [System_Date]-10 and [System_Date]-
3)

What am I doing wrong?

Thanks!
-----Original Message-----
Yes, what are you trying to do?

P





.
Click to expand...
Click to expand...
 
Back
Top