Access will not retrieve system date

[aicatech]

I have just created a database for a client. I have used the Date() function throughout the application to retrieve the system date, it has worked fine on my PC as well as theirs. I have just loaded the database to new PCs that the client has had installed. Now wherever I have used Date() the control returns an error #Name? or in the case of a macro it returns an error message Help!!!

 

[CMooreJr]

you know, I have seen it drop the "=" in front of the Date() which would cause it to display the #ERROR....just make sure the controlsource is =Date()

 

[aicatech]

CMooreJR, thanks for the hint. I have since tried again but to no avail. I get the same error on these new PCs wherever I have used Date(), forms where current date is default, reports where system date is used to calculate a client's age or unbound fields to nominate the date of the report. Also get errors in queries used to update workflow tasks. The mistery is that the error only occurs where Date() is used, Now() works fine??

 

[AccessAddict]

Hi
If anyone discovers the solution to this problem I'd love to know it!! I also have exactly the same problem - using Date() function on my PC works fine, but on 4 other PC's Date() returns #ERROR (with or without the = sign) but Now() works fine!! The strange thing is, that on trying to solve the problem on the non-working PC's by simply checking Windows settings (ie Regional Settings in Control Panel etc) for NO apparent reason the Date() function starts to works perfectly. I've searched the MS Knowledge Base unsuccessfully for a solution but came up with nothing. All I know is that the 4 PC's that it didn't work on for absolutely no reason that I can think of (as I didn't do anything other than keep opening the app!!) it now works fine <hmmph>
Regards
Alan

 

[gavinjb]

I have had this problem several times, I found with Access 97 that it was sometimes due to not a full install of Access on the Pc, the best solution I found was to use the following instead =format$(now(),&quot;dd/mm/yyyy&quot and this would give you the same result without the error.

 

[danvlas]

Have you ever compiled the code on the offending PC? You may miss some references...

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[aicatech]

AccessAdict, I feel your frustration, I too have tried regional settings, no settings are different on the offending PCs. Unfortunately I am only able to 'verify' the Access application (gov. client with NT and server based apps), not able to change installation options. Client will not upgrade from Access 97 due to incompatability fears with word (with which their mainframe system interacts). Using Now()and nominating date format fixes the forms and unbounds on reports, but I have used Date() in update and append queries throughout the app.

is anyone able to help??

 

[danvlas]

When distributing your application, first check the references you may have. Open any module (even a new one), Tools-References.

For Access 97, there are only 3 references absolutely needed by Access and those references are available on any machine that has Access installed:

VBA
Access 8.0 Object Library
DAO 3.51

If you have any other reference listed, you have to make sure it is available on the other PCs.

And do compile the code, who knows what you may find...

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[AccessAddict]

Hi
It's almost comforting to know that it's not only me who's experienced the Date() function malfunction LOL. The machines I've had the problems on have been running various combinations of OS's and Access versions. I checked all references (Tools - References) and code is fully compiled and all was as it should be. All I can tell you is that for no apparent reason (as I've not changed any windows or access settings, or re-installed access etc) within the space of an hour or so of tinkering but not changing anything, ALL the machines now recognise the Date() function in forms, queries, reports etc. As I said, if anyone has any logical explanation I'd love to hear it.
Cheers
Alan

 

[aicatech]

It's fixed, but I do not know why!!

The database is split, with the tables on a shared drive and customised apps on each user's personal drive. I found that the Date() function was only playing up in the user's apps, it worked fine in the tables. I created new mdb files and imported all objects from the user's apps into the new files, linked it to the tables on the shared drive and it now works perfectly. A mistery, possibly something to do with the system tables?? (mSysModules, MSysObjects etc)

 

[GingerR]

here is your answer:

faq181-936