Undefined function "left" in expression (QUERY) (1 Viewer)

Mohsin Malik · Dec 18, 2019

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 · Dec 18, 2019

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 · Dec 18, 2019

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 · Dec 18, 2019

Mohsin Malik said:
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?

Code:

SELECT Left("Hello World",5)

Mohsin Malik · Dec 18, 2019

This is my query SQL

Code:

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 · Dec 18, 2019

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

theDBguy · Dec 18, 2019

Mohsin Malik said:
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 · Dec 18, 2019

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

Code:

?Left("Hello World",5)
Hello

theDBguy · Dec 18, 2019

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

Code:

?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 · Dec 18, 2019

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

isladogs · Dec 18, 2019

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 · Dec 18, 2019

Mohsin Malik said:
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 · Dec 18, 2019

Thanks for reply.

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

Code:

Option Compare Database
Option Explicit

2. Yes code compiles without any error.

3. See Screenshot

Mohsin Malik · Dec 18, 2019

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

The_Doc_Man · Dec 18, 2019

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 · Dec 18, 2019

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 · Dec 18, 2019

Mohsin Malik said:
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 · Dec 18, 2019

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

Undefined function "left" in expression (QUERY) (1 Viewer)

Mohsin Malik

Registered User.

theDBguy

I’m here to help

Mohsin Malik

Registered User.

theDBguy

I’m here to help

Mohsin Malik

Registered User.

Mohsin Malik

Registered User.

theDBguy

I’m here to help

Mohsin Malik

Registered User.

theDBguy

I’m here to help

Mohsin Malik

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

Mohsin Malik

Registered User.

Mohsin Malik

Registered User.

The_Doc_Man

Immoderate Moderator

The_Doc_Man

Immoderate Moderator

theDBguy

I’m here to help

isladogs

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread