Intelligent handling of dates at the start of a calendar year

Did you know 80% of this year's dates can be entered with 4 keystrokes or less? Jan 1 is just 1/1 (or 1 1). Access automatically supplies the current year. Good data entry operators regularly enter dates like this.

But this comes unstuck during the first quarter of a new year, when you are entering dates from the last quarter of last year. It is January, and you type 12/12. Access interprets it as 11 months in the future, when it is much more likely to be the month just gone.

The code below changes that, so if you enter a date from the final calendar quarter but do not specify a year, it is interpreted as last year. But it does so only if today is in the first quarter of a new year.

How to use

To use this in your database:

Copy the function:
- In your database, open the code window (e.g. press Ctrl+G.)
- On the Insert menu, choose Module. Access opens a new module.
- Paste in the code below.
- To ensure Access understands it, choose Compile on the Debug menu.
- Save the module with a name such as ajbAdjustDateForYear.
Apply to a text box:
- Open your form in design view.
- Right-click the text box and choose Properties.
- In the Properties box, set After Update to:
  =AdjustDateForYear([Text0])
  substituting your text box name for Text0.
Repeat step 2 for other your text boxes.

If the After Update property of your text box is already set to:
[Event Procedure]
click the Build button (...) beside this property. Access opens the code window. In the AfterUpdate procedure, insert this line (substituting your text box name for Text0):
Call AdjustDateForYear(Me.Text0)

Optional: If you want to warn the user when an entry will be adjusted, set bConfirm to True instead of False. As offered, no warning is given, as the goal is to speed up good data entry operators. The way it behaves is analogous to the way Access handles dates when the century is not specified.

Limitations

As supplied, the code works only with text boxes (not combos), and only in countries where the date delimiter is slash (/) or dash (-). Other delimiter characters such as dot (.) are not handled.

The code makes no changes if you enter a time as well as a date.

For unbound text boxes, the code does nothing if it does not recognize your entry as a date. Setting the Format property of the unbound text box to General Date can help Access understand that you intend a date.

The function

Public Function AdjustDateForYear(txt As TextBox, Optional bConfirm As Boolean = False) As Boolean
On Error GoTo Err_Handler
    'Purpose:   Adjust the text box value for change of year.
    '           If the user entered Oct-Dec *without* a year, and it's now Jan-Mar, _
                    Access will think it's this year when it's probably last year.
    'Arguments: txt:       the text box to examine.
    '           bConfirm:  set this to True if you want a confirmation dialog.
    'Return:    True if the value was changed.
    'Usage:     For a text box named Text0, set it's After Update property to:
    '               =AdjustDateForYear([Text0])
    '           Or in code use:
    '               Call AdjustDateForYear(Me.Text0)
    'Note:      Makes no chanage if the user specifies a year, or includes a time.
    Dim dt As Date              'Value of the text box
    Dim strText As String       'The Text property of the text box.
    Dim lngLen As Long          'Length of string.
    Dim bSuppress As Boolean    'Flag to suppress the change (user answered No.)
    Const strcDateDelim = "/"   'Delimiter character for dates.
    
    With txt
        'Only if the value is Oct/Nov/Dec, today is Jan/Feb/Mar, and the year is the same.
        If IsDate(.Value) Then
            dt = .Value
            If (Month(dt) >= 10) And (Month(Date) <= 3) And (Year(dt) = Year(Date)) Then
                'Get the Text in the text box, without leading/trailing spaces, _
                    and change dash to the date delimiter.
                strText = Replace$(Trim$(.Text), "-", strcDateDelim)
                
                'Change multiple spaces to one, then to the date delimiter.
                Do
                    lngLen = Len(strText)
                    strText = Replace$(strText, "  ", " ")
                Loop Until Len(strText) = lngLen
                strText = Replace$(strText, " ", strcDateDelim)
                
                'Subtract a year if only ONE delimiter appears in the Text (i.e. no year.)
                If Len(strText) - Len(Replace$(strText, strcDateDelim, vbNullString)) = 1& Then
                    dt = DateAdd("yyyy", -1, dt)
                    If bConfirm Then
                        strText = "Did you intend:" & vbCrLf & vbTab & Format$(dt, "General Date")
                        If MsgBox(strText, vbYesNo, "Adjust date for year?") = vbNo Then
                            bSuppress = True
                        End If
                    End If
                    If Not bSuppress Then
                        .Value = dt
                    End If
                    AdjustDateForYear = True
                End If
            End If
        End If
    End With

Exit_Handler:
    Exit Function

Err_Handler:
    If Err.Number <> 2185& Then     'Text box doesn't have focus, so no Text property.
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "AdjustDateForYear"
        'Call LogError(Err.Number, Err.Description, ".AdjustDateForYear")
    End If
    Resume Exit_Handler
End Function

How it works

We only make a change if:

the text box contains a date (so is not null)
today is in the first quarter of the year (i.e. the Month() of the Date is 3 or less)
the value of the text box is October or later of the current year
the user did not specify a year.

The first two IF statements deal with (a), (b), and (c), but (d) requires a bit more effort. As well as its Value property, a text box has a Text property that exposes the actual characters in the box. The Text property will contain only one delimiter (/) if there is no year.

In practice, Access lets you use the slash (/), dash (-), space, or even multiple spaces as a delimiter between the different parts of the date. The code therefore strips multiple spaces back to one, and substitutes the slash for any dash or space. It then examines the length of the text, compared to the length of the text if you remove the delimiters. If the difference is 1, the user entered only one delimiter, so they did not specify a year. We have now evaluated (d).

If the bConfirm argument tells us to give a warning, we pop up the MsgBox() to get confirmation. Finally, if all these conditions are met, we assign a Value to the text box that is one year less, and return True to indicate a change was made.

The error handler silently suppresses error 2185. If the code runs when another control has focus on its form, the attempt to read the Text property will fail. Normally this could not happen: a control's AfterUpdate event cannot fire unless it has focus. But it could occur if you programmatically call its AfterUpdate event procedure.

The alternative error handler line is provided (commented out) in case you want to use our error logger.

Home

Index of tips

Top