Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Date function error in Access application

Posted on 2008-10-27
Medium Priority
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?
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.


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 800 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

730 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