Martin Green's Mailbag
|   Home   |   Excel   |   Access   |   Word   |   Outlook   |   VBA   |   eBooks   |   Handouts   |   Site Index   |   RSS   |
Mailbag

Back to the Mailbag

 
Support this site!

Please make a small donation to help support this site.

Thank you.

Why Doesn't the Date Function Work in My Access Database?

Published: 5 December 2018
Author: Martin Green
Screenshots: Access 2013, 2016, Windows 10
For Access Versions: 2007, 2010, 2013, 2016

The Problem: The Date Function Suddenly Stops Working

You build and test your database and everything seems to be working fine. then you roll it out and suddenly the Date function stops working. Perhaps just one or two users have the problem, whilst for others everything works properly. The problem won't arise until the database tries to use the Date function, maybe when a query employing the function is run or, as here (Fig. 1, Fig. 2), when a calculated field on a form doesn't seem to recognize it. What's going on?

A calculated field using the Date function stops working.

Fig. 1 A calculated field using the Date function stops working.
   

The field's expression uses the Date function to calculate Age.

Fig. 2 The field's expression uses the Date function to calculate Age.

In a form's calculated field the problem manifests simply as a #Name? error which usually means that Access doesn't know the function, but you know that it does. If this is the only way the function is used the effect is annoying but the consequences aren't really serious. But if you have used the Date function in your VBA code then you could have real problems. Take this example (Fig. 3) in which a line of VBA code in a form's Open event procedure writes the current date into the form's heading.

The form's Open event procedure includes the Date function.

Fig. 3 The form's Open event procedure uses the Date function to write the date into the heading.

As before, everything worked fine when you built it and maybe on most of the users' computers, but on others it fails. This time the consequences are more serious. An error handler won't help because, as you will see, the problem isn't a runtime error. If you locked up your code with a password, at best the form will refuse to open. If this form happened to be designated to show as the database opened you will probably find that the database crashes! So you need to find what's causing the problem and fix it. Urgently!

The Cause: A Broken Reference

The first thing to understand is there's nothing wrong with the Date function. That's just how the problem manifests itself. It may well affect other functions. I haven't tested any others but this one is very commonly used. If your code wasn't locked with a password (why not?) or perhaps you have unlocked it trying to find out what the problem is, you will see something like this (Fig. 4):

The Visual Basic Editor displays an error message.
Fig. 4 The Visual Basic Editor displays an error message.

The Visual Basic Editor displays a Compile Error and is highlighting the Date function as the cause of the problem. But you compiled the code when you wrote it and it has run perfectly on your computer and perhaps several others. A compile error usually means that the Visual Basic Editor can't compile the code because there is something wrong with it. The clue is in the wording of the message: Can't find project or library.

By Library the message is referring to a Type or Object Library. You usually set a reference to a type library when your code makes use of Automation, the term applied to code running within one program which is controlling another, different program. Now you remember that your database includes some code that talks to Outlook, or Word, or some other program and you have set a reference to that program. So let's take a look at the References dialog (Tools > References) (Fig. 5). Behold!...

A reference is Missing.

Fig. 5 A reference is shown as Missing.

Suppose you built and tested your database in a new version of Microsoft Office (such as Office 365 and Access 2016) and the computers where the problem arises are running an earlier version of Microsoft Office (such as Access 2013). The problem is being caused because the older version of Access does not recognize the newer version of the Outlook Object library. It can't find the specified file so it marks it as Missing, and as a consequence some things don't work (our Outlook code for example) and, mysteriously, the Date function.

With most things in your Microsoft Office programs, backwards compatibility, particularly when considering the previous version, isn't a serious problem... unless you are referring to specific code references because these point to a particular file which will not be present on the computer running the older version of the program. Forwards compatibility should not be a problem at all because the references will automatically update themselves when the code is first run on a newer version.

How to Fix It: Update or Remove the Reference, or Change the Code

So, now you have found out what's wrong. You have several choices when it comes to fixing it. What you do depends upon the circumstances. Perhaps your users are in process of updating their software or hardware and people are using different versions of the program. Maybe you didn't check which version your client was using before you built the database and everyone is using an older version than the one on your development machine. Whatever the cause, you have to do something about it so here are some options:

  • Remove the Reference - You could remove the reference altogether. Simply un-check the Missing reference in the References dialog and click OK. The Date problem will go away but your Outlook (or whatever program it concerns) code won't work. Perhaps you can devise an alternative.
  • Update the Reference - Change the reference to the current version on the user's machine. To do this open the Visual Basic Editor's References dialog (Tools > References) and remove the Missing reference, then find the correct Object Library (Fig. 6), put a tick in its checkbox then click OK.

Set a reference to the correct Object Library.

Fig. 6 Set a reference to the correct Object Library.

If you have a situation where everyone is using the same, older version of Access then changing the reference is fine, but if there is a mixture of versions this won't necessarily be appropriate. I (almost) always split a database so that each user has their own copy of the front-end. If that is the case you can make sure that those users with the older version have the correct references set. But if users with different versions share a front-end, or if the database isn't split, then even if you correct the reference for the older version Access will update it to suit the newer version the first time a user with the newer version uses it, and the problem happens again.

  • Use Late Binding - Late Binding is a form of Automation coding in which it is unnecessary to set a reference to the external application being addressed by your VBA code (the use of References in Automation is called Early Binding). Since no additional references are set the problem of version compatibility doesn't arise. This is probably the ideal solution to the problem.

Learn About Automation in VBA

The ability to control one program from another with VBA code is a great addition to your programming skills and isn't difficult to master. If you would like to learn about Early and Late Binding, and Automation in VBA programming, I have free fact sheet in PDF format that you can read, print or download.
Download here: Microsoft Office VBA - Automation.pdf [346KB]

Summary

Situations in which different users have a mixture of versions of Microsoft Office invariably cause problems. Sometimes it is unavoidable, such as when a large organization is going through a software upgrade, but this is temporary. It isn't uncommon in smaller organizations where each new member of staff is given a new computer equipped with the latest version of the software whilst others are still working with one or more older versions. I often find myself advising clients to get everyone on the same version of both Windows and Microsoft Office.

But, as a developer, you are presented with the situation as it is. Find out which version or versions of the program your client is using and, if possible, develop on that version but, at the very least, test your work thoroughly on all the versions on which it is likely to be used. Wherever possible, split your database so that each user can have their own copy of the front end. This can be tailored to each user's own requirements and, in the worst case, should their front-end crash other users should not be affected.

^ top

 

  ©2000-2018 Martin Green martin@fontstuff.com All rights reserved