Date function error in Access application

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?
Al230762Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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
0
Al230762Author Commented:
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!"
Else
MsgBox ref.FullPath & " : Is OK!"
End If
Next ref

all references were shown as OK. Any other ideas?
0
peter57rCommented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Al230762Author Commented:
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.
0
peter57rCommented:
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.
0
Al230762Author Commented:
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.
0
Al230762Author Commented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.