limit date Selection

[krc9127]

Is there a way on a form that has a date selection field (with date picker show) to limit how back a date be chosen or a date typed in? I asking to prevent someone from intentionally or unintentionally from adding data to a fiscal year that has been closed.

[CJ_London]

Use the control before update event to prevent user entering to early (or late) date

[moke123]

You can get the fiscal year of a date with something like

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

Result:
?GetFiscalYear(#9/7/2022#)
 2023 
?GetFiscalYear(#8/30/2022#)
 2022

Gives me another item to add to my tinkering list. I have a custom date picker class and it would be interesting to try it with a limited date range.

[krc9127]

You can get the fiscal year of a date with something like

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

Result:
?GetFiscalYear(#9/7/2022#)
 2023 
?GetFiscalYear(#8/30/2022#)
 2022

Gives me another item to add to my tinkering list. I have a custom date picker class and it would be interesting to try it with a limited date range.

So I just change the 9 to a 7 if our fiscal starts in July and they can't pick anything before the current fiscal year?

[moke123]

Yes 7 would be july 1.

if GetFiscalYear(#some date#) = GetFiscalYear(Date()) then . . . It would be in the current fiscal year.

[krc9127]

OK I tried to put this in the code for the DATE_BeforeUpdate event, but it errors out. Does it go between the Private Sub, End Sub portions?

[Welshgasman]

OK I tried to put this in the code for the DATE_BeforeUpdate event, but it errors out. Does it go between the Private Sub, End Sub portions?

And you cannot say what that error is?

[moke123]

DATE_BeforeUpdate

You can't use Date for a field or object name as it is a reserved word.

[krc9127]

You can't use Date for a field or object name as it is a reserved word.

Even if I change the name, it still gives me the same thing. The field date is obviously not the reason.

[krc9127]

ok I put the sub routine to call for the function but now it gives me an error. This is my subroutine that calls the function.

Dim FiscalYear As Variant Dim EntryDate As Variant
Dim CurrentYear As Integer


' Assuming you have a control named "RideDate" on the form
RideDATE = Me!RideDATE


' Call the GetFiscalYear function
FiscalYear = GetFiscalYear(EntryDate)


' Get the current fiscal year
CurrentYear = GetFiscalYear(DATE)


' Check if the fiscal year is the prior year
If FiscalYear < CurrentYear Then
' Cancel the update
Cancel = True

' Display an error message
MsgBox "The prior fiscal year is locked. You cannot make changes to records from that period.", vbExclamation, "Update Error"
Else
' Proceed with the update (optional additional logic can be added here)
MsgBox "The fiscal year is: " & FiscalYear
End If

[ano]

u did not copy the procedure and u did not highlight which line/word get the error nor the error # explanation

[krc9127]

u did not copy the procedure and u did not highlight which line/word get the error nor the error # explanation

I found the error. I changed the actual field name but I didn't change the name of the DIM.

Thanks for all your input.

[moke123]

Oops, wrote this last night and apparently forgot post it.


simplified:

    If GetFiscalYear(EntryDate) <> GetFiscalYear(Date) Then
        Cancel = True
        MsgBox "The prior fiscal year is locked. You cannot make changes to records from that period.", vbExclamation, "Update Error"
    Else
        MsgBox "The fiscal year is: " & GetFiscalYear(EntryDate)
    End If

Hard to tell where your error is. you don't show enough code or error.
Not sure what this is but I dont see a variable RideDate dimensioned anywhere. That would error.

' Assuming you have a control named "RideDate" on the form
RideDATE = Me!RideDATE

Where's EntryDate coming from?

FiscalYear = GetFiscalYear(EntryDate)

Even if I change the name, it still gives me the same thing. The field date is obviously not the reason.

Using reserved words can cause random, sporadic errors so don't dismiss them.
http://allenbrowne.com/AppIssueBadWord.html

Also note that testing for FY being < than current FY wont prevent entry of a date > than the current FY. Your test should be <> current FY.

[ano]

with incomplete info i assume the spelling and definitions are correct else we never can help