a bit of sql trouble...

[Psycho_NZ]

hiya, i have a table in an access database, i need an sql statement that will work in my VB to extract unique codes from it minus the last 2 chars

so here is the database

code...............| blah | blah |
------------------------------------
0654ab
0654cd
1234zz
1234yy
1234zz
023342ab
123ac
123dd

or sumthin like . and this is what i want it to select me

0654,1234,023342,123

any ideas? i've tried this:
SELECT UNIQUE Left(Code,(Len(Code - 2)) FROM Resin
but no luck.

Thanks for your time
Ang

[Musician]

Why not just select the code into a recordset it and then in your vb code use the string manipulation code - i.e. codeNum = Left(rec("Code"),(Len(rec("Code") - 2))

[Psycho_NZ]

well, cause i'm lazy. i was hoping to do it all in one step, if i do it that way, it's going to give me all the codes, then i'll have to fish out the unique ones. Also then i'll have to put them into the combo box separetely as well, as at the moment im just linking that to the ado that uses the select statement in question

does anyone know an easy way to fish out uniques from a recordset??

thanks again
Ang

[ralph]

You can embed Musician's function into sql:
Select Distinct Left(Code, Len(Code) - 2) As CodeNum From TableNAme

[Psycho_NZ]

oh okeys, i forgot about as's

nice, cheers mateys
Ang