Re: Sorting on mixed text-numeric
- From: John W. Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 30 Jun 2007 17:56:12 -0600
On Sat, 30 Jun 2007 12:21:45 -0700, wizard_chef <wizard_chef@xxxxxxxxxxx>
wrote:
In an Access query I am trying to sort on a text field containing
strings like mx1, mx2, ... mx9, mx10, mx11, ...
What I get is a sort like: mx1, mx10, mx11, ... etc. It seems I need
to convert these to numeric but I have tried cInt and a number of
other VBscript conversion functions, and I always get a type-conflict
error.
How can I convert this field in the query so that it will sort
treating the numeric part of the string as numeric rather than
alphabetic?? i.e., mx1, mx2, mx3, ... mx9, mx10, mx11, ... and so on.
If the alphabetic part is always two characters, you can use a calculated
field
Sortkey: Val(Mid([textfield], 3))
to extract a Number datatype value which will sort correctly.
If the number can occur in variable places in the text string... you may want
to consider redesigning your table. Fields should be "atomic" with only one
value; it seems this field has a text portion and a number portion, which
ideally should be in two fields (which can be concatenated at will for data
display).
John W. Vinson [MVP]
.
- Prev by Date: Re: Report sort
- Next by Date: Re: Timer Event help
- Previous by thread: Re: Report sort
- Next by thread: Re: Timer Event help
- Index(es):
Relevant Pages
|
