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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|