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
  #11  
Old May 26th, 2010, 09:37 PM posted to microsoft.public.access.queries
Goran Igaly
external usenet poster
 
Posts: 1
Default Unefined function Left in Query

I suppose the problem is when you move mdb file to another computer. In my case, I did the following test:

1)
Create a query:
SELECT Left([code],2) AS Expr1 FROM documents;

This query produced error "Undefined function Left"

2)
After removing reference to Microsoft DAO 3.6 Object Library, this query worked without problems.

3)
When I tried to compile the code, I got the Compile Error (because of missing reference)

4)
When I re-create reference to Microsoft DAO 3.6 Object Library, my query worked, and I can compile the database.


Conclusion - when I move from one to another computer, I have to re-create reference to DAO 3.6 Library.



Douglas J. Steele wrote:

Yes, if you've got anything named Left in your application (a table, a field
02-Mar-08

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...

Previous Posts In This Thread:

On Thursday, February 21, 2008 4:52 PM
The Joker via AccessMonster.com wrote:

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

On Thursday, February 21, 2008 5:37 PM
fredg wrote:

Undefined function 'Left' in expression
On Thu, 21 Feb 2008 21:52:12 GMT, The Joker via AccessMonster.com
wrote:



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/AccessReferenceErrors.html
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Thursday, February 21, 2008 5:38 PM
Douglas J. Steele wrote:

Even though you've said you've checked the references, odds are that's the
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/AccessReferenceErrors.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...

On Thursday, February 21, 2008 6:22 PM
MGFoster wrote:

The Joker via AccessMonster.
The Joker via AccessMonster.com wrote:

-----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-----

On Friday, February 22, 2008 3:04 PM
The Joker via AccessMonster.com wrote:

Thank you so much everyone for your assistance.
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

On Friday, February 22, 2008 3:29 PM
fredg wrote:

Undefined function 'Left' in expression
wrote:


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

On Friday, February 22, 2008 3:55 PM
The Joker via AccessMonster.com wrote:

fredg,It happens at ANY occurance of the Left function. In a query or in VBA.
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:

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

On Friday, February 22, 2008 6:18 PM
Douglas J. Steele wrote:

I don't believe you should have references to both Visual Basic for
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...

On Monday, February 25, 2008 10:24 AM
The Joker via AccessMonster.com wrote:

Douglas,I removed the reference and the issue still remains. I added vba.
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

On Sunday, March 02, 2008 10:55 AM
Douglas J. Steele wrote:

Yes, if you've got anything named Left in your application (a table, a field
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...


Submitted via EggHeadCafe - Software Developer Portal of Choice
Entity Framework 4.0 and the AJAX Autocomplete Extender.
http://www.eggheadcafe.com/tutorials/aspnet/77429274-e89f-49c2-a93a-b290f013f649/entity-framework-40-and.aspx
  #12  
Old May 26th, 2010, 09:54 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Unefined function Left in Query

It doesn't always happen, and it doesn't always involve the DAO library,
but you have outliined the steps to fix it when it does happen. The only
thing you left out is how the Reference looked in the dialog box before
you removed it. I assume the entry for the DAO libray was prefixed by
the word "MISSING: ".


Goran Igaly wrote:
I suppose the problem is when you move mdb file to another computer.
In my case, I did the following test:

1)
Create a query:
SELECT Left([code],2) AS Expr1 FROM documents;

This query produced error "Undefined function Left"

2)
After removing reference to Microsoft DAO 3.6 Object Library, this
query worked without problems.

3)
When I tried to compile the code, I got the Compile Error (because of
missing reference)

4)
When I re-create reference to Microsoft DAO 3.6 Object Library, my
query worked, and I can compile the database.


Conclusion - when I move from one to another computer, I have to
re-create reference to DAO 3.6 Library.



Douglas J. Steele wrote:

Yes, if you've got anything named Left in your application (a table,
a field 02-Mar-08

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...

Previous Posts In This Thread:

On Thursday, February 21, 2008 4:52 PM
The Joker via AccessMonster.com wrote:

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

On Thursday, February 21, 2008 5:37 PM
fredg wrote:

Undefined function 'Left' in expression
On Thu, 21 Feb 2008 21:52:12 GMT, The Joker via AccessMonster.com
wrote:



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/AccessReferenceErrors.html
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Thursday, February 21, 2008 5:38 PM
Douglas J. Steele wrote:

Even though you've said you've checked the references, odds are
that's the
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/AccessReferenceErrors.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...

On Thursday, February 21, 2008 6:22 PM
MGFoster wrote:

The Joker via AccessMonster.
The Joker via AccessMonster.com wrote:

-----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-----

On Friday, February 22, 2008 3:04 PM
The Joker via AccessMonster.com wrote:

Thank you so much everyone for your assistance.
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

On Friday, February 22, 2008 3:29 PM
fredg wrote:

Undefined function 'Left' in expression
wrote:


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

On Friday, February 22, 2008 3:55 PM
The Joker via AccessMonster.com wrote:

fredg,It happens at ANY occurance of the Left function. In a query
or in VBA.
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:

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

On Friday, February 22, 2008 6:18 PM
Douglas J. Steele wrote:

I don't believe you should have references to both Visual Basic for
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...

On Monday, February 25, 2008 10:24 AM
The Joker via AccessMonster.com wrote:

Douglas,I removed the reference and the issue still remains. I added
vba.
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

On Sunday, March 02, 2008 10:55 AM
Douglas J. Steele wrote:

Yes, if you've got anything named Left in your application (a table,
a field
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.


--
HTH,
Bob Barrows


 




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 07:26 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.