Results 1 to 6 of 6
  1. #1
    hari101213 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    5

    Limit date input based on current date

    Hi guys,



    I have a text box in my form to input date of entry. After today's date has reached on or after April 15, users must be stopped from putting in any entry date that is from quarter 1 (Jan to Mar)

    Similarly when today's date has reached on or after 15 July, Q2 and Q1 dates of entry will be out of bounce from input
    When today's date is on or after 15 Oct, Q3, Q2 and Q1 entry dates will be barred

    Can anyone guide me how i can program this into my database?

    Appreciate your help. Thank you.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Do you also want to prevent entry of a date later than current date?
    Textbox ValidationRule could catch that: <=Date() Or Is Null
    And ValidationText: Date entered must be today or earlier.


    For the other data validation, expect will need VBA in textbox BeforeUpdate event. https://docs.microsoft.com/en-us/off...reupdate-event Something like:
    Code:
    Private Sub tbxDate_BeforeUpdate(Cancel As Integer)
        Select Case Format(Me.tbxDate(), "q")
            Case 1
                If Date >= "#4/15/" & Year(Date) & "#" Or Year(Me.tbxDate) < Year(Date) Then Cancel = True
            Case 2
                If Date >= "#7/15/" & Year(Date) & "#" Or Year(Me.tbxDate) < Year(Date)  Then Cancel = True
            Case 3
                If Date >= "#10/15/" & Year(Date) & "#" Or Year(Me.tbxDate) < Year(Date)  Then Cancel = True
            Case 4
                If Date >= "#1/15/" & Year(Date) & "#"  Or Year(Me.tbxDate) < Year(Date) - 1 Then Cancel = True
        End Select
        If Cancel = True Then 
            MsgBox "Invalid date. Enter another."
            Me.tbxDate.Undo
        End If
    End Sub
    Last edited by June7; 03-31-2020 at 08:58 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    hari101213 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    5
    Hi June,

    My textbox for users to input the date is called Date_IN. So i tried this code below based on your advise above but it's not working. I can still enter dates before 15th April with no msgbox showing. I just want it to not accept dates before 31/3/2020 if the current date has passed 15th April 2020. Any help anyone?

    Private Sub DATE_IN_BeforeUpdate(Cancel As Integer)
    Select Case Format(Me.DATE_IN(), "q")
    Case 1
    If Date >= "#4/15/" & Year(Date) & "#" Or Year(Me.DATE_IN) < Year(Date) Then Cancel = True
    End Select
    If Cancel = True Then
    MsgBox "Invalid date. Enter another."
    Me.DATE_IN.Undo
    End If
    End Sub

  4. #4
    hari101213 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    5
    Not sure if my explanation was clear. The below is what i need. My date text box is called Date_IN

    Current date now 15 April and above – No more 1 Jan to 31 Mar dates allowed only 1st April onwards date allowed
    Current date now has reached 15 July - No more 1 Jan to 30 June dates allowed only 1st July onwards date allowed
    Current date now has reached 15 October - No more 1 Jan to 30 Sept dates allowed only 1st Oct onwards date allowed.

    Not sure why June's code didnt work for me. There was no error msg.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    The condition for the entry to be restricted will be
    Code:
    Iif(DateSerial(Year(Date()), Int((Month(Date())+2)/3),15)<Date(), Me.Date_IN < DateSerial(Year(Date()), Int((Month(Date())+2)/3)-3,1), Me.Date_IN < DateSerial(Year(Date()), Int((Month(Date())+2)/3),1))

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You show date in international format (31/3/2020). That can be issue. Review http://allenbrowne.com/ser-36.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-25-2017, 08:09 PM
  2. Replies: 3
    Last Post: 02-16-2016, 05:02 PM
  3. Replies: 2
    Last Post: 12-04-2015, 05:51 AM
  4. Replies: 1
    Last Post: 10-30-2014, 10:11 AM
  5. Replies: 2
    Last Post: 07-07-2014, 09:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums