Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-18-2019, 09:42 AM   #1
DoneganF
Newly Registered User
 
Join Date: Aug 2012
Posts: 18
Thanks: 4
Thanked 0 Times in 0 Posts
DoneganF is on a distinguished road
Smile Automate Text/Date Criteria

I have a data field named Fiscal Year which stores values in the following text format; FY 2018. Our fiscal year period is from July thru June with the YEAR portion of FY 2018 being the fiscal year ending YEAR. For instance, the date of October 2019 is part of FY 2020. Heres my problem, I have several queries in which I criteria the Fiscal Year field and Id like to automate that process, based on the month a user selects. Here is the closest Ive come thus far:

"FY " & DateSerial(Year(DateAdd("m",+6,([Enter Month]))),6,1)

The problem is that this expression returns FY 6/1/(appropriate year). I only want it to return FY (space) and the appropriate 4-digit year; For instance ...FY 2020

Any help is greatly appreciated!!

DoneganF is offline   Reply With Quote
Old 07-18-2019, 09:49 AM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,563
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Automate Text/Date Criteria

Hi. I'm not sure I follow. So, if the user enters a month number, do we assume the user "always" refers to the upcoming month for the fiscal year? For example, if the user enter the number 7 today, do we return 2020 or 2021?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-18-2019, 09:57 AM   #3
DoneganF
Newly Registered User
 
Join Date: Aug 2012
Posts: 18
Thanks: 4
Thanked 0 Times in 0 Posts
DoneganF is on a distinguished road
Re: Automate Text/Date Criteria

No, the user enters a whole date; 6/1/2018 - in which case I need the expression to return "FY 2018"

If the user enters 10/1/2016 the expression needs to return "FY 2017"

DoneganF is offline   Reply With Quote
Old 07-18-2019, 10:11 AM   #4
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,563
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Automate Text/Date Criteria

Quote:
Originally Posted by DoneganF View Post
No, the user enters a whole date; 6/1/2018 - in which case I need the expression to return "FY 2018"

If the user enters 10/1/2016 the expression needs to return "FY 2017"
Okay, in that case, try using this:
Code:
"FY " & Format(DateAdd("m",6,[Enter Date]),"yyyy")
(untested)
Hope it helps...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
DoneganF (07-18-2019)
Old 07-18-2019, 12:22 PM   #5
DoneganF
Newly Registered User
 
Join Date: Aug 2012
Posts: 18
Thanks: 4
Thanked 0 Times in 0 Posts
DoneganF is on a distinguished road
Re: Automate Text/Date Criteria

Thank you, thank you, thank you! Worked like a charm!
DoneganF is offline   Reply With Quote
Old 07-18-2019, 12:25 PM   #6
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,563
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Automate Text/Date Criteria

Quote:
Originally Posted by DoneganF View Post
Thank you, thank you, thank you! Worked like a charm!
Hi. You're welcome! Glad to hear you got it to work. Good luck with your project.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-19-2019, 03:35 AM   #7
DoneganF
Newly Registered User
 
Join Date: Aug 2012
Posts: 18
Thanks: 4
Thanked 0 Times in 0 Posts
DoneganF is on a distinguished road
Re: Automate Text/Date Criteria

Ok, so one more question. I now need to return the exact same value - but for the previous year?


DoneganF is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DSUM with date and short text criteria bymedia17 Queries 5 03-15-2017 10:13 AM
Automate the row selection criteria Cheli General 31 04-14-2015 04:46 AM
Automate a date field simon4amiee Queries 1 03-12-2014 01:16 AM
Date Criteria based Month and Year text box Core Queries 1 08-20-2009 03:10 AM
Date Criteria from Text Boxes MikeAngelastro Queries 3 12-23-2002 10:20 AM




All times are GMT -8. The time now is 12:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World