Separating Out Fields 1

[gingerboy]

I have a field which contains a reference code which can be of different lengths:

1.2
1.2.3
1.2.3.4
2.1
2.1.3
2.3
5.3
4.2.3.1

(these are nested categories, i.e. 1.2.3 is part of 1.2)

I currently have a query which is grouping by this field, but would like to group by the first two (i.e. x.x) and three (x.x.x)

The values lengths are from x.x to x.x.x.x

Can i use the trim function to reduce x.x.x.x to x.x
Thanks for any help

Gingerboy

 

[captainquigers]

The function you would use is


Left (x.x.x.x,3)


This would give you the first 3 characters of the sequence i.e. "x.x"

If you group on this it will give you the answer you are after.

 

[gingerboy]

Cheers, Solved it

Thanks Very Much

 

[gingerboy]

Just realised a problem...

forgot that sometimes x might be 2 characters:

1.2.3
10.1.2
1.12.1

The expression above gives me

1.2
10.
1.1

Is there any way i can use the "."?

Ta

 

[OhioSteve]

The easiest solution would be to standardize the format of the field. However, if that is not possible, you could do a preliminary query to pull out the first and second groupings.

Consider a table called table3, with a text field called F1. Its contents are~

F1
999.99.9
88.8888.8
1.111.11

This query separates out the first two groupings:

SELECT Table3.F1,
InStr([F1],".") AS a,
InStr([a]+1,[F1],".") AS b,
Mid([F1],1,[a]-1) AS [First Grouping],
Mid([F1],[a]+1,-[a]-1) AS [Second Grouping]
FROM Table3;

This is the output~

999.99.9 4 7 999 99
88.8888.8 3 8 88 8888
1.111.11 2 6 1 111

The last two fields contain the first and second groupings. You can use this query as a basis for other queries. You can learn more about InStr() and Mid() at

http://www.filex.com/htmlhelp/traxhelpMS_Access_Functions.html

 

[flyover789]

here is a way, that also uses instr() but you don't need subqueries. I've just developed it down to the fourth level (means forth appearance of a "." in your field.

Level1: Left([YourField];InStr([YourField];"."))

Level2: Left([YourField];InStr(InStr([YourField];".")+1;[YourField];"."))

Level3: IIf(Len([Level2])>0;Left([YourField];InStr(Len([Level2])+1;[YourField];"."));"")

Level4: IIf(Len([Level3])>0;Left([YourField];InStr(Len([Level3])+1;[YourField];"."));"")

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[PHV]

Another way if you have access 2k or higher:
- In a code module window create a function like this
Public Function mySplit(str, delim, n)
myArr = Split(str, delim)
myStr = myArr(0)
For i = 1 To n - 1
If i > UBound(myArr) Then Exit For
myStr = myStr & delim & myArr(i)
Next
mySplit = myStr
End Function
- Amend your query like this:
GROUP BY mySplit(refCode, ".", 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244