extract text between first and second character

[masoud_sedighy]

i would like to make a query in ma access can extract text between first and second character "/" and when there are not "/" in field it returns null.
now data in my table are like below

No option1​

1 100​

2 145/Mechanical/0800​

3 120/electrical/1620​

4 131/mechanical/0200/dw-001​

Now I like to make a query can extract text between first and second character "/" like below:​

No option1 discipline​

1 100 null​

2 145/Mechanical/0800 Mechanical​

3 120/electrical/1620 electrical​

[FONT=&quot]4 131/mechanical/0200/dw-001 mechanical [/FONT]

[FONT=&quot][/FONT]

 

[plog]

First, I would be remiss if I didn't tell you that you shouldn't store your data like that. Discrete pieces of data, need to be in their own field. That means you should have at least 3 fields for the one you currently have now. Break up slash delimited piece of information out into its own field.

To get there you will need to use the Mid (http://www.techonthenet.com/access/functions/string/mid.php) and Instr(http://www.techonthenet.com/access/functions/string/instr.php) functions. My suggestion would be to create a function inside a module to do this, you would pass that function your field value and it would return what you wanted the end field to look like.