Solved Update FY Start and End (1 Viewer)

EM2021

EM2021

New member
Local time
Yesterday, 22:47
Joined
Aug 25, 2021
Messages
24
Pat Hartman said:
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:
Code: 
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.
Click to expand...
Thanks, I agree. The VBA is where I get lost in this report. Where, specifically, would I put this?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:47
Joined
Oct 29, 2018
Messages
21,504
EM2021 said:
Current Date would be ideal.
Click to expand...
Don't remember if you said there's anything in the Default Value property but try replacing it with this.

=Date()
 
EM2021

EM2021

New member
Local time
Yesterday, 22:47
Joined
Aug 25, 2021
Messages
24
theDBguy said:
Don't remember if you said there's anything in the Default Value property but try replacing it with this.

=Date()
Click to expand...
It is defaulting to the last day in the FY End. That is what I need to figure out how to change.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:47
Joined
Oct 29, 2018
Messages
21,504
EM2021 said:
It is defaulting to the last day in the FY End. That is what I need to figure out how to change.
Click to expand...
If you go to design view and select the box; in the properties window, what do you see in the default value property?
 
EM2021

EM2021

New member
Local time
Yesterday, 22:47
Joined
Aug 25, 2021
Messages
24
theDBguy said:
If you go to design view and select the box; in the properties window, what do you see in the default value property?
Click to expand...
It is blank.
1632238139045.png
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:47
Joined
Feb 19, 2002
Messages
43,392
Current Date would be ideal.
Click to expand...
So, On Sept 1, you NEVER want to run last year's report? Reports need criteria. The start year is the minimum. If you use Date(), you would need to have additional logic to test the date. If it is >=9/1, then you use that year. Otherwise, you have to subtract 1 from the year. You can make a default, but I would not do it this way.

T code I posted goes into the CLICK event of the button that runs the report. It does NOT go into the report itself. You could modify this code to "assume" a year based on the logic from the previous paragraph.

If you are currently using an embedded macro to run the report, convert the macro to VBA so you can add the suggested code in front of the DoCmd.OpenReport method.
 
EM2021

EM2021

New member
Local time
Yesterday, 22:47
Joined
Aug 25, 2021
Messages
24
theDBguy said:
That points to some code putting the date in there for you. You'll have to find out where it is. Do you know how to do that?
Click to expand...
I'm in the VBA code, but I'm not super familiar so maybe it's not jumping out at me. Any pointers? I've searched the 8/31/2021, "Report Date", "default" and I'm not finding anything.

Additional comment, the Report Date was changing automatically throughout FY21. So this is a new issue as we've come in to the new year.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:47
Joined
Oct 29, 2018
Messages
21,504
EM2021 said:
I'm in the VBA code, but I'm not super familiar so maybe it's not jumping out at me. Any pointers? I've searched the 8/31/2021, "Report Date", "default" and I'm not finding anything.

Additional comment, the Report Date was changing automatically throughout FY21. So this is a new issue as we've come in to the new year.
Click to expand...
Maybe you could copy and paste the entire code page here, so we can examine it.
 
EM2021

EM2021

New member
Local time
Yesterday, 22:47
Joined
Aug 25, 2021
Messages
24
theDBguy said:
Maybe you could copy and paste the entire code page here, so we can examine it.
Click to expand...
Here are all the Subs under the Form code window. Let me know if this is not where I should be.


Option Compare Database

Private Sub cmdFYTDBUConstituentSummary_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.ListIndex = -1 Then MsgBox "Please pick a school in the dropdown box!", vbOKOnly, Me.Caption: Exit Sub
'
DoCmd.OpenReport "FYTD BU Constituent Summary", acViewReport, , , acWindowNormal
End Sub

Private Sub cmdFYTDBUEndowmentCommitments_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.ListIndex = -1 Then MsgBox "Please pick a school in the dropdown box!", vbOKOnly, Me.Caption: Exit Sub
'
DoCmd.OpenReport "FYTD BU Commitments", acViewReport, , "[DTE] <= #" & Me![ReportDate] & "# AND [FundCategory] IN ('Endowment Faculty & Staff','Endowment Scholarships') and ([BU] IN " & Me![cboSchool] & " OR [RBU] IN " & Me![cboSchool] & ")", acWindowNormal
'
[Reports]![FYTD BU Commitments]!lblTitle.Caption = "FY" & Right(Year(Me!FYEnd), 2) & " Fiscal-Year-To-Date Faculty and Scholarship Endowment Commitments: " & Me![cboSchool].Column(0)
[Reports]![FYTD BU Commitments]!lblSubtitle.Caption = Me!FYStart.Value & " through " & Me!ReportDate.Value & " - " & Format(Me!FYElapsed.Value, "0.0%") & " of Fiscal Year Elapsed"
End Sub

