Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date function error in Access application

Posted on 2008-10-27
7
Medium Priority
?
931 Views
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?
0
Comment
Question by:Al230762
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:peter57r
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
0
 

Author Comment

by:Al230762
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!"
Else
MsgBox ref.FullPath & " : Is OK!"
End If
Next ref

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

Expert Comment

by:peter57r
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Al230762
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.
0
 
LVL 77

Assisted Solution

by:peter57r
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.
0
 

Author Comment

by:Al230762
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.
0
 

Accepted Solution

by:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

877 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