Text field weird sorting?

[LeiDBug007]

Greetings experts!

I've got a text field called "Rule Number". And basically in this field, the rule# is entered in sequence...1A, 1B, 1C...10A, 10B, 10C...

So far, I have entered the records in order by rule number, however... it's sorting it weird. My records in my table & form keep starting @ 10A instead of 1A... I just checked it right now... and now it's starting @ 5C??? I format the sort to "ascending" and it starts off with 10A all the time. I get out & exit the dbase, and it's back to starting w/5C?

I definitely need it to go in order and start the records with 1A (as the 1st record).

Hope the above makes sense. HELP PLEASE!!!

~LeiDBug

 

[BobJacksonNCI]

The easiest way to get what you want is to add a numeric field to your table, enter your desired sort order in that field and use it for sorting.

E.G.,
Cur Sort
Fld Ord
1A 1
1B 2
etc


HTH,
Bob

 

[LeiDBug007]

Hi Bob, looks like that's the only way to go. I set it up like you suggested and it seems to do the job. It's an extra step to the process but... it works! That's all I care about.

Thanx so much for taking the time in responding to my problem. Wish me luck!

~LeiDBug007

 

[BobJacksonNCI]

You're welcome, LeiDBug007! And Good Luck!!

Hmmm, Hawaiian?

Sis and Brother-In-Law just relocated to the Big Island. I feel partly responsible because they came to visit when I lived on Oahu from 1989 - 1996.

Happy computing,
Bob

 

[LeiDBug007]

What made you ask if I was Hawaiian? And...nothing wrong with convincing family members to live in paradise! In any case... why did YOU leave? Gotta be a good reason to leave paradise...

 

[BobJacksonNCI]

Hi!

Mainlanders rarely use "lei"...

I was Systems and Programming Manager for City Bank, until they downsized and ultimately outsourced their data processing. I looked, but couldn't find employment at the same level.

Bob

 

[LeiDBug007]

Wow, good catch! You're the only one that's been able to catch my handle... and I've had that handle for everything for more than 5 years now! I guess you would have to have some kind of heavy exposure to the island to catch it. I'm actually partial... most of my mom's family is in the island all spread out from Oahu to Eva to wherever possible!

As far as my handle is concerned... of course the "LeiD" (for lady) is obvious... the bug part is simply 'cause it's a computer term that reflects who I am... and I love ladybugs! What a concept... it makes all the while sense! That's why... can't let go of the handle cuz it's to reflective of me.

 

[CosmoKramer]

Another way is to sort seperately on the numeric and alpha portions of the Rule Number. If the alpha part is always one character in length, try something like this as your order by clause:

ORDER BY Val([Rule Number]), Right([Rule Number],1);

Hoc nomen meum verum non est.

 

[BobJacksonNCI]

Good job, CosmoKramer!

LeiDBug007, his code allows the order you want without having to a add a field to your table.

Bob
Saving $ and frequent flyer miles for return visit to Paradise. Was married there in 2001. And have a number of friends I'd love to visit.