Left$, Left, Mid, Right, Right$ 1

[thisisme]

I just upgraded from v97 to microsoft 2000 and found out that the string functions above (Left, Mid, Right) are not recognized by the VB code in my events. Can someone tell me what I missed doing? My user defined function/queries that used to have these functions used to work in V97. Now it does not work in V2000.<br>
<br>
Thanks,

 

[DougP]

Yes there is a &quot;Reference&quot; that you must check in the VBA program. Open a form or something with code to launch VBA.<br>
in VBA<br>
Click &quot;Tools&quot; Click &quot;References...&quot;<br>
I have these 6 checked in mine<br>
<br>
Visual Basic for Appliactions<br>
Micorsoft Access 9.0 Object Library<br>
OLE automation<br>
Micosoft Active X Data Objects 2.1 Library<br>
Micosoft Internet control<br>
Micosoft DAO 3.6 Object Library<br>

 

[Norm158]

Im an Access Newbie. I'm trying to create a calculated field &quot;ZIPprefix&quot; which consists of the first three digits of another field, &quot;ZIP&quot;, which contains user entered zip codes. In query design view, I've defined a query field as follows:

ZIPprefix: LEFT([ZIP],3)

When I run the query, I get the error message &quot;Undefined function Left in expression.&quot;

I looked at the References referred to in DougP's prior post. They are all already checked.

Can anyone help me figure out how to do what I'm trying to do?

BTW, I also tried a second approach: I defined the length of the ZIPprefix field to be three characters and then used a query formula:

ZIPprefix:[ZIP]

but when I ran the query, the field ZIPprefix had the entire zip code in it, not just the first three characters.

Why didn't that work?

Thanks for any help in clearing up my total confusion.

Norm158

 

[JohnYingling]

Unless I am missing something?
ZIPprefix: LEFT([ZIP],3)
should be
ZIPprefix = LEFT([ZIP],3)
The ZipPrefix: is a label.

http://WWW.VBCompare.Com

 

[tlbroadbent]

Norm158,

I assume ZIPprefix: LEFT([ZIP],3) is in the field name of the query design grid. That syntax is correct. In this case ZIPPrefix is an alias or alternate name for the column. In one sense I suppose you could think of it as a label.

Do as DougP recommended in his post to the thread. Make sure the function libraries are available in Access. Then your query should work. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[dawnd3]

Hi there, I just had this very same issue. What I found out is that these functions are not in 2000 for some reason and that you have to create a custom function yourself. I don't know a lot about it, so please make sure you find other answers, but I thought that I could help a little. The function I was trying to use was &quot;TRUNC&quot; and I had to create a module for it. Dawn

 

[tlbroadbent]

Missing function references is a known issue. See the article at

http://support.microsoft.com/support/kb/articles/Q208/2/18.ASP.

You do not need to create your own functions - at least not common functions like Mid, Left, and Right. Just correct the missing reference. The article mentioned above explains how to do that. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[dawnd3]

Terry, that is good to know, thanks. Does this apply to Round and Trunc? Does the missing library fix those? Because I did replace a library and it still didn't work, but I will read the artical you offered and see if I did something wrong. Thanks!

Dawn

 

[dawnd3]

Hi again, Terry, I just looked at that artical and it seams to apply to only Visual basic functions...and I didn't think of it before but that doesn't solve the issues with the functions that are not in VBA like the ones mentioned above. I don't think the missing library reference will fix the functions I am referring to. Am I totally off on this? I may have gotten confused.

Dawn

 

[tlbroadbent]

The functions mentioned are VB functions which are available for use in queries, text boxes, etc. Round is a standard function and should be availble. If not a library is broken or missing.

TRUNC is not a standard function in Access as far as I know. It is available in Excel and I understand it can be added to Access though I've never done that.

In short, if standard functions (listed in the VB language reference) are not available then there is a problem with a library that can be corrected. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[Norm158]

Terry,

Thanks a lot for your responses, but I'm afraid I'm still at sea:

I did do as DougP suggested: All the libraries he mentioned were already checked.

The artilce you suggested I read said that after getting an error message, there would be an entry &quot;MISSING: <referencename> in the References dialog box.&quot; In fact, there is no entry in the References dialog box about anything missing.

Can you think of any other ways to fix my problem?

And please, do you have any idea why my alternative solution noted above (i.e., defining the query field ZIPprefix to be only three characters long and giving it the formula

ZIPprefix: [ZIP]

doesn't work? What happens is that the entire ZIP code shows in ZIPprefix, not just the first three characters, as desired.

Thanks
Norm

 

[tlbroadbent]

I still think the problem is a bad or missing refernece. Here are a couple of other ideas I gleaned from an Access Newsgroup (comp.databases.ms-access).

[ul]&quot;It's a very common problem and I've never heard of any cause but one -- bad or missing References. Open any module, then on the menu, Tools | References, and fix the bad or missing ones.

&quot;The bad or missing reference is not the one for the DLL containing the builtin functions, most likely. Like the proverbial bad apple, one bad reference can spoil the whole barrel.&quot; L. M. (Larry) Linson[/ul][ul]&quot;I am also having the same probs. When I use vba.mid, vba.right etc it works fine. I also note that there is a reference to Visual Basic for Applications. In fact there are a number of them in the list of references.&quot; PSingh[/ul][ul]&quot;Any time these errors occur, it's usually a problem with the References.

&quot;Open any code module (or open the Immediate Window using Ctrl-G) and select Tools | References from the menu bar. Examine all of the selected References. One or more will probably have &quot;MISSING:&quot; in front of them. Unselect them, and back out. If you think you really do need those references, go back in and reselect. If you don't find any references with &quot;MISSING:&quot; in front, select any other reference at random, back out, then go back in and unselect the reference you just selected.

&quot;What's happening is that the database is point to specific files in specific locations to get its references. If the location of the file changes, or the version of the file changes, Access gets confused. Once it gets confused, it 'stops' looking for the functions. (in other words, just because it can't find a function doesn't mean that there's any problem with the reference to the file that contains that function.) Qualifying the functions with vba. in front helps Access to find the appropriate reference, but avoiding the problem in the first place is probably a better idea.&quot; Doug Steele, Microsoft Access MVP[/ul] Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[dawnd3]

Norm, the same thing happened to me, that is why I am confused. I checked for missing references, and even referenced a couple of libraries that someone suggested. I finally got help from someone online and that was when I was told that these functions were not automatically put in Access 2000 and that I would have to write a module for the funtion. In my case it was TRUNC and the code for the funtion was:

Function TruncCC(X)

Dim Factor As Integer
Factor = 1
TruncCC = Int(X * Factor) / Factor
End Function

Dawn

 

[Norm158]

Terry,

You were absolutely right: there was a missing reference to some sort of a help file that was checked. Even though it had nothing to do with the error message about not finding the 'LEFT' I unchecked it. That fixed the problem. Many thanks for your help.

Dawn,

Hope you find a way to fix your problem.

Norm