Solved Update FY Start and End

[pbaldy]

Thanks, I agree. The VBA is where I get lost in this report. Where, specifically, would I put this?

Is this what you're looking for?

First VBA

 

[theDBguy]

Current Date would be ideal.

Don't remember if you said there's anything in the Default Value property but try replacing it with this.

=Date()

 

[EM2021]

Don't remember if you said there's anything in the Default Value property but try replacing it with this.

=Date()

It is defaulting to the last day in the FY End. That is what I need to figure out how to change.

 

[theDBguy]

It is defaulting to the last day in the FY End. That is what I need to figure out how to change.

If you go to design view and select the box; in the properties window, what do you see in the default value property?

 

[EM2021]

If you go to design view and select the box; in the properties window, what do you see in the default value property?

It is blank.

 

[theDBguy]

It is blank. View attachment 94650

Great. Now, type this in there.

=Date()

 

[EM2021]

Great. Now, type this in there.

=Date()

Sure thing. I hit save and it's still doing it. What. Am. I. Missing?!

 

[theDBguy]

Sure thing. I hit save and it's still doing it. What. Am. I. Missing?!
View attachment 94651
View attachment 94652

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?

 

[EM2021]

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?

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]

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.

Maybe you could copy and paste the entire code page here, so we can examine it.

 

[EM2021]

Maybe you could copy and paste the entire code page here, so we can examine it.

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]

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

The above part... Try changing it to this:

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]

The above part... Try changing it to this:

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...

Bingo! Thanks so much.

 

[theDBguy]

Bingo! Thanks so much.

You're very welcome. We were all happy to assist. Good luck with your project.

 

[moke123]

just an FYI, you can get the fiscal year of any date easily with a function

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

 

[Gasman]

Pat, I don't' believe the code is the OP's, else they would have known where to look?

 

[moke123]

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

You would also be doing yourself a big favor if you added Option Explicit to the declarations.

 

[gemma-the-husky]

Is this sorted now?
Maybe the issue is trying to treat the date as a text string
if the dates fields were defined as dates. then presumably the values would be #9/1/2020# and #8/31/2021#

Something like that. I assume the OP is in the US, or at least some somewhere where #9/1/2020# is "September 1", rather than "9 January"