Search The Web

Today's Headlines

Monday, June 7, 2010

Microsoft Access Tips & Tricks: Using The Date-Picker

Just as the windows file-picker allows users to enter file names (along with their full paths) into Microsoft Access forms without having to type it in by hand, the date-picker allows a user to enter dates into form controls without having to type in actual dates. The date-picker is a pop-up calendar that allows users to enter dates just by clicking on a date in the calendar.

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.
Click on More Controls in toolbox
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.
Select Microsoft Date And Time Picker Control
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.
Place control on 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.

Set options for MS Date time pickerIn 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)
me.HiddenDateControl = me.VisibleDatePicker.Value
End Sub

Private Sub Form_Current()
If Not IsNull(Me.Text1) Then
Me.DTPicker0.Value = CDate(Me.Text1)
Else
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.
End If
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
'assignments entirely.
End Sub
Using the drop-down calendarWhen 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.

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!

17 comments:

bryn_officeteam said...

Thanks for posting these great Microsoft Access tips & tricks! I am sure many users will find this handy!

You should share your knowledge with the community of Microsoft Access users over on Facebook:
http://www.facebook.com/MicrosoftAccess

Cheers,
Bryn
MSFT Office Outreach Team

Anonymous said...

This post was very helpful.

How do you handle a Datepicker field error (not updating) because it is hidden.
"An error occurred in a call to the Windows Date and Time Picker control."

Blogannath said...

A form control that is just hidden should not have any problem being updated. Only if the control is not enabled or it is locked, there could be problems with updating it. A more detailed description of the structure of your form and the error you are getting (along with the series of actions that lead to the error) may help in my effort to figure out what may be going wrong. But, first, check the other properties of the control and make sure the control's enabled property and locked property are set correctly (enabled should be yes and locked should be no).

Rob said...

The problem occurs when I have multiple DTPicker fields in which some are hidden and some are not. (enabled and not locked) The form opens okay, but when you go to the next record the errors start popping up. If I leave all the controls visible, I do not have the problem.

Blogannath said...

That is interesting. I have never worked with multiple DTPicker controls on the same form, so this may be something I have missed. Are the controls bound or unbound? What is the full error message? What are the settings of the DTPicker properties? Are you using any special coding behind the form to move stuff from unbound controls to bound controls and so on?

Rob said...

DTPicker controls are bound to date fields in a table. I start with the control visible. I hide the date fields if the check box is not checked. So on a form there are several check boxes and several date fields. The error is as stated above in a Msgbox.

Blogannath said...

Sorry about the long delay in getting back. I have been trying to duplicate the problem, but I am missing something. I don't get the error message you get.

I am wondering if something is wrong with the form itself in your application. Have you performed a compact and repair on your database? If that does not resolve it, try creating a new form and see if the error carries over to the new form also. If one of these steps solves your problem, it was probably because of some kind of corruption issue.

If this does not solve your problem, then start out with one DTPicker ccontrol on your form, then when that works fine, add one more and so on and see when exactly the error starts appearing. Maybe there is something wrong with the particular field that control is bound to (not allowing nulls, or something like that).

Please do post back what you find, and I will think about it in the meantime and see if there is something else that could be going on.

Rob said...

I repeated the error with a very simple set up. Created a table with two date fields and a yes/no field. Created a form with two DTPicker fields and a check box. I put code behind the Form.Current to hide the second DTPicker field if the checkbox was true. I then put in a couple of records and checked the box on a couple. Then if you go from record to record you get an error. (Access 2003, XP) I got around it by creating my own record navigation buttons and a timer on the mouse scroll button.

Anonymous said...

Hi, thank you for the article! Have a question. After picking date in DatePicker control, which action I should use to update listbox? "After Upfate and On change don't seem to work untill I press enter or tab key? Thank you!

Blogannath said...

What listbox are you trying to update? If you associate a control with the datepicker, I think only the onexit(), lostfocus() and other events associated with leaving the control will fire. Afterupdate() and onchange() may not fire because the update is considered to occur programmatically rather than because the user typed something in the control.

Anonymous said...

Thank you Blogannath! I have text box with date (and Datepicker) and list box underneath with appointments. I want to pick a date and list box automatically update after I select date but it doesn't happen unless I press enter after choosing a date or tab. Thank you, Tim.

Blogannath said...

I understand what you are trying to do. Unfortunately, since afterupdate() and onchange() do not fire, you have to wait till the user tabs away from the date field before you can refresh the listbox. If the user's next step is to click on the listbox to pick an appointment from it, then this is not going to be a problem since clicking on the listbox will cause the lostfocus() or onexit() event of the date control to fire.

Anonymous said...

Thank you Blogannath. Actually, user can not pick appointment from list box since it's not showing appointment until you click on it :) It's a kind of chicken and egg situation but, I found a solution, I am using GoToControl command after update and it seems to work well. thank you again, Tim.

Blogannath said...

That is good to know. Thank you for sharing your solution with everybody.

Jacqui said...

Thank you Blogannath. Problem is I am not finding the Properties Window for the Date Picker - I've right clicked and dblclicked on the textbox, and only get the regular text box properties window. Any suggestions?
Using Access 2010, the field is date/time and the date picker is performing properly in Form view.

Blogannath said...

I am not familiar with the controls in Access 2010. But it does look like things have changed from Access 2003 to Access 2010, with the original date picker control being deprecated. Please take a look at the following article and see if it solves the problem you are having: http://msdn.microsoft.com/en-us/library/gg251104%28v=office.14%29.aspx

Anonymous said...

If you don't want any text in that control then use the custom format : ( Access 2003 )

If Not IsNull(date.Value) Then
dtpicker.Value = date.Value
dtpicker.CustomFormat = "dd-MM-yyyy"
Else
dtpicker.CustomFormat = " "
End If

Maćko.

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web