IIF in criteria? (1 Viewer)

[David R]

I'm trying to make my query robust enough to count differently on Friday than Monday, but it keeps evaluating the criterion to '2' no matter what. For now I have to switch between two versions, one for M-Th and one for Fridays.

Relevant field and criterion: [Forms]![Data]![txtInspDate]-[Activity Created]-[AddDays] > IIf(Weekday([Forms]![Data]![txtInspDate])=2,2,0)

Full SQL if necessary:

SELECT TOP 13 ImportTemp.[Empl ID], ImportTemp.[Inspection Activity], ImportTemp.[SO ID], [Forms]![Data]![txtInspDate]-[Activity Created]-[AddDays] AS DPDMax, IIf(Weekday([Forms]![Data]![txtInspDate])=2,2,0) AS Weekd
FROM [Priority List] INNER JOIN ImportTemp ON [Priority List].Activity = ImportTemp.[Inspection Activity]
WHERE (((ImportTemp.[Empl ID])=[Forms]![formReview]![eid]) AND ((ImportTemp.[Inspection Activity]) Like "*inspect*") AND (([Forms]![Data]![txtInspDate]-[Activity Created]-[AddDays])>IIf(Weekday([Forms]![Data]![txtInspDate])=2,2,0)) AND ((ImportTemp.[Provider Group])<>"NCS-HazardousStructure"))
ORDER BY [Priority List].Priority, [Forms]![Data]![txtInspDate]-[Activity Created]-[AddDays] DESC;

Search is failing me due to the common terms.

 

[namliam]

What is it you are trying to accomplish?

 

[David R]

It's counting how many days have elapsed since the Inspection was entered. AddDays is how long before a new inspection 'expires', with the caveat that they always get 1 business day.

So Tuesday-Friday, [Forms]![Data]![txtInspDate]-[Activity Created]-[AddDays] just needs to be > 0. If I'm pulling the list for Monday, I need it to ignore anything created on Saturday or Sunday last, because the administrative staff won't have touched the case yet.

I can't just use networkday() because I'm only ignoring the FIRST weekend.

 

[namliam]

See if anything from this thread helps you along
http://www.access-programmers.co.uk/forums/showthread.php?t=254716&highlight=workday

 

[David R]

The problem is not the Weekend days, as I said above. It's whether or not criteria can include the IIF statement, which throws no error but always evaluates to TRUE (2, in this case).

However this time search turned up http://www.access-programmers.co.uk/forums/showthread.php?t=35942 which explains why (IIF is not an operator).

Guess I'll have to go the VBA route...

 

[namliam]

The only reason you are trying to make a difference from friday to monday is because of the weekend days.... Yes the weekend IS indeed important.

If you "simply" want to "fake" anything created in the weekend as beeing created on the next monday... I.e. Skip the first weekend. You can do that in a simple oneline function inside a query.
This will move your date to the next monday, based on which you can then calculate the difference.
[YourDate] - Datepart("W", [Yourdate],vbMonday,vbFirstFourDays) + 8