Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Date + Time _ Fiscal Year Query

Posted by: Chris L Aug 14 2019, 01:05 AM

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


Posted by: RJD Aug 14 2019, 06:32 AM

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

Posted by: theDBguy Aug 14 2019, 07:20 AM

Hi Chris. Welcome to UA! welcome2UA.gif

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

Posted by: Chris L Aug 14 2019, 09:12 AM

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

Posted by: Chris L Aug 14 2019, 09:47 AM

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.

Posted by: theDBguy Aug 14 2019, 09:54 AM

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

Posted by: Chris L Aug 14 2019, 07:58 PM

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

Posted by: RJD Aug 14 2019, 08:50 PM

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

Posted by: Chris L Aug 15 2019, 09:11 AM

I believe that 100%. Thanks.

Posted by: RJD Aug 15 2019, 09:32 AM

thumbup.gif