Solved Update FY Start and End

EM2021 said:
Thanks, I agree. The VBA is where I get lost in this report. Where, specifically, would I put this?
Click to expand...

Is this what you're looking for?

 
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()
 
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.
 
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?
 
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
 
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.
 
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.
 
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
 
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...
 
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.
 
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
 
Pat, I don't' believe the code is the OP's, else they would have known where to look?
 
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.
 
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"
 
You must log in or register to reply here.

Similar threads

J
Textbox as source criteria for multiple queries
Replies
10
Views
426
JPR
J
R
subforms and forms
Replies
5
Views
894
ryetee
R
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
1,171
Mike Krailo
Mike Krailo
esturgesjr
Solved HELP WITH VBA ON SUBFORM
2
Replies
30
Views
942
The_Doc_Man
The_Doc_Man
S
End of the Month
Replies
4
Views
683
Sebsboutique
S

Users who are viewing this thread

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