[Jovacle]

Hi,

I have a field (Tag_Num) in an Access database that contains a heap of values that look something like this;

12345-1234-ABC-40-CG1
12345-2868-ACC-40-CG1
12345-4897-ACC-40-CG1
12345-7874-BGC-40-CG1
12345-8756-BXC-40-CG1
12348-123-ABC-40-CG1
12348-257-HJG-40-CG1
12348-389-UIO-40-CG1
12348-487--40-CG1
12348-575-YUO-40-CG1

I want to be able to extract all the five sections separated by dashes (-) into separate columns. So far I have been trying to

do this using the Mid function which has only worked on the first few numbers since they are all only 5 digits long. For

example;

SELECT Mid([Tag_Num],1,5) AS Area1
FROM [table_name]
ORDER BY Mid([Tag_Num],1,5);

This will give me the following results;

Area1

12345
12345
12345
12345
12345
12348
12348
12348
12348
12348

If I try and do the same to extract the next few numbers after the fist dash I use this SQL code;

SELECT Mid([Tag_Num],7,3) AS Area2
FROM [table_name]
ORDER BY Mid([Tag_Num],7,3);

This will give me the following results;

Area2

1234
2868
4897
7874
8756
123-
257-
389-
487-
575-

The problem is obviously that it gets the dashes in. A similar thing occurs for the next few columns.

Any ideas how I can separate my Tag_Num column into 5 separate columns using the dash (-) as a divider?

:confused::eek::cool::confused:

[ScottGem]

First this is not a SQL expression, its using Access built in functions.

The thing you are missing is the InStr function. For example, the first part could be parsed out using:

=Left(fieldname,Instr(1,fieldname."-")-1)

If the last part is always three characters, I would use:

=Right(fieldname,3)

Otherwise use:

Right(fieldname,Len(fieldname)-InstrRev(fieldname,"-"))

For the 2nd through 4th parts, you have to use the Mid function. For example:

Mid(fieldname",Instr(1,fieldname,",Instr(1,fieldna me,")+1,Instr(Instr(1,fieldname,")+1,Instr(Instr(1 ,fieldname,")+1,fieldname,")+1,fieldname,")-Instr(1,fieldname,")-Instr(1,fieldname,")-1)

The idea is to use the Instr function to determine what the start and length should be for the MID function.

[Jovacle]

Man that's brilliant. The Mid example works spot on as is! I've just got to try & figure out how it works now so I can alter it for the next section after the second dash (-). Too good. Very happy.

[Jovacle]

The mid example works perfect for values after the first dash, but I still haven't figured out how to alter it to work for the next section after the second dash.

The best I've done is to use;

Mid(fieldname,Instr(8,fieldname,"-")+1,Instr(Instr(8,fieldname,"-")+1,fieldname,"-")-Instr(8,fieldname,"-")-1)

So really I'm just starting it a bit further in (8 is the only change) which is risky considering I could have a value like;

123-02-ACC-40-CG1

The above example would end up finding the value after the third dash (40) which is not what I'd be after.

Also, the above string fails if I have the following possible value;

12345-2868-ACC

It won't work if there's no third dash and just reports "#error."

If this above example had another dash e.g.

12345-2868-ACC-XX

then it would work.

[ScottGem]

Instead of the 8 you need to use another Instr that gets you past the first dash.