You are here:

Using MS Access/Manipulation of a part no. in access query

Advertisement


Question
Hi,

I have created a field within a query to manipulate a part no. by removing the last two letters:

PN WO Size: Left([PMPartNo],Len([PMPartNo])-2).

The bit I can't do is to say if the Part No has an "R" in it remove 3 digits not 2.
Any advice would be appreciated!
Thanks
Jenny

Answer
Hi Jennifer,

Thanks for the question.

What you will need include is an IIF and Instr to achieve this.  IIF is like saying if it is this, do this, if not do that.  

Where as Instr - checks to see if some text occurs in a string and gives it a number for where it occurs, if the text does not occur that number is zero.

For your example it would look like this:

PN WO Size:IIf(InStr([PMPartNo],"R")=0,Left([PMPartNo],Len([PMPartNo])-2),Left([PMPartNo],Len([PMPartNo])-3))

Hope this is of some help.

If you would like to know more about IIF

http://www.simply-access.com/What_is_the_IIf_Function.html

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.