Sorting on numeric value of alphanumeric field

Hi everyone,

How about this one… In my G/L Account table I have a field called "No. 2". This field contains a value to group my G/L Accounts by. The value is 1 or 2 digits. The field is setup as a Code field - alphanumeric - but the value is always a number.

When I create an NL Unique function to group by this field it looks like this:
=NL("Rows","G/L Account","No. 2")But the sorting I get shows 1 10 11 12 2 21 22 (in Rows of course).

Too bad, 'cause I wanted 1 2 … 10 11 12 21 22. So, I thought I could use the Excel VALUE command in my NL :idea: :
=NL("Rows","G/L Account","=VALUE(NF(,""No. 2"")")
But now I get '0' as end result :(

Pressing my Easy button didn't help, but fortunately there's the Jet Community! Anybody an idea why this doesn't work?


Please sign in to leave a comment.