IF statement in query criteria - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

IF statement in query criteria

Michel Paul

5 years ago

Hi,

I am trying to use a IIF statement inside a query criteria.
The database is used to track membership in a club, for example we need to use a query to look for records between sept 30, 2019 and sept 30, 2020. Since we are currently in 2021, the query does not return the correct date range.

We are using an IIF statement to determine if the current month is between Jan and Aug or sept and Dec and set the correct criteria.

The date format is for a french format (YY-MM-DD)

Here is the criteria we are trying to use:

IIF(Month(Date())8, (Year(Date())-1 & "-09-30" and Year(Date()) & "-09-30")
, (Year(Date())-2 & "-09-30" and Year(Date())-1 & "-09-30"))

The query always return 0 records instead of over 500 records.

Can you help. If I can figure out how to get this type of query working I have many other query to do this with.

Thanks in advance for your help

	Juan C Rivera @Reply 5 years ago
Tech Tip - Query Criteria I try to do something like that but with fiscal year... This answered my questions. Hope it helps you also. V/r Juan

	Adam Schwanz @Reply 5 years ago
Is this being used as criteria in another query expression? Can you just use entered parameters? If I'm reading that right, your saying if Month 8 is true then LastYear-09-30 and CurrentYear-09-30, if Month is not 8 then 2YearsAgo-09-30 and LastYear-09-30

	Richard Rost @Reply 5 years ago
Yeah, remember guys my web site filters out < and > characters for now. I'm gonna fix it later, but it was an anti-hacking measure to prevent HTML tags. :)

	Adam Schwanz @Reply 5 years ago
Well that explains a little :P.

	Juan C Rivera @Reply 5 years ago
Howdy Y'all I would use entered parameters or you will be re-coding every year. Enter StartDate and EndDate, what you think Adam?

	Adam Schwanz @Reply 5 years ago
Depends what he's trying to do. He wouldn't need to re-code it if it was ALWAYS going to be previous year month 8 as the criteria when used. Although for looking up memberships I think I'd use parameters, then you can use the same query between ANY two dates.

Michel Paul OP @Reply

5 years ago

Just to clarify, We are looking at finding record with a date for the previous year activities (From Sept 2019 to August 2020)

The IIF statement is used to determine if the current month is between Sept and Dec (larger that 8) and in this situation use the year of the current date (2020) as a start date year, else if the month is between Jan and Aug (Not larger than 8) then we are in the year 2021. But I still need to have 2020 as the start date instead of 2021.

I will try the parameter approach, but I would prefer a solution where I don't need a parameter.

Thanks for you help

	Adam Schwanz @Reply 5 years ago
Just out of curiosity, did this work last year(2020) before and after September? Or is it something new?

	Michel Paul OP @Reply 5 years ago
This a new query and I have a few more similar ones that I need to build in the near future.

	Richard Rost @Reply 5 years ago
I'm pretty sure I've already addressed this problem: Fiscal Year Seminar

	Michel Paul OP @Reply 5 years ago
Sorry, but I never saw that seminar I have tried using the Parameter option and it worked, So I guess that is how I will go. Thanks

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View