By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

Format and date functions not working for users

P: 5
I have an Access 2003 SP2 Application in which I split the data base into 2 files: data-only.mdb and interface.mdb. I then "compiled" the interface file to a .mde file. Both files are on a server. The data-only file stays there permanently, but I have the Users copy the interface file to their PC and use it locally. My problem is that I have a number of queries that use the Format() and date() functions. I have no problem executing them, but my Users cannot execute them. They get an error message that says "Function not available in expressions in query expression ..." where ... is simply a restatement of the query. Oddly, they don't have any problems with the Month() function. Does anyone have any idea as to what is causing this problem? Thanks.
Feb 27 '08 #1
Share this Question
Share on Google+
5 Replies


missinglinq
Expert 2.5K+
P: 3,532
When apps run fine on one machine and not on others the first thing you have to think about are missing references.

Here are MVP Doug Steele's instructions on how to troubleshoot the problem:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to suspect is a references problem.

This can be caused by differences in either the location or file version of certain files between the machine where the application was developed, and where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect them, and back out of the dialog. If you really need the reference(s) you just unselected (you can tell by doing a Compile All Modules), go back in and reselect them.

If none have "MISSING:", select an additional reference at random, back out of the dialog, then go back in and unselect the reference you just added. If that doesn't solve the problem, try to unselect as many of the selected references as you can (Access may not let you unselect them all), back out of the dialog, then go back in and reselect the references you just unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out

http://www.accessmvp.com/djsteele/Ac...nceErrors.html

Just so you know: the problem will occur even if the library that contains the specific function that's failing doesn't have a problem.

**** End Quote ****

Welcome to TheScripts!

Linq ;0)>
Feb 27 '08 #2

P: 5
Ouch! I had seen that comment and checked with one of my users, who is in the same building. She had the same references as my machine. I have users all over the country, so it is difficult to check everyone out individually. Besides, they are all copying an mde file and the references are grayed out for mde files. I would rather not expose my design and code to the users if possible. That is why I chose to go the mde route in the first place. Is it possible that I have to let them use an mdb file?
Feb 27 '08 #3

NeoPa
Expert Mod 15k+
P: 31,769
You can open the MDB file on their machines to check the references. When done, they can go back to using the MDE.
Feb 28 '08 #4

Expert Mod 2.5K+
P: 2,545
Hi. Although it was with MDB rather than MDE files I had exactly this problem on an IT dept-installed laptop version of Office 2003, where date and format functions failed on every database I tried. In one case, a front-end form displaying the user name and date was claiming that the Date() function was invalid.

In the laptop installation a reference to Microsoft Visual Basic Extensibility 5.2 had to be enabled before these very basic functions worked again. It was a pain to find.

-Stewart
Feb 28 '08 #5

P: 5
Hi. Although it was with MDB rather than MDE files I had exactly this problem on an IT dept-installed laptop version of Office 2003, where date and format functions failed on every database I tried. In one case, a front-end form displaying the user name and date was claiming that the Date() function was invalid.

In the laptop installation a reference to Microsoft Visual Basic Extensibility 5.2 had to be enabled before these very basic functions worked again. It was a pain to find.

-Stewart
Thanks, Stewart for the info. I tried it with an expert friend and it didn't work, but it did help lead us to the eventual solution. Apparently, the user with whom I compared references was mistaken when she read one of them to me and it turned out that the missing reference had date and format functions that were superceding the Visual Basic ones. Once I removed the reference, the user was able to see the data. Thanks again.
Feb 28 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.