Sorting in non-alphabetical order (1 Viewer)

[Egyptologist]

I am trying to build a database of ancient Egyptians and their titles.

I need to sort names and titles in Egyptian alphabetical order, not in our alphabetical order.

The order I need is this:

3 j e w b p f m n r h v c u z s ; q k g t o d x (yes, that is a semi-colon)

Is this possible?

I have asked my Access savvy friends, who don't think it can be done. I have checked on Google (sort in non-alphabetical order) and got nothing useful. I have looked in all my Access books and can't find anything.

Do I need a macro, perhaps? Or can someone point me in the right direction of a resource?

Thanks for any help you can provide.

 

[namliam]

I think its possible....

You could try something like so:

Make a hidden column and sort on that.
This column you would have a function or use Replace to substitute the characters in order of alphabet.

So you would do something like
Replace(Replace([MyField],"3", "a"), "j","b")

Thusly translating your "egyptian" into "english" for getting the ordering right.

Eventhough I am not a real frequent visitor anymore to this forum... I hope this helps...

 

[Egyptologist]

Thanks for the tip. I'll go away and research functions (complete newbie here).

 

[namliam]

The function would be something like:

Function EgyptSort(InputString As String) As String
    EgyptSort = Replace(InputString, "3", "a")
    EgyptSort = Replace(EgyptSort, "j", "b")
    EgyptSort = Replace(EgyptSort, "e", "c")
' .... Etc

End Function

A string "3je" would then be returned as "abc"

Thusly if you use this as a hidden field and order on it, you get your desired order.

Use it like so in your query: EgyptSort([YourEgyptionNameField])

Regards

The Mailman

 

[Egyptologist]

Ah! That's very helpful! Thanks very much for the idea (I can do queries - still have to figure out functions ...).

Thanks again.