A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Undefined function 'Left' in expression



 
 
Thread Tools Display Modes
  #1  
Old February 21st, 2008, 10:52 PM posted to microsoft.public.access.queries
The Joker via AccessMonster.com
external usenet poster
 
Posts: 12
Default Undefined function 'Left' in expression

Hello all,

First off I want to say I read a lot of the previous posts about this issue
and none seem to be similar to mine. I am working on a database someone
before me had created, actually it was two databases so I'm combining them
into one since there is no reason to have two of them and I'm automating them
to buttons (instead of manually running multiple queries, macros, etc). Now
when I run through the queries I'm getting the "Undefined function 'Left' in
expression" error. This doesn't deal with VBA, just simply queries but I
still checked for missing references and there were none. I've tried now
creating a basic query with only one field and used the Builder to create an
expression using left on a select query and it still gives me this error.
What would make it so I can't use the Left function in any query? Thanks for
any assistance you can provide!

--
Message posted via http://www.accessmonster.com

  #2  
Old February 21st, 2008, 11:37 PM posted to microsoft.public.access.queries
fredg
external usenet poster
 
Posts: 4,386
Default Undefined function 'Left' in expression

On Thu, 21 Feb 2008 21:52:12 GMT, The Joker via AccessMonster.com
wrote:

Hello all,

First off I want to say I read a lot of the previous posts about this issue
and none seem to be similar to mine. I am working on a database someone
before me had created, actually it was two databases so I'm combining them
into one since there is no reason to have two of them and I'm automating them
to buttons (instead of manually running multiple queries, macros, etc). Now
when I run through the queries I'm getting the "Undefined function 'Left' in
expression" error. This doesn't deal with VBA, just simply queries but I
still checked for missing references and there were none. I've tried now
creating a basic query with only one field and used the Builder to create an
expression using left on a select query and it still gives me this error.
What would make it so I can't use the Left function in any query? Thanks for
any assistance you can provide!



It most likely is a missing reference.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://www.accessmvp.com/djsteele/Ac...nceErrors.html
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
  #3  
Old February 21st, 2008, 11:38 PM posted to microsoft.public.access.queries
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default Undefined function 'Left' in expression

Even though you've said you've checked the references, odds are that's the
problem.

Open any code module. Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/Ac...nceErrors.html, or check my
December, 2003 "Access Answers" column in Pinnacle Publication's "Smart
Access" (you can download the column, and sample database, for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"The Joker via AccessMonster.com" u17775@uwe wrote in message
news:8014e3747fd23@uwe...
Hello all,

First off I want to say I read a lot of the previous posts about this
issue
and none seem to be similar to mine. I am working on a database someone
before me had created, actually it was two databases so I'm combining them
into one since there is no reason to have two of them and I'm automating
them
to buttons (instead of manually running multiple queries, macros, etc).
Now
when I run through the queries I'm getting the "Undefined function 'Left'
in
expression" error. This doesn't deal with VBA, just simply queries but I
still checked for missing references and there were none. I've tried now
creating a basic query with only one field and used the Builder to create
an
expression using left on a select query and it still gives me this error.
What would make it so I can't use the Left function in any query? Thanks
for
any assistance you can provide!

--
Message posted via http://www.accessmonster.com



  #4  
Old February 22nd, 2008, 12:22 AM posted to microsoft.public.access.queries
MGFoster
external usenet poster
 
Posts: 653
Default Undefined function 'Left' in expression

The Joker via AccessMonster.com wrote:
Hello all,

First off I want to say I read a lot of the previous posts about this issue
and none seem to be similar to mine. I am working on a database someone
before me had created, actually it was two databases so I'm combining them
into one since there is no reason to have two of them and I'm automating them
to buttons (instead of manually running multiple queries, macros, etc). Now
when I run through the queries I'm getting the "Undefined function 'Left' in
expression" error. This doesn't deal with VBA, just simply queries but I
still checked for missing references and there were none. I've tried now
creating a basic query with only one field and used the Builder to create an
expression using left on a select query and it still gives me this error.
What would make it so I can't use the Left function in any query? Thanks for
any assistance you can provide!


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Make sure the query doesn't have just the word LEFT without any
parentheses. If that is OK, it may not necessarily be a MISSING
reference as a reference that isn't checked. Make sure the reference
"Visual Basic for Applications" is checked. The Left() function in
Access is a VBA function.

About combining the databases - if the dbs are a front-end (forms,
reports, queries) and a back-end (only tables) then the reason there are
2 dbs may be that the original creator planned to deploy the front-end
amongst the users (each user requires a copy of the front-end on their
PC - the front-end has links to the tables in the back-end). Forgive me
if you already know this, but so many people who post here haven't the
slightest idea about this concept.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR74HzIechKqOuFEgEQLrGACg1Vs20f4qNvuXS+ZIBjMLFh 8EnW4AoMNO
AHikJH/jpmeqLxkUZ63C7QFS
=imgG
-----END PGP SIGNATURE-----
  #5  
Old February 22nd, 2008, 09:04 PM posted to microsoft.public.access.queries
The Joker via AccessMonster.com
external usenet poster
 
Posts: 12
Default Undefined function 'Left' in expression

Thank you so much everyone for your assistance. I still can not find my
issue though after trying all your suggestions and reading attached websites.

I've added a random reference and removed it to refresh the references and it
still doesn't work. I've compiled it. I've looked at the references in the
original version and made sure they are all EXACTLY as the previous designer
had it. I've also tried it on a few difference computers. All to no avail.
I've still not seen any "Missing" references in this database.

