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

Function is not available in query expression - HELP

P: 1
Hi everyone

I have created a small db, in access 2000, on my own test machines it works excellent, however, upon transferring it to my colleague, I expierience a strange error code

Function is not available in query expression - 'Left([employee]![dob],2)'

In English - I am trying to pull the first digits in the date of birth, in example 12/08/2006 - should return 12.

This i no problem on my own machine, which have access 2000 (9.0.2812) running on an XP H.E. Sp. 2, however on my colleague's machine he has access 2000 (9.0.6926) sp3

I however fail to see that differences in build number should create this problem.

I have tested my colleagues access, it seems the problem occurs every time i try to use either left, mid or right.

Any good ideas would be highly appreciated.

Best regards
Jesper Hornum
Jul 14 '06 #1
Share this Question
Share on Google+
4 Replies


100+
P: 179
I have never heard of the expression/function called "Left". What is it supposed to do?
Jul 14 '06 #2

P: 28
Can't seem to duplicate your error message - I got a different one "The expression is too complex to be evaluated.... blah blah blah", but you are right this is a little strange as you can indeed specify as criteria a vB function and Left(string,len) is a legitimate function. I have never tried to use the function in the way you seem to be though.

It looks to me like [employee]![dob] is a reference to a field in a table that may or may not be open at the time the query is run. If it is an external reference, why not just include the employee table in the query and make the dob field a column in the query? Then you can specify an expr1 column to represent a simplified cirterion of Left([dob],2) dropping the external reference. That worked for me everytime I tried it that way. Hope that helps.

Best Regards,
Jul 16 '06 #3

P: 1
There is a good chance you have probably solved your problem with the "Function is Not Available"...etc, but here's an answer anyway.

Left, Right, Format (there are others) are all VB commands and even though you are using them in a Query you need to make sure that the module Type Library exists or is correct.

Basically if you start a new module click Tools then References, you should get a pop box. Find the reference called "Microsoft Jet and Replication Objects Library" and make sure it's ticked.

Now your Query will run!!

This can happen when you run a database on another computer that has a new version of these references or if it hasn't been ticked. Not 100% sure exactly what these references are but i think they translate the different languages/commands to work in Access.
Aug 24 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Jesper

In Tools - References on your own system check the libraries that are ticked and the order in which they are ticked. Then go to your colleagues system and check again. You will find that either there is a library missing or that they appear in a different order.

I don't know why the order is important but in my experience it can make a difference. However, the Libraries are a series of prewritten functions in VBA. If you want to use a function in VBA you either have to tick a reference to the library or declare it for use at the top of your code.

The Left() function is a prewritten function. I'm not sure what library its in but you will see when you compare your library list to your colleagues.


Hi everyone

I have created a small db, in access 2000, on my own test machines it works excellent, however, upon transferring it to my colleague, I expierience a strange error code

Function is not available in query expression - 'Left([employee]![dob],2)'

In English - I am trying to pull the first digits in the date of birth, in example 12/08/2006 - should return 12.

This i no problem on my own machine, which have access 2000 (9.0.2812) running on an XP H.E. Sp. 2, however on my colleague's machine he has access 2000 (9.0.6926) sp3

I however fail to see that differences in build number should create this problem.

I have tested my colleagues access, it seems the problem occurs every time i try to use either left, mid or right.

Any good ideas would be highly appreciated.

Best regards
Jesper Hornum
Aug 24 '06 #5

Post your reply

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