Dynamic Date Value Criteria (1 Viewer)

DoneganF

Registered User.
Local time
Today, 12:17
Joined
Aug 7, 2012
Messages
29
I have a financial reporting database which produces reports on a fiscal year basis. Our fiscal year is from June thru July. We completed our 1st fiscal year at the end of June ’19 and began our 2nd fiscal year on July 1st. Throughout the first fiscal year, my query calculated FYTD values by simply using the following expression in the Date field; Between #7/1/2018# And [Enter Reporting Month]. Here’s the problem …. Now that we’re in our 2nd fiscal year, I need to replace the static value of #7/1/2018# with the appropriate fiscal year begin date – based on the value entered in the “[Enter Reporting Month]” field. For instance, if the reporting month entered is between 7/1/2019 and 6/30/2020, I need the beginning fiscal date to be 7/1/19; if the reporting month is between 7/1/2020 and 6/30/2021, the fiscal year begin date should be 7/1/20, etc. Any help is greatly appreciated
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:17
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try something like:
Code:
Between DateSerial(Year(DateAdd("m",-6,([Enter Reporting Month])),7,1) And [Enter Reporting Month]
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:17
Joined
May 7, 2009
Messages
19,169
or you use:
Code:
[DateField] Between Format([Enter Reporting Month], "\#mm\/dd\/yyyy\#") And Format(DateAdd("y", 1, [Enter Reporting Month])-2, "\#mm\/dd\/yyyy\#")
 

DoneganF

Registered User.
Local time
Today, 12:17
Joined
Aug 7, 2012
Messages
29
Hey Thanks So Much to both of you!! The DateSerial expression worked perfectly. Much appreciated
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:17
Joined
Oct 29, 2018
Messages
21,358
Hey Thanks So Much to both of you!! The DateSerial expression worked perfectly. Much appreciated
Hi. You're welcome. Arnel and I were happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom