If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, and using the windows file-picker.
Dates are always a problem to enter into any system because dates are written in different ways in different parts of the world. In the US, dates are written as mm/dd/yyyy, but in most parts of Europe, they are entered as dd/mm/yyyy. In Japan and some other countries, they are entered as yyyy/mm/dd. This creates the opportunity for a lot of confusion when it comes to accepting date inputs on any form you create. If the date is not entered in the format expected by your application, it may generate an error or store the wrong date in the database.
One way to get around this problem is to have separate controls for the month, date and year portions of a date on the form. You can then choose to create fields for these three components of a date in the database table, and combine them into a date using the DateSerial() function for reports, queries and other places where an actual date needs to be used or displayed. The other option is to have the controls for year, month and date be unbound, but have a bound, hidden control on the form to store the date. In the after-update event of each of the visible controls (year, month and day), you can check if none of the three are NULL, and then create the date indicated by the contents of these controls using the DateSerial() function once again. If this control is bound to a date field in a table, the date created will be stored in the table and can be used in queries, reports and other applications.
However, this is not a good solution because it is difficult to validate the date entered by the user. For instance, what happens if the user enters 2010 in the year control, 2 in the month control and 30 in the day control? The DateSerial() function will happily accept 2010, 2 and 30 as arguments and will return the 2nd of March, 2010 as the date entered by the user. In this case, the user probably did not mean that at all. It was either a typographical error on the part of the user, or the user did not know that that February has only 28 days in 2010 (they may have thought that February had 30 days and tried to enter the last day of February rather than the 2nd of March). You either have to write your own extensive error-catching validation code before you use the DateSerial() function. Otherwise, the users are likely to be unhappy with the error-detection capabilities of your form.
By the way, DateSerial() is a very weird and powerful function. It can accept practically any numbers as inputs and somehow convert it into a date. It can accept month numbers that are not in the range 1 through 12 (it will simply consider months below 1 to be in the previous years, and months beyond 12 to be in the next year and so on), and date numbers that are not within that month (any numbers beyond the actual number of days in the month change the month higher internally and negative numbers change the month lower). Thus, for instance, DateSerial(2010, 100, 100) will produce a date output of July 9, 2018!
A date-picker that enables users to enter dates by clicking on a set of valid dates presented to the users in the form of a calendar enables you to get around these problems. Microsoft Access has a built-in ActiveX calendar control that has been designed for this purpose. It is called the Microsoft Date And Time Picker Control.
Using this control is sometimes problematic because the .OCX file that contains this control sometimes does not get installed on the computer correctly when MS Office is installed. If this happens, you will not be able to invoke this control on such a computer. In fact, if you move your Access application that uses this control to such a computer from a computer that had the control, the control will suddenly stop working.
The problem is easy enough to solve though. The ActiveX control is part of a file named MSCOMCT2.OCX. If the file is not installed on the computer or registered properly, you can obtain a copy of the file for your computer from Microsoft directly. It is available in the form of a .CAB file. To open a .CAB file you can use freeware such as 7-Zip to extract its contents. Once you have extracted the contents to a folder on your computer, simply run the included MSCOMCT2.BAT file to install the files in the proper folder and register the controls on the computer.
Once such problem are resolved, using the control itself is very straightforward. To place a date-picker control on your form, open the form in design view. Then open the toolbox that shows all the controls that are available for you to use on the form. The last tool in the toolbox is a generic tool that says "More Controls" when you hover on it with your mouse. Click on it, and wait for Access to take stock of all the controls it has its disposal. It will then produce a long list which you can scroll by moving your mouse to the bottom of what is displayed on your screen. Scroll down to "Microsoft Date and Time Picker Control". Choose the latest version of the control available in the list.
This then changes your mouse cursor to a cross with a hammer next to it. Use this cursor to draw a rectangle on your form where you want the control to go. Note that all you need to be able to use the control is a small rectangle (that is large enough to show the date in text form). The actual calendar pops up when you click on this control, and can extend outside the form itself, so it is a very versatile control that requires very little space on your form. You don't have to size your form to accommodate a monthly calendar unless you feel strongly on aesthetic grounds that the popup calendar has to be contained within the boundaries of the form.
Now, this control is associated with two sets of properties. This can be a little confusing, but I will try to explain so that it is easy to understand as possible. One set of properties is reached by right-clicking on this control and choosing "Properties". This takes you to the set of properties that all form controls in Access have. You can use this property sheet to give this control a proper name, bind it to a table field if necessary, and write events based on the normal events you have available with all form controls. Anything you can do with any other form control, you can do with this form control also through this property sheet. I am not going to dwell on this property sheet since I assume you are familiar with what you can do with form controls in general.
The other set of properties are specific to the type of control this is. This property sheet is accessed by either double-clicking on the control, or by right-clicking on it and choosing DtPicker Object->Properties. This brings up a form with 4 tabs on it: General, Font, Color and Picture. The latter 3 are fairly self-explanatory, so I am not going to spend time on them.
In the General tab though, you have access to several options that control how this control looks and acts. The first option is the Value property of the control. By default, Value is set to today's date. This is the date that is selected by default when the calendar control pops up. It can also be set by code to a different date if necessary. For now, we will leave this option untouched.
Similarly, the next two options are MinDate and MaxDate. This shows you the range over which the control will show the monthly calendar and allow the user to pick. If you are designing an application in which you need to limit the dates the users can pick, you can modify these values to enforce these limits. These limits can also be manipulated by code using the attributes MinDate and MaxDate of the control.
On the right hand side of the property sheet, you have three checkboxes. The first of these, labeled "Enabled" simply enables or disables the control on the form. The second one allows the user to control whether the control is enabled or disabled by putting a checkbox within the control. When the user checks on this checkbox, the control is enabled and it returns a valid date. When the user unchecks this checkbox, the control is disabled, and it returns a NULL value.
The "UpDown" checkbox controls the way users interact with the control. Usually, the UpDown checkbox is unchecked, and it results in the control on the form having a dropdown arrow (just like a list box or combobox). Clicking on this arrow drops down a calendar which allows users to pick a date by clicking on a date. When the UpDown checkbox is checked, the date can be manipulated by clicking on up and down arrows that appear to the right of the control instead of a dropdown arrow. Users can position their cursor on any part of the date (month, date and year), and click on the up and down arrows to increment and decrement that part of the date. I find this mode much less satisfactory to use than the dropdown mode, but that is a personal preference you may choose to disagree with!
The last control on the right hand side is the Time. Since we are going to use this control only to pick dates, we will not do much with this option in this lesson. Now, going back to the left hand side, we see the Format listbox. You can set how the date and/or time are displayed by either choosing long date, short date, time or custom. Since we are worried about using this control to pick only the date, the time format and custom format will not be dealt with in detail in this post. But, short date and long date mean exactly what they sound like: one shows you the date with the day of week, the month spelt out etc., while the other one just shows the date in the abbreviated format.
The next option, MousePointer allows you to change the mouse pointer as it appears when it moves over this control on the form. This is a purely aesthetic decision and I leave it at its default value normally. I think it would be better off placed on one of the formatting tabs rather than the general tab, but I did not design this properties form! The OleDropMode option allows you to set whether the control can handle drag and drop events. It is best to leave it in its default mode: 0 - cc2OLEDropNone. Lastly, the CustomFormat box is for specifying a custom format if you chose custom format under the Format options. To learn more arcane details about this than you could possibly want to, you can refer to this helpful document put out by Microsoft.
Now, how do you use it? You can go in and experiment with it once you have got the basics working. To get the basics working, here is what I would do. Once you have created the control on the form, use the control's properties (accessed by right-clicking and choosing Properties from the menu to set properties such as the name of the control, the table field it should be bound to, and so on. Once you are happy with your settings in that property box, right-click on the control once more and choose DTPicker Object->Properties or double-click on the control.
In the four-tabbed property box that brings up, click on General. Then set the following properties: Leave pretty much everything at the default values. Make sure UpDown is not checked. Pick either short date or long date in the format. Most importantly, make sure the Checkbox property is checked. You are now pretty much done.
You can use the control's properties such as Value, MinDate, MaxDate, etc., to set many controlling values for this control at run time. You can use the form's onload event, the current event or any other event that suits your purposes for setting these values.
Why did I set the Checkbox property to true in this case? The result looks somewhat ugly with a checkbox in front of the date that is not needed in most cases. The problem is that the date-time picker control has a small bug in it. When this box is not checked, and the control is bound to a table field that does not have a default value associated with it, then, every time you move to a new record on the form, you will get the following error message twice: "can't set value to null when checkbox property = false". After you dismiss both error messages, you will be able to enter a date into the control as usual and everything else will work fine, but there seems to be no way to avoid this error message.
Obviously, one way to get rid of the error message is to go to the design of your table and give the field associated with this form control a default value. You can set the default value to a hard-coded single date or to something like "=Date()" (without the quotes) to make the default value dynamic (in fact, you can use any functions you like in building up the default value for the field). If the field has a valid default value, then you can leave the Checkbox property in its default state (off), and you won't be plagued by this stupid error message. So, if you don't mind setting up your table with a default value, then your control will look much better.
The other way to avoid this error is by having two controls to store the date on your form. One of them will be visible but unbound, while the other can be hidden and bound to the actual table field that stores the date. Create the visible control based on the date and time picker and in the On_Exit event of this control, copy the date returned by this control to the hidden control (which can simply be a text box). Now, the control has an On_Updated event also, but for some reason it never seems to fire, so you have to use the On_Exit event. Also, to keep the visible control in sync with the hidden control for existing records in the table, you can use the On Current event of the form to set the value of the visible control using the value loaded in the hidden control. The two sets of code are presented below:
Private Sub VisibleDatePicker_Exit(Code As Integer)When you want to enter a date, you can either click on the date in the control and then manipulate it using your arrow keys, or you can click on the drop-down arrow at the right to reveal the drop-down calendar using which you can pick the date you want. Pretty simple really! It is actually simpler than using the file-picker in that does not require any special coding at all. Hopefully Microsoft will fix the bug in the control that causes that irritating and unnecessary error message to pop up under some circumstances, then the control will become much more usable.
me.HiddenDateControl = me.VisibleDatePicker.Value
Private Sub Form_Current()
If Not IsNull(Me.Text1) Then
Me.DTPicker0.Value = CDate(Me.Text1)
Me.DTPicker0.Value = Date
'You can change the right hand side of this assignment to any value you want
'depending on what you want as the default value in your date picker.
Me.DTPicker0.MinDate = Date - 100
Me.DTPicker0.MaxDate = Date + 100
'You can change the assignments above to set any maximum and minimum allowable dates.
'If your application does not restrict possible dates, you can remove these two
Hope this post has been helpful in solving any problems you might have had with using a date-picker in Access. If you have any problems or concerns with the VBA code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!