You are here:

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



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!

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

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Julie Misson


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.


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

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.

©2017 All rights reserved.