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

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

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

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

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

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

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

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
tblBatchTest.BatchNum 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
tblBatchTest;

Bob