Function is not available in query expression - HELP

[Cantell]

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

[comteck]

I have never heard of the expression/function called "Left". What is it supposed to do?

[zzqxxq]

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,

[spike]

There is a good chance you have probably solved your problem with the "Function is Not Available"...et c, 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.

[MMcCarthy]

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