How to use OR within an iif value

[BFauska]

I'm trying to create a query where the results are filtered differently depending on a date that is either input directly or from an existing field. I've got the basics working but the challenge has been that I want the results to sometimes include more than one string.



If the date is before July 1st I want to filter using "FY" and the last two digits of the date's year. "FY18" would be the filter for Feb 2, 2018 for example.
If the date is BETWEEN June 30 and July 15 I want to filter for the last two of the date's year OR the date's year +1. "FY18 OR FY19" would be the filter for July 2, 2018.
If the date is after July 14 I want the filter to be the last two of the date +1. "FY19" would be the filter for August 7, 2018.

I've got the date stuff working and the using the last two of the year but my trouble comes when I try and get the results to include BOTH using an OR. Here's a super simplified example of my iif.

Like IIf([testthing]=1,"fy18",IIf([testthing]=2,"'fy18 OR fy19'","fy19"))

in this example I've removed the date handling because I know I've got that stuff working and I am really only having a problem with the OR being included. I can't seem to work out the string, I suspect it's a matter of single and double quotes which always seem to vex me.

Any help would be awesome.

Extra credit for anyone that can give me an easy way to filter for those date ranges that works in a leap year, I've been using the datepart("y") option but in a leap year those days all shift by one.

[isladogs]

I'm afraid that's as clear as mud ...to me anyway.
How those dates follow from FY18 etc is beyond me.
Is there a lookup table for those values and if so is 'FY18 or FY19' one item or two?

Or are you suggesting we should ignore the dates and just fix your nested IIf statement?

[June7]

LIKE is only useful when used with wildcard.

You are doing this expression in query? Don't think filter clause can be dynamic like that. The OR is just text in that string, not a special word.

Suggest using VBA to build filter and apply to form or report. Review http://allenbrowne.com/ser-62.html

[orange]

Seems a confused post at best - I agree.

??What is your Fiscal Year??

Try to describe what you are trying to do in simple, plain English -no jargon and no database terms.
What is the significance of July 14??

If you can't describe it clearly, nobody can code it.

[BFauska]

My problem is with the OR in the nested iif. It's really the only part I feel like I NEED help with but I'm always interested in learning some best practices along the way.

I've removed the like for simplification sake, thanks for pointing out that it was unnecessary.

I don't think it plays into my primary question but our fiscal year is July 1-June 30. The purpose of my filter is that our pay periods cross the fiscal line and projects that I want to show in a drop down list should be filtered for projects that could fall in a pay period, for the pay period that crosses the fiscal year I need to show projects from both years, for other pay periods I want to show fewer items (only the ones in the current fy).
For each project there is a fiscal year assigned in the project table, I'm using that field to filter the list. For the first two weeks of July I need to see both fiscal year's projects, before and after that I only need current fiscal year projects. I can make it work to return FY18 projects before 7/1, and FY19 projects after 7/14 but when I try and get both results with a date in the middle I just get nothing at all. I suspect it's my syntax trying to include an OR in the iif output value. If I manually put

"FY18" OR "FY19"

in the criteria line I get the combined results I want.

Hopefully that's clearer.

[Minty]

If I've understood you correctly...

Like IIf([testthing]=1,'fy18',IIf([testthing]=2,"'fy18' OR 'fy19'",'fy19'))

might work.

If not you've confused me as well

[BFauska]

Assuming that the code you gave me is supposed to allow the filtered field to be either "fy18" or "fy19" then yes you've understood correctly. However, I still get results when I enter 1 or anything else but nothing for 2 (which would be the result with the OR).

I'm sure I can hash this out with VBA but I've always been inclined to keep as much of my stuff dealt with by using native access features. Maybe that's silly, but when I walk away from this tool I'm making and someone else ends up with a new feature or troubleshooting who will also likely be self trained I feel like they'll be better off.

¯\_(ツ)_/¯

[June7]

Because the OR is still just text in a string. It is not evaluated as operator. Consider:

IIf([testthing]=2, "*", "fy18") OR IIf([testthing]=1, "*", "fy19")

Now do tests with [testthing] set to 1, 2, 3.

[BFauska]

AWESOME, thank you. It worked as you posted it and it worked when I added my date complications back in. To make sure I understand how it's doing it, without a "like" the * isn't doing anything right? I replaced "*" with "" and got the same behavior and I also tried adding "like" at the beginning and leaving the "*" and got all the results all the time.

Here's my final if anyone cares.

IIf(DatePart("y",[EnterEndDate])<182,"*","FY" & Int(Right(Year([EnterEndDate]),2))+"1") Or IIf(DatePart("y",[EnterEndDate])>195,"*","FY" & Int(Right(Year([EnterEndDate]),2)))

I've just left the date window a day long to accommodate leap years, the only downside is that sometimes the list will be longer than necessary for a day.

[June7]

Okay, empty string works as well as * character (without LIKE it is just text not wildcard) - any value that would not have matching data would serve, try "FYZ".