Undefined Function 'Date' in expression

[Seth Schrock]

I'm getting an error when trying to open my database on a user's PC: Undefined Function 'Date' in Expression. From looking online, it looks like it is a reference issue. The problem is that the user is using Access Runtime so I can't check the references in the VBA editor. Is there any other way?

[zmbd]

Create you a shortcut to the regular Access program (ie cut and paste) edit the short cut so that you now have:

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /runtime

Rename it so that you know that the shortcut opens in runtime.

Now drag and drop your database file ontop of this shortcut.

This will allow you to check how your database will work (or not ;) ) when using the MSRT.

Provided all of your references are correct on your PC then the file should work fine. If it does then there is an issue with the installation on the client PC.

[Seth Schrock]

I think that I have solved the issue. We had a spare copy of Access on the shelf so we installed it on the trouble PC. The Microsoft Common Control Library reference was missing. I fixed this and it works. I just need to make sure that it still works once I remove the full version of Access. I just wonder why this reference was causing a problem with the Date function.

I'm in the process of finding code online to test the references to see if they are broken and if they are, to reconnect them. So far, I've read that this would be called from an AutoExec macro so that it can run before any form gets loaded.

[Stewart Ross]

Hi Seth. The Date() function problem is a manifestation of the built-in VBA library itself no longer functioning. You would find if you took out the first line on which the Date() problem occurred you would likely get a failure at the use of whatever was the next VBA function called.

In a full Access database this is not so much a reference issue as one of corruption of the database concerned, or as a result of competing versions of DLLs being present in installations where there are multiple versions of Access active (e.g. A2003 and A2007).

-Stewart

[Seth Schrock]

Well, out of nine PCs running this database (all FEs copied and pasted from the same file) only one is having this issue. Also, copying and pasting again did nothing to fix it, so I'm not sure corruption is the case here. Looking online, I found many cases of this that were fixed by fixing the references. I was just surprised that the reference that was missing in my case was the MS Common Control Library and not the Office 14 or the Access 14 reference.

Anyway, I'm now in the process of removing the full version of Access to test it with just Access Runtime.

Side note: I have had this issue before with another database. I solved it by rebuilding the form that time, but at the time I didn't have any extra Access license to get the references fixed, so I was hoping that

[jimatqsi]

This problem can be caused by installing 64-bit Access and 32-bit Access. In my experience, folks install 64-bit Office/Access, discover what a bad idea that is because of all the 32-bit supporting code they've got, then removed 64-bit and install 32-bit. Then, bingo, you've got this problem.

Sometimes I've been able to solve this by simply copying the Microsoft Common Code folders relevant to MS Office (C:\Users\Jim\Documents\Clients\SMI\Microsoft Shared) but today that didn't work. So I found this thread and started experimenting. I created a new Access database, imported everything and that appeared to work.

Then I started over. I couldn't find the missing reference. So I decompiled the app and that did everything I needed! So, at least in some cases, decompile the app is all you have to do. The command line entry for that in my case today is :

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\Public\Access\QSI.mdb"  /decompile

Change this depending on what version of Office/Access you're running and the path to your database file.

Thanks again to all the experts and contributors at Bytes.com

Jim

[NeoPa]

Thanks for that Jim. Very helpful.

Just to add to what you've already said, these addresses can be found using the environment variables {USERPROFILE}, {ProgramFiles(x86)} & {PUBLIC} thus :

%USERPROFILE%\Documents\Clients\SMI\Microsoft Shared
%ProgramFiles(x86)%\Microsoft Office\root\Office16\MSACCESS.EXE" "%PUBLIC%\Access\QSI.mdb" /decompile

This ensures that the strings are portable between systems.