| Version 1.62/1.72 Approx 1 MB (zipped) | First Published 30 Jan 2018 | Last Updated 26 May 2026 |
|---|
UPDATED 25 Apr 2025: Version 1.62 / 1.72
Fixed error in SetWeekNumber function affecting the final week in the calendar year. Changes to modResizeForm. Added internet connection check (v1.72)
The built in Access date picker control works in both 32-bit & 64-bit Access.
It was first introduced with Access 2010 to replace the old ActiveX calendar control which was 32-bit only.
Although it does work, there are some issues:
a) the date picker icon only becomes visible after clicking in a bound date textbox - one extra unnecessary click
b) months/years can only be changed one month at a time - painfully slow to do if you need to enter e.g. a date of birth
c) it takes at least 3 clicks to select a date - click the textbox to make the icon visible; click the icon to open the control then click again to select a date
d) the control doesn't appear for unbound textbox controls (unless these are formatted as dates)
e) the control cannot be moved on the screen
f) the control is quite small - particularly for anyone with eyesight issues
The Better Date Picker is designed to improve on the poor functionality of the built-in date picker
It is a replacement date picker with no Active X controls. It can be used in both 32-bit and 64-bit Access.
The original version of this utility was originally posted in this thread at Access World Forums in Jan 2018
The calendar form was loosely based on open source code by Brendan Kidwell from 2003 which can be found at:
https://glump.net/software/microsoft-access-date-picker
However, I've since made extensive changes to both the appearance and functionality of the calendar form.
If all you want is a visual calendar, it will certainly do that. However, its main purpose is to input a date in a form textbox.
All you need for that is one line of code in the textbox click event:
CODE:
Private Sub txtDate_Click()
InputDateField txtDate, "Select a date to use this on your form" 'Modify text as required
End Sub
The string "Select a date to use this on your form" is used for info on the form and can be adapted to suit.
To use, copy frmDatePicker and modDatePicker to your own application.
Ignore frmMain - its only needed for the example app
Version History
v1.0 30 Jan 2018
Original release. Days displayed in English from Sun to Sat
v1.1 UPDATE - 3 Jan 2022
Following a request at Utter Access forum, I created an alternative version with the calendar display week starting on Mondays and ending on Sundays.
v1.4 UPDATE - 7 June 2022
Significant changes made following suggestions made by @Kitayama at Access World Forums
a) First day of week now automatically assigned according to Windows settings - no need for multiple versions
b) Date format automatically assigned according to Windows regional settings
For example: dd/mm/yyyy (UK); mm/dd/yyyy (USA); dd.mm.yyyy (Germany); d/m/yyyy (Greece); yyyy/mm/dd (Japan)
c) Day and month names are displayed using the regional language currently in use.
d) Out of month days added in dark grey (optional)
e) Days from following month only shown if in same week as last day of current month
f) Selecting an out of month day assigns the correct month automatically. Also works successfully for dates selected in previous year or following year
g) Clicking the Today button resets the calendar to the current month/year & highlights current date ready for selection to confirm
Below are 2 examples in Japanese and Greek with different date formats and day order
Japanese: Sun to Sat - date format yyyy/mm/dd
|
Greek: Mon to Sun - date format d/m/yyyy
|
Click to download: Better Date Picker v1.4 (zipped)
After downloading, unblock the file then extract the ACCDB file and save to a trusted location / enable content.
v1.5 UPDATE - 27 Mar 2023
A further update was made in response to a question by @Psycoperl at Utter Access forum:
I just wonder is it easy to make the fonts bigger on the calendar since I have users with visual acuity issues with small print.
The form design is fairly complex with a total of 64 controls (including 42 date buttons)
Adjusting the size of the form and each of its controls manually would be both time consuming and tedious to do.
The font size could be hard coded but ideally it should also be adjusted for different screen resolutions.
However, updating this is very easy to do using automatic form resizing.
Import the module modResizeForm to your app and then add the following code to the frmDatePicker declarations and the start of the Form_Load event
Private intFontSize As Integer 'font size of date buttons after form resize
'-------------------------------------------
Private Sub Form_Load()
On Error GoTo Err_Handler
'add next 2 lines of code
ReSizeForm Me 'automatically resize form on load
intFontSize = Me.d00.FontSize 'save font size for subsequent redraws
'remaining code follows here....
When the form is opened, it will be scaled up depending on your screen resolution
NOTE: You can adjust the scaling factor by changing the value of the constant DESIGN_HORZRES in modResizeForm
In the attached example app, DESIGN_HORZRES = 1024
If your horizontal resolution = 1920, the form & its controls will be enlarged by a scaling factor = 1920/1024 = 1.875
Increase the value of DESIGN_HORZRES to e.g. 1280 or 1680 if you wish to reduce the scaling factor (and vice versa).
If you don't want to use the automatic form resizing feature at all, disable the line ResizeForm Me in the Form_Load event
For more details, see my series of articles: ResizeForm Me - A Tutorial in Automatic Form Resizing
However, all the date buttons are redrawn each time the month or year is changed.
This would result in all the date buttons reverting to the original font size as in design view
To fix this, we also need to modify two lines of code in the DrawDateButtons procedure
' This method draws the date buttons on the 7 x 6 grid.
Private Sub DrawDateButtons()
'...
'format day colour and set font size
If I = SelectedDay And txtYear = SelectedYear And cboMonth = SelectedMonth Then
'current date - highlight it
Set cmdCurrentDay = btn
btn.BackColor = ColLemon
btn.ForeColor = ColDarkRed
btn.FontWeight = 600
btn.FontSize = intFontSize + 2 'MODIFIED: set to stored font size value +2pt (was 10)
Else
'other dates
btn.BackColor = ColPaleGrey
'currently selected month in blue
btn.ForeColor = ColDarkBlue
btn.FontWeight = 400
btn.FontSize = intFontSize 'MODIFIED: set to stored font size value (was 8)
'...
End Sub
The form will now retain the font size values each time the buttons are redrawn
@Psycoperl also had another question in the Utter Access thread:
Is there a way to provide a starting date for when the field is initially blank? So that it does not default to today?
By default, the calendar opens at whatever date is specified in the textbox control or is set to today if the date is null.
This can easily be changed by altering one line of code - also in the Form_Load event
Private Sub Form_Load()
'. . .
' if there is a valid date to initialize to, use it.
'otherwise, default to current or any other specified date
If IsDate(modDatePicker.InitDate) Then
myDate = modDatePicker.InitDate
Me.txtSelectedDate = myDate
Me.txtSelectedDate.Visible = True
Else
myDate = Date 'CHANGE TO WHATEVER YOU WANT e.g. #1/1/2000#
Me.txtSelectedDate.Visible = False
End If
'. . .
End Sub
Click to download: Better Date Picker v1.5 (with AFR) (zipped)
After downloading, unblock the file then extract the ACCDB file and save to a trusted location / enable content.
v1.62 UPDATE - 25 Apr 2025
A further update was made in response to an email request by Peter Rasmussen. He asked whether I could provide:
. . . an option to show week numbers in the calendar. The week number could be shown in a column before Monday.
In fact, this was a feature I had considered adding many years ago but, until now, nobody had requested it!
The Date Picker form now looks like this with the week number (#) shown on the left:
After selecting a date, the week number is also (optionally) displayed on the calling form:
If you don't require the week number on the calling form, just disable the line ShowWeekNumber in the txtDate_Click procedure:
Private Sub txtDate_Click()
InputDateField txtDate, "Select a date to use this on your form"
DoEvents
'v1.6 - disable next line if week number not needed on this form
ShowWeekNumber
cmdExit.SetFocus
End Sub
The function to get the week numbers is very simple. It uses the system settings to detemine the first week of the year.
Public Function GetWeekNumber(dteDate As Date) As Integer
' Get the week number for the specified date
' vbUseSystemDayOfWeek - use system setting for first day of week (as on calendar) e.g. vbMonday
' vbUseSystem - use system setting for determining first week of year
' e.g. vbFirstFourDays (first week of year with at least four days)
GetWeekNumber = DatePart("ww", dteDate, vbUseSystemDayOfWeek, vbUseSystem)
End Function
This is called by the SetWeekNumbers procedure to update the week numbers when the date picker form is opened.
The week numbers are also updated whenever the month or year values are altered by the user.
NOTE: This function was updated in version 1.61 to fix an error in setting the week number for the final week in the calendar year.
Private Sub SetWeekNumbers()
On Error GoTo Err_Handler
Dim dteDate As Date
'Set the caption for the first week number based on first day of selected month
dteDate = CDate(Me.txtYear & "-" & Me.cboMonth & "-1")
Me.w01.Caption = GetWeekNumber(dteDate)
'restart year for second week number if first week number continues from previous year
If Me.w01.Caption >= 52 Then
Me.w02.Caption = 1
Else
Me.w02.Caption = Me.w01.Caption + 1
End If
'Set the captions for the remaining week numbers
Me.w03.Caption = Me.w02.Caption + 1
Me.w04.Caption = Me.w02.Caption + 2
Me.w05.Caption = Me.w02.Caption + 3
Me.w06.Caption = Me.w02.Caption + 4
'There will always be at least 4 weeks visible but w05 / w06 may not be visible
Me.w05.Visible = Me.d40.Visible
Me.w06.Visible = Me.d50.Visible
'v1.61/1.71 - Check/modify week number for final week of year
If Me.cboMonth = 12 Then
If Me.w06.Visible And Me.w06.Caption >= 52 Then
dteDate = CDate(Me.txtYear & "-12-31")
Me.w06.Caption = GetWeekNumber(dteDate)
ElseIf Me.w05.Visible And Me.w05.Caption >= 52 Then
dteDate = CDate(Me.txtYear & "-12-31")
Me.w05.Caption = GetWeekNumber(dteDate)
End If
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " in SetWeekNumbers procedure : " & Err.Description
Resume Exit_Handler
End Sub
Version 1.62 also includes changes to modResizeForm to fix an unwanted side effect of the automatic form resizing code added in version 1.5.
Until now, the date picker dialog form always opened in the centre of the primary display monitor, even where Access was opened in a different monitor.
In this version, I adapted some code suggested by John Shahilow so that the date picker always now opens on the same monitor as the Access app itself.
This required changes to the declarations section of modResizeForm and to the ResizeForm procedure
The value of DESIGN_HORZRES has also been changed from 1024 to 1280 in modResizeForm.
This slightly reduces the size of the form after scaling to the user resolution. You can modify the value to suit your own preferences. See version 1.5 info for further details.
Click to download: Better Date Picker v1.62 (with AFR & Week Numbers) approx 0.7 MB (zipped)
After downloading, unblock the file then extract the ACCDB file and save to a trusted location / enable content.
v1.72 UPDATE - 25 Apr 2025
This contains all the features from version 1.62 together with code to optionally modify all form captions to the current Office language.
This was done in response to a request by a German developer, Frank Hentzschel, at the recent Access DevCon conference.
The main code changes are:
• New module modLocale including functions GetCurrentOfficeLangCode, GetLocale and SetLocalizedFormCaptions
• New module modTranslate with function TranslateXL. This uses the Google Translate feature and requires an internet connection to run.
• New table tblmsoLang containing language and locale data
When the app opens, an autoexec macro runs a function SetLocalizedFormCaptions which checks whether the Office language is different to that used for the date picker form captions.
If so, a message similar to this will be shown:
The text is in both the current Office language (in this case French) and in English.
If the user chooses to proceed, the captions on both forms are updated. This only takes a couple of seconds but does require an internet connection.
The process only needs to be done once. After translation the form frmDatePicker can be imported into your own apps together with modResizeForm.
If the user is offline, a message similar to this will appear at startup (but only where the Office language is different to that used by the date picker form):
After translation, the two new modules, the table and the autoexec macro are no longer needed . . . unless the Office language is changed again.
These are the two forms after translation to French:
Here are the two forms after translation to German:
The forms can be translated into any of the 100+ languages supported by Google Translate including those based on non-Latin character sets such as Greek, Chinese, Malayalam etc.
NOTE:
As with any automatic translation service, translated words / phrases lack context and are not always accurate. Modify the captions to more accurately represent the meaning as required.
Click to download: Better Date Picker v1.72 (as v1.62 and with caption translation) approx 1.0 MB (zipped)
After downloading, unblock the file then extract the ACCDB file and save to a trusted location / enable content.
IMPORTANT: If you are running a non English language version of Office, restart the app so that the autoexec macro can run at startup and detect the Office language in use.
v1.77 UPDATE - 26 May 2026
This version is based on version 1.72 but allows developers / end users to customise the date picker by marking localised public holidays or custom events on the date picker form.
For full details, see my article: Add Holidays / Events to the Better Date Picker
Video
I have created a short video comparing the functionality of the built-in Access date picker control with my better date picker.
The video was based on an earlier version of the better date picker.
You can watch the Better Date Picker video on my YouTube channel or you can click below:
If you liked the video, please subscribe to my Isladogs on Access
channel on YouTube. Thanks.
Feedback
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Also, do let me know if you find any bugs in the application.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 26 May 2026
|
Return to Example Databases Page
|
Return to Top
|