Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access VBA Error - Undefined Function 'Left' in Expression

Posted on 2012-04-04
9
Medium Priority
?
2,698 Views
Last Modified: 2012-04-04
Hello,

My Access database has recently had trouble any time I run code, a query or report that calls one of the standard functions (Left(), Right(), Date(), etc.).  I get an error "Undefined Function 'Left'  in Expression"

When I compile my code, I get no compile-time errors.

I know this is frequently associated with broken references, however I have no missing references (see the attached image).

Any thoughts?

Thanks.
References.png
0
Comment
Question by:Orcadian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 75
ID: 37809503
Well, 99% of the time ... Classic case of Missing References.

Is this an A2010 db?  If so, is it in a Trusted Location ?
0
 

Author Comment

by:Orcadian
ID: 37809507
I agree, which is why I checked the references, and you can see that none are missing.  That's when I got stumped.

This is an Access 2007 DB and it is in a Trusted Location.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 37809508
OK ... so it's A2007.  Still, it needs to be in a Trusted Location.  Has the db been moved?

If not, try running the procedure below (ignore the Reference part):


Open the VBA Editor and from the menu ...Tools>>References ....
If you see any listed as **Missing: <reference name>, including the asterisks and the word Missing, the
you need to fix that first.

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

AND ...
Once you get familiar with the Decompile idea (and ALWAYS make a BACKUP first!) ... you can add both Decompile and Compact/Repair to the Right Click menus in Windows Explorer, which I use multiple times daily:

Getting the Decompile and Compact context menu options
http://access.mvps.org/access/modules/mdl0039.htm

mx
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Closing Comment

by:Orcadian
ID: 37809530
Decompile did the trick.  Thanks!
0
 
LVL 75
ID: 37809532
Man, I'm on a roll with Decompile today ... 3x , lol.

cool.mx
0
 

Author Comment

by:Orcadian
ID: 37809534
Haha, that's great.  That Decompile and Compact context menu link looks useful, but the download link on their site is broken.  Do you know of another place I can get it?
0
 
LVL 75
ID: 37809542
Well, I just got to the link.  The site has been intermittent recently ... try again.  If you cannot get there ... let me know.

Yes ... that is MUST HAVE.  I use it daily.  Be SURE to open in Notepad first and tweak the folder paths for your installation of Access. You can also a entries for ACCDA, ACCDE (Compact only of course) ... and MDB/E/A also.

mx
0
 

Author Comment

by:Orcadian
ID: 37809607
The link to the website works (http://access.mvps.org/access/modules/mdl0039.htm), but on their site, when I click on "Download AccessReg.Reg" I get a 404 file not found error.
0
 
LVL 75
ID: 37809635
OOPS ... Humm. Bummer.

OK ... attached is the basic file ...with A2010 stuff ... easy to tweak for whatever version .
I renamed .REG to .TXT to upload.  Open in NotePad, tweak as necessary, then change back to .REG and double click to add to Registry.

Quick and simple.

mx
RightClickCompactDecompile2010-W.txt
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question