UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Fiscal Year Query, Access 2010    
 
   
Chris L
post Aug 14 2019, 01:05 AM
Post#1



Posts: 5
Joined: 14-August 19



I am trying to generate a report that shows open cases from the previous fiscal year, but I want the end user to be able to generate the report from an unbound text box on a form.

My table has a date received field and a date closed field. My form has an unbound text box where the end user enters say, today's date, then clicks a command button that runs a query that populates the report.

I am sneaking up on my solution. So far, in my test query I can generate an answer using CarryCaseForward:iif(is null([DateClosed]) AND ([DateRecevied])<Forms!FormName.textbox,1,0)

My issue is now, how do I create a query that says whatever the date that is entered into Forms!FormName.Textbox show me the number of cases that matches that criteria of no Date Closed but the Date Received was from the previous fiscal year?

Any ideas would be appreciated.
Thanks

Go to the top of the page
 
RJD
post Aug 14 2019, 06:32 AM
Post#2


UtterAccess VIP
Posts: 10,153
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

Hi Chris: How do you define a fiscal year? What date span? And do you mean that if the user enters a full date, the results should be from the FY prior to the one containing the date entered?

You might be able to use the DateSerial function, along with some logic, to create the date span to use.

A bit of clarification would help us help you.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
theDBguy
post Aug 14 2019, 07:20 AM
Post#3


UA Moderator
Posts: 76,891
Joined: 19-June 07
From: SunnySandyEggo


Hi Chris. Welcome to UA! welcome2UA.gif

As Joe said, we’ll need more information before we can offer any solutions.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Chris L
post Aug 14 2019, 09:12 AM
Post#4



Posts: 5
Joined: 14-August 19



The fiscal year is from July 1 through June 30.

The report is produced three times per year. Each time it is produced it does require a specific time period for the cases received in the current fiscal year and all open cases from the previous fiscal year.

As an example:
In January 2019 the report was generated and showed open cases received from July 1, 2018 through December 31, 2018. This would be cases received during the current fiscal year. The report also included all open cases that where received on June 30, 2018, backwards. This would be cases received during the previous fiscal year.

The next time this report is generated would be in May 2019. This report showed open cases received from July 1, 2018 through April 15, 2019, and all open cases received June 30, 2018, backward.

The third time the report is generated would be in July 2020. The report would show all open cases received between July 1, 2018 through June 30, 2019, and all cases still open that were received June 30, 2018, backward

I created a form for the end user to generate the report. I added two unbound text boxes. One is txStartDate and the second is txEndDate and I set the Format control for these text boxes to Short Date. This allows for a between dates search for the sections of the report for the current fiscal year.

Hopefully I am explaining this well
Thanks
Chris
Go to the top of the page
 
Chris L
post Aug 14 2019, 09:47 AM
Post#5



Posts: 5
Joined: 14-August 19



I believe I may have way over thought this. It just dawned on me, if the date the end user enters in txStartDate is the first day of the current fiscal year, then my original idea of
CarryCaseForward:iif(is null([DateClosed]) AND ([DateRecevied])<Forms!FormName.txStartDate,1,0) should show all open cases from the previous fiscal year.

Now hopefully I am not under thinking this.
Go to the top of the page
 
theDBguy
post Aug 14 2019, 09:54 AM
Post#6


UA Moderator
Posts: 76,891
Joined: 19-June 07
From: SunnySandyEggo


Hi Chris. Glad to hear you got something to go on. Let us know how it goes...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Chris L
post Aug 14 2019, 07:58 PM
Post#7



Posts: 5
Joined: 14-August 19



I have tested CarryCaseForward:iif(is null([DateClosed]) AND ([DateRecevied])<Forms!FormName.txStartDate,1,0) a lot today and it actually works very well.

I appreciate you guys asking questions as it got me thinking.

Chris
Go to the top of the page
 
RJD
post Aug 14 2019, 08:50 PM
Post#8


UtterAccess VIP
Posts: 10,153
Joined: 25-October 10
From: Gulf South USA


Glad you got that solved. Sometimes just asking questions clarifies the problem enough that a solution pops up. thumbup.gif

Continued success with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Chris L
post Aug 15 2019, 09:11 AM
Post#9



Posts: 5
Joined: 14-August 19



I believe that 100%. Thanks.
Go to the top of the page
 
RJD
post Aug 15 2019, 09:32 AM
Post#10


UtterAccess VIP
Posts: 10,153
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th December 2019 - 04:53 PM