Right Function 3

[tdonahue]

I am having problems using my Right function command.
I have a 12 digit number that if it equals 500 at the end of the number, I want another column to be updated with the first 9 digits of the number. To give specifics
Column A name is APComcode
Column B name is LUComcode

If Column A number ends with 500 I want Column B to be updated with the 9 digits to the right.


The formula I am using is : UPDATE TblComcode1 SET TblComcode1.LUComcode = Left([TblComcode1.APComcode],9)
WHERE (((Right([TblComcode1!LUComcode],3))="500&quot);

The error message I am getting is Undefined function'Left' in expression.

 

[Maquis]

I think you're using the Right function on the wrong field (no pun intended). According to your post, APComcode is the field that ends in 500, right?

Try this: : UPDATE TblComcode1 SET TblComcode1.LUComcode = Left([TblComcode1.APComcode],9)
WHERE (((Right([TblComcode1!APComcode],3))="500&quot);
Maq
<insert witty signature here>

 

[jebry]

Hi!

If LUComcode and APComcode are actually numbers then you can use the following:

UPDATE TblComcode1 SET TblComcode1.LUComcode = (TblComcode1.APComcode] - 500)/1000
WHERE [TblComcode1!LUComcode] Mod 500 = 0

I've not tried this but the logic makes sense.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Maquis]

Oooh, Jeff has a good point. If these fields are numeric types then you must use math functions on them. Left() and Right() functions are meant just for text fields. Maq
<insert witty signature here>

 

[KenReay]

Hi

While the other posts are all valid comments, I do not think you would get the error message &quot;undefined Function&quot;, if they were the problem, I think it more likely that you have a missing reference, to check, open any code module in design view, and choose tools\references then look for any that say 'Missing', my money is on the Office Library being missing Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tdonahue]

I tried your solution Marquis. I am sorry to report I got the same error message. To respond to the other posts, This is a text field.

 

[tdonahue]

Ken Reay,
You may have a point. In the design view I went to tools but I could not find a reference tab. Any sugesstions?

 

[KenReay]

Hi

It is not a tab, it is a menu option under tools, but it is only enabled if you are design view of a module Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TorF]

I think jebry solution is false, for the following reason:
&quot;WHERE [TblComcode1!LUComcode] Mod 500 = 0&quot; is true if LUComcode=1000
Modulo 500 is true for 500,1000,1500,2000, etc.

You can use:
UPDATE Table1 SET B = (A-500)/1000
WHERE A LIKE '*500';

 

[jebry]

Hi again!

A star for Ken for reading the whole post! Reading is such a good skill! My thanks to TorF for pointing out the flaw in my solution. The solution given ought to work.

Actually, after testing, the original solution works as well. Apparently Access does a conversion on the fly to text so it can use the Left and Right functions.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[tdonahue]

Ken Reay,
I found what you meant. There must be hundreds of boxes not checked is there a way to determine which one would help me?

 

[TorF]

Instead or left(xxx), use strings.left(xxx)

Same for right function and all strings functions.

 

[tdonahue]

I have tried T or F ' solution and I get an error Data Type mismatch in criteria expression

 

[Maquis]

Psalm, don't worry about Jebry's or TorF's solutions. They are valid solutions, but since you said you are using text fields they don't apply to you.

As Ken pointed out, you're problem is most likely a missing reference. When you go to the Tools, References menu, there should be a library name with MISSING in front of it. It would be right at the top of the list with your checked references. If so, that is the one you need to download (or copy from another PC).

If nothing says MISSING then you probably just didn't have the appropiate library checked to begin with. These libraries are checked in my database and the Left and Right functions work for me. (Access 97)
Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Maq
<insert witty signature here>

 

[tdonahue]

I went to Module, New Tools Reference and the

Visual Basic for applications is checked
Microsoft Access 9.0 Object LIbary is checked
Microsoft DAO 3.6 Object Libary is checked

My query still does not work

 

[tdonahue]

I rechecked my last post and One of the objects was not checked I saved the change closed and than reopened the database and everything is working fine. I was dreading having to manually edit 14500 records. Now that my query works I did the update in about 5 sec. Thank you so much for your patience and help today.