sort 10A before 100,101, etc.. 1

[LikeThisName]

had a field called tab that was a number and converted to text in order to go back and add letters to numbers.

for sorting solution i propose creating field in a query that strips last character of tab if not a number so 10A would become 10 so it definitely would come before 100,101 etc. 2nd sort would be actual tab field so 10B would comeafter 10,10A.

i was just curious if there was a better solution.

LikeThisName <- ? Sorry It's Taken =)

 

[LikeThisName]

sortMe: IIf(Right([TAB],1)<>"9",Left([TAB],Len([TAB])-1),[TAB])

is there a better way then to take above and nest it to check 0-8 as well.

i tried using int(right([TAB],1)<=9....
thinking that would take care of 0-9. but int isn't working. what am i doing wrong?

TIA,



LikeThisName <- ? Sorry It's Taken =)

 

[dhookom]

You should be able to use:
SortMe: Val([YourField])

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LikeThisName]

the reasons for spaces in above code is because my field was called TAB and I had it in BRACKETS, which i guess at this site inserts actuall Tabspaces

LikeThisName <- ? Sorry It's Taken =)

 

[LikeThisName]

sortMe: IIf(Val(Right(TAB,1))<=9,Left(TAB,Len(TAB)-1),TAB)

i try using Val() and i get type data mismatch



LikeThisName <- ? Sorry It's Taken =)

 

[LikeThisName]

THANKS dhookum

val() worked:
sortMe: IIf(Len([TAB])>0,Val([TAB]),[TAB])

Len([TAB])>0 check for nulls others wise can't sort.

Thanks a lot!

LikeThisName <- ? Sorry It's Taken =)

 

[dhookom]

Sorry, I missed the letter part for the sorting. I would use
SortMe: Format( Val([TAB]),"0000000") & Right([Tab],1)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LikeThisName]

final solution

Int(IIf(Len(TAB)>0,Val(TAB),TAB))

kept 10A and 11 from coming after 109

thanks again duane!

LikeThisName <- ? Sorry It's Taken =)