Private Sub cmdFYTDBUFundCommitments_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.ListIndex = -1 Then MsgBox "Please pick a school in the dropdown box!", vbOKOnly, Me.Caption: Exit Sub
'
DoCmd.OpenReport "FYTD BU Fund Commitment Totals", acViewReport, , "[DTE] <= #" & Me![ReportDate] & "# AND ([BU] IN " & Me![cboSchool] & " OR [RBU] IN " & Me![cboSchool] & ")", acWindowNormal
'
[Reports]![FYTD BU Fund Commitment Totals]!lblTitle.Caption = "FY" & Right(Year(Me!FYEnd), 2) & " Fiscal-Year-To-Date Fund Totals: " & Me![cboSchool].Column(0)
[Reports]![FYTD BU Fund Commitment Totals]!lblSubtitle.Caption = Me!FYStart.Value & " through " & Me!ReportDate.Value & " - " & Format(Me!FYElapsed.Value, "0.0%") & " of Fiscal Year Elapsed"
End Sub

Private Sub cmdFYTDBUGiftInKind_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.ListIndex = -1 Then MsgBox "Please pick a school in the dropdown box!", vbOKOnly, Me.Caption: Exit Sub
'
DoCmd.OpenReport "FYTD BU Commitments", acViewReport, , "[Type] In ('Gift-in-Kind') AND [DTE] <= #" & Me![ReportDate] & "# AND ([BU] IN " & Me![cboSchool] & " OR [RBU] IN " & Me![cboSchool] & ")", acWindowNormal
'
[Reports]![FYTD BU Commitments]!lblTitle.Caption = "FY" & Right(Year(Me!FYEnd), 2) & " Fiscal-Year-To-Date Gift-In-Kind Commitments: " & Me![cboSchool].Column(0)
[Reports]![FYTD BU Commitments]!lblSubtitle.Caption = Me!FYStart.Value & " through " & Me!ReportDate.Value & " - " & Format(Me!FYElapsed.Value, "0.0%") & " of Fiscal Year Elapsed"
End Sub

Private Sub cmdFYTDBUNewCash_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.ListIndex = -1 Then MsgBox "Please pick a school in the dropdown box!", vbOKOnly, Me.Caption: Exit Sub
'
DoCmd.OpenReport "FYTD BU Commitments", acViewReport, , "[Type] In ('Cash','Stock/Property','Stock/Property (Sold)') AND [DTE] <= #" & Me![ReportDate] & "# AND ([BU] IN " & Me![cboSchool] & " OR [RBU] IN " & Me![cboSchool] & ")", acWindowNormal
'
[Reports]![FYTD BU Commitments]!lblTitle.Caption = "FY" & Right(Year(Me!FYEnd), 2) & " Fiscal-Year-To-Date New Cash Commitments: " & Me![cboSchool].Column(0)
[Reports]![FYTD BU Commitments]!lblSubtitle.Caption = Me!FYStart.Value & " through " & Me!ReportDate.Value & " - " & Format(Me!FYElapsed.Value, "0.0%") & " of Fiscal Year Elapsed"
End Sub

Private Sub cmdFYTDBUPayments_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.ListIndex = -1 Then MsgBox "Please pick a school in the dropdown box!", vbOKOnly, Me.Caption: Exit Sub
'
DoCmd.OpenReport "FYTD BU Payments", acViewReport, , "[DTE] <= #" & Me![ReportDate] & "# AND ([BU] IN " & Me![cboSchool] & " OR [RBU] IN " & Me![cboSchool] & ")", acWindowNormal
'
[Reports]![FYTD BU Payments]!lblTitle.Caption = "FY" & Right(Year(Me!FYEnd), 2) & " Fiscal-Year-To-Date Payments: " & Me![cboSchool].Column(0)
[Reports]![FYTD BU Payments]!lblSubtitle.Caption = Me!FYStart.Value & " through " & Me!ReportDate.Value & " - " & Format(Me!FYElapsed.Value, "0.0%") & " of Fiscal Year Elapsed"
End Sub

