Removing Left and Right characters and getting only the middle characters

[JohnLee]

Good day,

I'm hoping may be able to assist me here. I am appending data from one table to another, and I need to split one of the fields into 3 parts.

The first part is the scanned Batch Number the middle part is the Manufacturer and the end part is the split Batch Number as follows:

00000129_HOTPOINT_003
00000130_SONY_001
00000131_FLYMO_002
00000132_BOSCH_001
00000133_TISUNRECOGNISED_002

etc etc

I have used the following to seperate the left and right characters as required

Left([strName],8) which gives me 00000129
Right([strName],3) which gives me 003

which is what I want for those parts, the real tricky part which I can't seem to work out is to get the middle part without the underscores

I need to just get the Manufacturer name i.e. BOSCH, FLYMO, TISUNRECOGNIZED etc

If anyone could help with how I can acheive that in a query I would be most grateful. I would prefer to do it in the query grid rather writing SQL as I am not familiar with SQL code writing.

Thanks in advance


John

 

[namliam]

Look into the Mid, Len, Instr and InstrRev functions...

i.e.
Mid("00000129_HOTPOINT_003",10)
HOTPOINT_003

I think you can take it from here, if not, let me know what problems you encounter

 

[JohnLee]

Hi,

Thanks for your reponse, however I've tried looking up those Mid, Len, Instr and InstrRev functions, and I'm having difficulty in understanding how to use them.

when I ask for help with the help it brings up a worksheet help for Instr and doesn't know what is meant by InstrRev!

because the characters between the underscores are not always the same length, I don't understand your example "Mid("00000129_HOTPOINT_003",10)
HOTPOINT_003"

How do I construct the required expresssion?

Your assistance would be appreciated.

John

 

[namliam]

Open any database,
Hit CTRL + G to get the immediate window
Type Mid and hit F1

Same works with the other functions.

Assuming your numbers on the beginning are always 8 numbers (because you use Left(...,8) )
Now the next character is your _
Mid("00000129_HOTPOINT_003",10) will then take all characters starting from character # 10.

Now your "end" that you dont want will be 4 characters, your 3 numbers + your _

The mid function has a third input parameter... the total works like:
Mid("YourText",StartFrom, #ofCharacters)

Now use the Len function to determine the total number of characters in your Text.
Len("YourText") will in this case return 8.
For Len("00000129_HOTPOINT_003") it returns 21.

Now from character 10 we want all the characters up to the last 4. In other words we are skipping the first 9 and stopping at the last 4.

In this case your solution will be to:
mid("00000129_HOTPOINT_003",10 ,Len("00000129_HOTPOINT_003")-9-4)
to return HOTPOINT

Dispite me giving you this I strongly urge you to look up the mentioned functions in the help and read up on them.

Good Luck

 

[JohnLee]

Hi,

Thanks for your response, I was looking up those bits of info, and trying a couple of things, without too much success, when your email came through.

your solution has done the trick and I will continue to read up on the recommended information.

John

 

[raskew]

Hi -

Here's a little different approach that is not dependent on specific lengths:

p = "00000129_HOTPOINT_003"
w =left(p, instr(p, "_") -1)
x = mid(p, instr(p, "_") + 1)
y = left(x, instr(x, "_") -1)
z = mid(x, instr(x, "_") + 1)

? w
00000129
? y
HOTPOINT
? z
003

HTH - Bob

 

[JohnLee]

Hi Bob,

Thanks for your response, however I have used NAMLIAM solution in my query, which works great. Looking at your solution I'm not sure it would work in a query, but then who I am to challenge the experts. But I will keep your solution just in case I need to use it in a VB environment.

Thanks once again

John

 

[raskew]

Looking at your solution I'm not sure it would work in a query

Sure it will. Try this, replacing your table and field names, as required.

SELECT
    [COLOR="Blue"]tblBatchTest[/COLOR].[COLOR="blue"]BatchNum[/COLOR] AS P
  , Left([p],InStr([p],"_")-1) AS w
  , Mid([p],InStr([p],"_")+1) AS x
  , Left([x],InStr([x],"_")-1) AS y
  , Mid([x],InStr([x],"_")+1) AS z
FROM
   [COLOR="blue"]tblBatchTest[/COLOR];

Bob