Undefined function "left" in expression (QUERY)

[Mohsin Malik]

Hello,

I started getting this type of error in queries. I am using JKP-Ads Pro Treeview controls whose reference is adding in run-time with AutoExec Macro. There are no missing references. Left also works in immediate window, I am not sure but these functions are failing in access queries and giving me this error. Any idea to fix this?

Thanks
Mohsin

 

[theDBguy]

Hi. Not 100% sure, but the only thing that comes to mind is make sure you don't have any conflicting functions with the same name. Try fully qualifying the name of the function in your query. For example, try using VBA.Left()


Just a thought...

 

[Mohsin Malik]

Hello, thanks for reply. All functions (i-e Left, Right, Mid, Nz, Trim) in my queries are failing. However, in VBA immediate window they are working fine. I have double checked and there are no missing references.

 

[theDBguy]

Hello, thanks for reply. All functions (i-e Left, Right, Mid, Nz, Trim) in my queries are failing. However, in VBA immediate window they are working fine. I have double checked and there are no missing references.

Hi. Can you post the SQL statement for your query? How about trying to create a new query to test each failing function? For example, what do you get with this?

SELECT Left("Hello World",5)

 

[Mohsin Malik]

This is my query SQL

SELECT Employees.IDEmployee, [empfirstname] & " " & [emplastname] AS Employee, Employees.Folder, Employees.EmpEmail, Left([empfirstname],1) & Left([emplastname],1) AS EmployeeInitials
FROM Employees
ORDER BY [empfirstname] & " " & [emplastname];

 

[Mohsin Malik]

I have created a new Query with following SQL SELECT Left("Hello World",5) and getting Undefined function 'left' in expression error.

 

[theDBguy]

I have created a new Query with following SQL SELECT Left("Hello World",5) and getting Undefined function 'left' in expression error.

Okay, so if the Left() function, for example, works in the Immediate Window but not in a query, then perhaps you may need to "repair" your installation of Access. Just a thought...

 

[Mohsin Malik]

If I test this in the immediate window, it returns 'Hello', however queries are failing

?Left("Hello World",5)
Hello

 

[theDBguy]

If I test this in the immediate window, it returns 'Hello', however queries are failing

?Left("Hello World",5)
Hello

Hi. Please see my last reply about possibly having a bad/corrupted install of Access on your machine.

 

[Mohsin Malik]

Thanks, does changing sequence order in VBA reference really matter? As I have one reference which is added on runtime.

 

[isladogs]

I know you said there are no missing references but
1. Have you ticked Require Variable Declaration in the VBE and do you have Option Explicit as the second line in each code module? If not, please do both of those?
2. Does you code compile?
3. Please post a screenshot of your VBA References window and tell us which reference is added at runtime. For info, the order of the references does matter in that Access checks each of those in the order written. For example setting ADO before DAO (or equivalent) will determine which is used by default

 

[theDBguy]

Thanks, does changing sequence order in VBA reference really matter? As I have one reference which is added on runtime.

Hi. Reference sequence/priority matters only if they have the same objects, properties, or methods, and you try to declare them without specifying which reference you meant to use.

 

[Mohsin Malik]

Thanks for reply.

1. Yes I have the declaration on top of module.

Option Compare Database
Option Explicit

2. Yes code compiles without any error.

3. See Screenshot

 

[Mohsin Malik]

The last 02 references ListGrid.accde and TreeView.accde is adding on runtime.

 

[The_Doc_Man]

does changing sequence order in VBA reference really matter?

It can if you don't qualify the object or method in code. For instance if you use both DAO and ADO recordsets in code, you MUST qualify them; otherwise you will build the wrong kinds of recordsets. The rule is "first match wins."

EDIT: I see Colin also mentioned this.

I have experienced cases where some OTHER failing reference stops the code compiler from seeing the correct reference for something else later, even if that something else is from a valid and non-missing reference. I.e. missing the LEFT() function normally would be due to missing the VBA library - but if ANOTHER library in the list precedes the VBA library and the OTHER library is broken, you can't see LEFT() because the search fails when it trips over whatever is really wrong before it reaches LEFT().

 

[The_Doc_Man]

There is a way to test for conflicts of names, by the way.

Open a code window. Now in the menu bar, open the Object Browser. Set it to search ALL libraries. Don't worry; it will only search what is listed in references, not the whole computer full of .DLL files.

Now tell it to find LEFT() or any other of the failing routines and see how many times it occurs. Also see which one comes up first. If it does not come up at all, then use the Object Browser drop-down to select ONE library. Try again and see if it comes up. I suspect ONE of the libraries in your list is lying about not being missing.

 

[theDBguy]

The last 02 references ListGrid.accde and TreeView.accde is adding on runtime.

So, just as a test, try disabling your autoexec macro or open your db with shift bypass and see if the query now works or not.

 

[isladogs]

You didn't say whether Require Variable Declarations is ticked in the VBE Options

There are 4 default references in an Access 16.0 database

Compare that with your very lengthy reference list.
All 4 of the default references are missing and should be added at the top of the list in the order shown. Also:
a) Visual Basic for Applications - FYI that includes the Left function
b) Microsoft Office 16.0 Access database engine Object library - this supercedes the old DAO 3.6 Object Library and has additional functionality. You need to remove the DAO reference then add that

However I recommend you also look at whether all those many references you've added are really needed. If not remove any superfluous references as these are adding unnecessary load to your application
Also, late binding would allow you to remove The Excel & Outlook libraries