Date function error in Access application

Posted on 2008-10-27
Last Modified: 2013-11-27
We have a commercial application developed in MS Access 2003 which has been successfully operating for 12 months during which there have been a number of updated versions. Th app. has a frontend and a backend database. It is distibuted on CD with MS Setup Bootstrapper and installs MS Access Runtime. In our latest update we find that Date functions in SQL queries are causing the following error:
'Function is not available in expressions in query expression 'Date()'
on XP machines (both SP2 and SP3 and we THINK with no MS Office applications installed). This type of query is causing the error:
"DoCmd.RunSQL "UPDATE School SET School.YrGpUpdated = Date();"
if we change the Date function to the Now function, the query works - but we have a lot of Date functions and want to avoid changing them all.  This current version has been develoepd in the same version of MS Access; it has the same DLLs and references as the previous version; the same Package and Deployment and Runtime version. Any ideas on why this might suddenly be happening?
Question by:Al230762
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 77

Expert Comment

ID: 22819691
I would say that it is almost certain that this is a references problem.
Difficult to diagnose in a runtime but you can use code in your app startup to check for missing refs.

Dim ref As Reference    
'Find the DAO reference    
For Each ref In Application.References        
If ref.IsBroken Then            
MsgBox ref.Name & " : Is Missing/Broken!"        
End If    
Next ref

Author Comment

ID: 22819816
peter57 - I agree it seems like a reference problem. I tried your suggestion on the target machine where the problem is occurring and even added a positive confirmation that each reference was OK:
If ref.IsBroken Then
MsgBox ref.FullPath & " : Is Missing/Broken!"
MsgBox ref.FullPath & " : Is OK!"
End If
Next ref

all references were shown as OK. Any other ideas?
LVL 77

Expert Comment

ID: 22819883
I would still opt for a refs problem - but maybe it is a file version difference rather than a missing file.

Are you using an mdb or an mde in your runtime?
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.


Author Comment

ID: 22819917
We use an mde developed in Access 2003. When I take the mde from a previous build of the application and place it on the target machine, that works fine. When I open and re-compile that previous build on my development machine and then place that new mde on the target machine it behaves exactly as the current version, i.e with errors. Things have obviously changed on my development machine between the previous build and the current build and yet all the references appear to be unbroken on the target machine for both the previous build and the current build.
LVL 77

Assisted Solution

peter57r earned 200 total points
ID: 22820150
Well obviously an mde is more picky than an mdb when it comes to references.
But all i can think of now is to go through each ref in turn and locate the file and check the version numbers between the two machines.

If the target machine has no office software installed then it won't be getting updates, I would assume.

Author Comment

ID: 22820219
The target machine I am testing on is a virtual machine with XP Pro SP3 and all the latest updates but no MS Office at all. We have customers with this same setup and they have reported the problem to us.

We have now replaced all instances of the Date() function with Now() and placed the new mde on the test target machine. It works fine! We are continuing to test but it seems as if other likely candidates to crash e.g Left(), Nz, Mid, Right etc are all functioning OK. We cannot find any problem other than Date().

I'm sure you are right in that it's related to updates and/or references but it's going to be hellish long job for us to discover what the precise problem is. I'd like to know if anybody else has had this problem.

Accepted Solution

Al230762 earned 0 total points
ID: 22888644
It appears that my development machine had recently applied the Office 2003 Service Pack 3. On compiling the application and then deploying on a machine without any Office product installed or without Office 2003 SP3 installed, the Date() function produced an error message. Answer was to remove any Date() functions, particularly in queries, and replace with Now() - just another of Microsoft's little surprises. I suspect that the VBE6.DLL may be the culprit and that this should possibly be applied to the target machine but I'd rather change my code than so that.

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question