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:

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.

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.

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.

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