Private Sub cmdFYTDBUPledges_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.ListIndex = -1 Then MsgBox "Please pick a school in the dropdown box!", vbOKOnly, Me.Caption: Exit Sub
DoCmd.OpenReport "FYTD BU Commitments", acViewReport, , "[Type] In ('Pledge','MG Pledge','Planned Gift') AND [DTE] <= #" & Me![ReportDate] & "# AND ([BU] IN " & Me![cboSchool] & " OR [RBU] IN " & Me![cboSchool] & ")", acWindowNormal
[Reports]![FYTD BU Commitments]!lblTitle.Caption = "FY" & Right(Year(Me!FYEnd), 2) & " Fiscal-Year-To-Date Pledge Commitments: " & Me![cboSchool].Column(0)
[Reports]![FYTD BU Commitments]!lblSubtitle.Caption = Me!FYStart.Value & " through " & Me!ReportDate.Value & " - " & Format(Me!FYElapsed.Value, "0.0%") & " of Fiscal Year Elapsed"
End Sub

Private Sub cmdFYTDBUSummary_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.ListIndex = -1 Then MsgBox "Please pick a school in the dropdown box!", vbOKOnly, Me.Caption: Exit Sub
DoCmd.OpenReport "FYTD BU Summary", acViewReport, , , acWindowNormal
End Sub

Private Sub cmdFYTDSummary_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
DoCmd.OpenReport "FYTD Summary", acViewReport, , , acWindowNormal
End Sub

Private Sub Form_Load()
Me!ReportDate.Value = DateSerial(Year(Date), Month(Date), 0)
Me!cboSchool.Value = "SOMED"
End Sub
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:47
Joined
Oct 29, 2018
Messages
21,504
EM2021 said:
Private Sub Form_Load()
Me!ReportDate.Value = DateSerial(Year(Date), Month(Date), 0)
Me!cboSchool.Value = "SOMED"
End Sub
Click to expand...
The above part... Try changing it to this:
Code: 
Private Sub Form_Load()
'Me!ReportDate.Value = DateSerial(Year(Date), Month(Date), 0)
'Me!cboSchool.Value = "SOMED"
End Sub
Basically, just add single quotes in front of those two lines. Then, make sure in the Default Value property of those two boxes, you have the following:

For the "ReportDate" box, it should say: =Date()
For the "cboSchool" dropdown, it should say: "SOMED"

Hope that helps...
 
EM2021

EM2021

New member
Local time
Yesterday, 22:47
Joined
Aug 25, 2021
Messages
24
theDBguy said:
The above part... Try changing it to this:
Code: 
Private Sub Form_Load()
'Me!ReportDate.Value = DateSerial(Year(Date), Month(Date), 0)
'Me!cboSchool.Value = "SOMED"
End Sub
Basically, just add single quotes in front of those two lines. Then, make sure in the Default Value property of those two boxes, you have the following:

For the "ReportDate" box, it should say: =Date()
For the "cboSchool" dropdown, it should say: "SOMED"

Hope that helps...
Click to expand...
Bingo! Thanks so much.
 
moke123

moke123

AWF VIP
Local time
Yesterday, 23:47
Joined
Jan 11, 2013
Messages
3,933
just an FYI, you can get the fiscal year of any date easily with a function

Code: 
Function GetFiscalYear(Dt As Variant, Optional StartMonth As Integer = 9) As Variant

    If Not IsDate(Dt) Then Exit Function
    
    If Month(Dt) >= StartMonth Then
        GetFiscalYear = (Year(Dt) + 1)
    Else
        GetFiscalYear = Year(Dt)
    End If
    
End Function
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:47
Joined
Feb 19, 2002
Messages
43,392
@EM2021 you might want to review some code written by experts. You don't get points for mushing all the if statement onto a single line or for never indenting anything.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 04:47
Joined
Sep 21, 2011
Messages
14,376
Pat, I don't' believe the code is the OP's, else they would have known where to look?
 
moke123

moke123

AWF VIP
Local time
Yesterday, 23:47
Joined
Jan 11, 2013
Messages
3,933
EM2021 said:
Option Compare Database

Private Sub cmdFYTDBUConstituentSummary_Click()
If Not IsDate(Forms![Development Report Manager]!ReportDate.Value) Then MsgBox "Please pick a Report Date!", vbOKOnly, Me.Caption: Exit Sub
If Me!cboSchool.List
Click to expand...
You would also be doing yourself a big favor if you added Option Explicit to the declarations.
 
You must log in or register to reply here.

Similar threads

Z
Split Date
Replies
2
Views
299
CJ_London
C
D
Handling relationships between Documents and references
Replies
3
Views
286
Denise2020
D
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
481
gemma-the-husky
gemma-the-husky
S
Refine a working regex exclude periods or dots except if a decimal
Replies
10
Views
592
DickyP
DickyP
B
error update current column name
Replies
7
Views
266
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom