a query with wildcard

Here are some of the values in my table:
AW-3002-12, DM-2002-12, MS-4221-01, MS-1003-32, etc.

I want to have a list that will sort this items regardless the three
first charachters. So, the new list will look like:
MS-1003-32, DM-200-12, AW-3002-12, MS-4221-01, etc.

Thanks,

Jeff

re: a query with wildcard

You need to create a calculated field. You can uncheck the Show checkbox so
that it doesn't appear in the queries output, if you wish. You would then
sort this field.

Example:
SortMe: Right([FieldName], 7)
Set the sort to Ascending or Descending as desired

You may run into a problem since these are text values with the numbers not
sorting in numerical order. If the numbers are all 4 digits - 2 digits, you
may be ok. If not, then you will need to repeat the above process, breaking
it into each set of numbers, using CInt to change them to numbers, and
sorting on each one individually. Also, if the length after the first 3
characters varies from 7, then you'll need to change the 7 to be the full
length - 3 (i.e. Right([FieldName], Len([FieldName])-3).

--
Wayne Morgan
MS Access MVP


"Joe" <galsaba@aol.com> wrote in message
news:b4123330.0405070645.938f179@posting.google.co m...[color=blue]
> Here are some of the values in my table:
> AW-3002-12, DM-2002-12, MS-4221-01, MS-1003-32, etc.
>
> I want to have a list that will sort this items regardless the three
> first charachters. So, the new list will look like:
> MS-1003-32, DM-200-12, AW-3002-12, MS-4221-01, etc.[/color]

re: a query with wildcard

Thanks, where should I put this line? VBA?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: a query with wildcard

No, it would be a calculated field in the query. Place the line in the query
grid where you would normally place the name of a field. The name of the
calculated field would be the part before the ":" and the calculation is the
part after the ":".

--
Wayne Morgan
MS Access MVP


"Aaron Gal" <galsaba@aol.com> wrote in message
news:409ba866$0$203$75868355@news.frii.net...[color=blue]
>
>
> Thanks, where should I put this line? VBA?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

re: a query with wildcard

Thanks!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: a query with wildcard

Actually, a better way to get character #4 and those following is with
Mid([FieldName],4) which will return a string starting in the fourth
position and continuing to the end regardless of original string's length.

S.A.M.


"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:KqNmc.6436$q%3.198@newssvr24.news.prodigy.com ...[color=blue]
> You need to create a calculated field. You can uncheck the Show checkbox[/color]
so[color=blue]
> that it doesn't appear in the queries output, if you wish. You would then
> sort this field.
>
> Example:
> SortMe: Right([FieldName], 7)
> Set the sort to Ascending or Descending as desired
>
> You may run into a problem since these are text values with the numbers[/color]
not[color=blue]
> sorting in numerical order. If the numbers are all 4 digits - 2 digits,[/color]
you[color=blue]
> may be ok. If not, then you will need to repeat the above process,[/color]
breaking[color=blue]
> it into each set of numbers, using CInt to change them to numbers, and
> sorting on each one individually. Also, if the length after the first 3
> characters varies from 7, then you'll need to change the 7 to be the full
> length - 3 (i.e. Right([FieldName], Len([FieldName])-3).
>
> --
> Wayne Morgan
> MS Access MVP
>
>
> "Joe" <galsaba@aol.com> wrote in message
> news:b4123330.0405070645.938f179@posting.google.co m...[color=green]
> > Here are some of the values in my table:
> > AW-3002-12, DM-2002-12, MS-4221-01, MS-1003-32, etc.
> >
> > I want to have a list that will sort this items regardless the three
> > first charachters. So, the new list will look like:
> > MS-1003-32, DM-200-12, AW-3002-12, MS-4221-01, etc.[/color]
>
>[/color]