MGFoster,
I do have the parenthesis on it; I've used Access pretty in depth, though I'm
definitely no Douglas Steele or Klatuu. Also I do have VBA checked in my
references. There are 2 databases because of poor design not because of any
thought of front end/back end. One does the Access queries/code and the
other is just a "scrape" that takes information from our mainframe which you
have to manually import the results into the other database.

I tried adding a Left statement in the code just to see if it would run and
it produces the same error. The following are the references I have checked:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library (I've tried it with and with out
this one)
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
and one for our mainframe for the "scrape"

Thank you all again for your assistance and any more thought you may have on
this would be GREATLY appreciated.

--
Message posted via http://www.accessmonster.com

  #6  
Old February 22nd, 2008, 09:29 PM posted to microsoft.public.access.queries
fredg
external usenet poster
 
Posts: 4,386
Default Undefined function 'Left' in expression

On Fri, 22 Feb 2008 20:04:32 GMT, The Joker via AccessMonster.com
wrote:

Thank you so much everyone for your assistance. I still can not find my
issue though after trying all your suggestions and reading attached websites.

I've added a random reference and removed it to refresh the references and it
still doesn't work. I've compiled it. I've looked at the references in the
original version and made sure they are all EXACTLY as the previous designer
had it. I've also tried it on a few difference computers. All to no avail.
I've still not seen any "Missing" references in this database.

MGFoster,
I do have the parenthesis on it; I've used Access pretty in depth, though I'm
definitely no Douglas Steele or Klatuu. Also I do have VBA checked in my
references. There are 2 databases because of poor design not because of any
thought of front end/back end. One does the Access queries/code and the
other is just a "scrape" that takes information from our mainframe which you
have to manually import the results into the other database.

I tried adding a Left statement in the code just to see if it would run and
it produces the same error. The following are the references I have checked:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library (I've tried it with and with out
this one)
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
and one for our mainframe for the "scrape"

Thank you all again for your assistance and any more thought you may have on
this would be GREATLY appreciated.


How about posting the complete exact expression you are trying to use?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
  #7  
Old February 22nd, 2008, 09:55 PM posted to microsoft.public.access.queries
The Joker via AccessMonster.com
external usenet poster
 
Posts: 12
Default Undefined function 'Left' in expression

fredg,

It happens at ANY occurance of the Left function. In a query or in VBA.
Below is the exact usage of it. NALID2 is an address field and we are
looking for P.O. Boxes.
Pbox: Left([NALID2],4)

But I've tested it in code and a blank query with an example like: Left
("Blah",2) Which I would have though would give me "Bl" back.

fredg wrote:
Thank you so much everyone for your assistance. I still can not find my
issue though after trying all your suggestions and reading attached websites.

[quoted text clipped - 26 lines]
Thank you all again for your assistance and any more thought you may have on
this would be GREATLY appreciated.


How about posting the complete exact expression you are trying to use?


--
Message posted via http://www.accessmonster.com

  #8  
Old February 23rd, 2008, 12:18 AM posted to microsoft.public.access.queries
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default Undefined function 'Left' in expression

I don't believe you should have references to both Visual Basic for
Applications and Microsoft Visual Basic for Applications Extensibility 5.3.

Try removing the Extensibility one.

Also, see whether using VBA.Left works.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"The Joker via AccessMonster.com" u17775@uwe wrote in message
news:8020857912f36@uwe...
Thank you so much everyone for your assistance. I still can not find my
issue though after trying all your suggestions and reading attached
websites.

I've added a random reference and removed it to refresh the references and
it
still doesn't work. I've compiled it. I've looked at the references in
the
original version and made sure they are all EXACTLY as the previous
designer
had it. I've also tried it on a few difference computers. All to no
avail.
I've still not seen any "Missing" references in this database.

MGFoster,
I do have the parenthesis on it; I've used Access pretty in depth, though
I'm
definitely no Douglas Steele or Klatuu. Also I do have VBA checked in my
references. There are 2 databases because of poor design not because of
any
thought of front end/back end. One does the Access queries/code and the
other is just a "scrape" that takes information from our mainframe which
you
have to manually import the results into the other database.

I tried adding a Left statement in the code just to see if it would run
and
it produces the same error. The following are the references I have
checked:
Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library (I've tried it with and with
out
this one)
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
and one for our mainframe for the "scrape"

Thank you all again for your assistance and any more thought you may have
on
this would be GREATLY appreciated.

--
Message posted via http://www.accessmonster.com



  #9  
Old February 25th, 2008, 04:24 PM posted to microsoft.public.access.queries
The Joker via AccessMonster.com
external usenet poster
 
Posts: 12
Default Undefined function 'Left' in expression

Douglas,

I removed the reference and the issue still remains. I added vba.left to the
expression and now it says Undefined function 'vba.Left' in expression. Any
more thoughts would be appreciated. Thanks for all your help.

A random thought: Not that I've seen it but could a variable or something
being Dim'ed as "left" be creating this? I would think that could confuse
Access.

--
Message posted via http://www.accessmonster.com

  #10  
Old March 2nd, 2008, 04:55 PM posted to microsoft.public.access.queries
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default Undefined function 'Left' in expression

Yes, if you've got anything named Left in your application (a table, a field
in a table, a variable, a function, a module, etc.), Access is likely to get
confused.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"The Joker via AccessMonster.com" u17775@uwe wrote in message
news:8043cc83e403d@uwe...
Douglas,

I removed the reference and the issue still remains. I added vba.left to
the
expression and now it says Undefined function 'vba.Left' in expression.
Any
more thoughts would be appreciated. Thanks for all your help.

A random thought: Not that I've seen it but could a variable or something
being Dim'ed as "left" be creating this? I would think that could confuse
Access.

--
Message posted via http://www.accessmonster.com



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.