Solved Update FY Start and End (1 Viewer)

[EM2021]

Hi there, I've inherited a report that is coded to pull specific dates (9/1/YY - 8/31/YY) It's our new year and I can't seem to figure out the notes on updating this. to pull 9/1/2021-8/31/2022. Notes are: "modify the code on the start and end dates by changing the option to Editable and removing the code in the source"

Ideas on where this code is? When I look in VBA, I'm not seeing anything specific. Thanks in advance.

 

[EM2021]

Hi there, I've inherited a report that is coded to pull specific dates (9/1/YY - 8/31/YY) It's our new year and I can't seem to figure out the notes on updating this. to pull 9/1/2021-8/31/2022. Notes are: "modify the code on the start and end dates by changing the option to Editable and removing the code in the source"

Ideas on where this code is? When I look in VBA, I'm not seeing anything specific. Thanks in advance.

View attachment 94514

Report date is editable, but I'm having a hard time editing FY Start and End to reflect the new year

 

[theDBguy]

Hi. If you're going to edit the start and end years anyway, then why even use an expression. Simply use:

=9/1/2021 and
=8/31/2022

Wouldn't that work?

 

[plog]

From the looks of it, you shouldn't need to change any code or do any work in Design Mode. You simply type in a value for the Report Date and it should do the rest.

If you want the report for FY 2022 then type in 8/31/2022 into the box.

 

[pbaldy]

What happens if you change report date to 9/1/21?

 

[pbaldy]

Oops, too slow.

 

[EM2021]

Hi. If you're going to edit the start and end years anyway, then why even use an expression. Simply use:

=9/1/2021 and
=8/31/2022

Wouldn't that work?

I would like to make it that simple, for sure.
Here is what I entered and what I get:

What am I missing to make it stick at the dates in Design?

 

[EM2021]

I would like to make it that simple, for sure.
Here is what I entered and what I get:
View attachment 94516
View attachment 94517

What am I missing to make it stick at the dates in Design?

Silly question. But... then I get a #Type mismatch.

 

[EM2021]

What happens if you change report date to 9/1/21?

So it does update and work when I do this. However, when you close the form and reopen it, it defaults back. I need to have it open with today's date or at least 8/31/2022

 

[EM2021]

Accidently marked solved. Definitely is not!

 

[theDBguy]

So it does update and work when I do this. However, when you close the form and reopen it, it defaults back. I need to have it open with today's date or at least 8/31/2022

Hi. Not sure I understand. What is "it" that reverts back? When it does revert back, what does it say? Did you save the changes you made before closing the form?

 

[EM2021]

Hi. Not sure I understand. What is "it" that reverts back? When it does revert back, what does it say? Did you save the changes you made before closing the form?

Updating the Report Date does update the FY Start and End. But even after saving, when I reopen, the Report Date defaults back to 8/31/2021

 

[theDBguy]

Hi. In the image you posted above, the Report Date box says "Unbound," which means you must be using code to populate it with 8/31/2021. Either that or the Default Value is set to 8/31/2021. Double-check either location and let us know which one is doing it.

Or, you could simply replace that with this:

=Date()

 

[pbaldy]

You can put a default value of Date() on that textbox.

 

[EM2021]

Hi. In the image you posted above, the Report Date box says "Unbound," which means you must be using code to populate it with 8/31/2021. Either that or the Default Value is set to 8/31/2021. Double-check either location and let us know which one is doing it.

Or, you could simply replace that with this:

=Date()

Trying to replace and I get taken here through Debug:

 

[EM2021]

Hi. In the image you posted above, the Report Date box says "Unbound," which means you must be using code to populate it with 8/31/2021. Either that or the Default Value is set to 8/31/2021. Double-check either location and let us know which one is doing it.

Or, you could simply replace that with this:

=Date()

And I believe that its pulling with 8/31/2021 because that is the last option within the FY. So, my best guess with the note left behind is that I need to find where 9/1/2020 - 8/31/2021 is coded

 

[pbaldy]

Your highlighted code puts the last day of the previous month (relative to "today") in the textbox.

 

[theDBguy]

And I believe that its pulling with 8/31/2021 because that is the last option within the FY. So, my best guess with the note left behind is that I need to find where 9/1/2020 - 8/31/2021 is coded

Hi. If you don't want the box to open with 8/31/2021, what value did you want it to have instead?

 

[Pat Hartman]

You absolutely do NOT want to hardcode the date. You want to use variables. Have a visible form field to accept the beginning year. Then in the button to run the report, have something like the following:

If Len(Me.StartYear) = 4 AND IsNumeric(Me.StartYear) Then
    Me.StartDate = "9/1/" & Me.StartYear
    Me.EndDate = "8/31/" & Me.StartYear + 1
Else
    Msgbox "Please Enter a valid year."vbOKOnly
    Exit Sub
End If

DoCmd.OpenReport "DoCmd.OpenReport "YourReport", acViewPreview, , "DataDT Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#"

If you want to display the start/end dates on the report just reference the form fields.

 

[EM2021]

Hi. If you don't want the box to open with 8/31/2021, what value did you want it to have instead?

Current Date would be ideal.