Ascending Sort Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2000 and I have a field which contains sample data as
follows: LAHA 2, LAHA 55, HONO 33, HONO 77. With each of these I want to
find the next highest number. But when I sort I get the following, LAHA 1,
LAHA 12, LAHA 2, LAHA 21. Since this field combines text and numbers, is
there anyway to find the next highest number or basically the last number
used. Thanks in advance. Any help is appreciated. Paul
 
Paul,

Make a query based on your table. You can make a calculated field in
the query like this...
NumericalPart: Max(Val(Mid([YourField],InStr([YourField]," ")+1))
.... and another one like this...
PreText: Left([YourField],4)

Make it a Totals Query (select Totals from the View menu), and then in
the Totals row of the query design grid, put Group By for the PreText
column and Expression for the NumericalPart column. Does this do what
you need?
 
if your values have a standard format of x alpha characters followed by one
space and then x numeric characters, here's one solution:

CLng(Right(FieldName,Len(FieldName)-InStr(FieldName," ")))

you can use this as a calculated control in a query, for instance, or just
about anywhere you need to isolate only the numeric characters, as numbers
rather than text. suggest you keep checking this thread - hopefully somebody
else will post a less-clunky solution.

hth
 
Hey Steve,
Thanks for all the help unfortunately I only get zeroes in the second
column. The first column shows the 4 charater code fine but the second one
shows nothing but zeroes. Any further help is appreciated. Thanks again Paul

Steve Schapel said:
Paul,

Make a query based on your table. You can make a calculated field in
the query like this...
NumericalPart: Max(Val(Mid([YourField],InStr([YourField]," ")+1))
.... and another one like this...
PreText: Left([YourField],4)

Make it a Totals Query (select Totals from the View menu), and then in
the Totals row of the query design grid, put Group By for the PreText
column and Expression for the NumericalPart column. Does this do what
you need?

--
Steve Schapel, Microsoft Access MVP

I'm using Access 2000 and I have a field which contains sample data as
follows: LAHA 2, LAHA 55, HONO 33, HONO 77. With each of these I want to
find the next highest number. But when I sort I get the following, LAHA 1,
LAHA 12, LAHA 2, LAHA 21. Since this field combines text and numbers, is
there anyway to find the next highest number or basically the last number
used. Thanks in advance. Any help is appreciated. Paul
Click to expand...
Click to expand...
 
Hi Tina,
Thanks for all your help though I keep getting the error message "data
type mismatch in criteria expression" Any further help is appreciated.
Thanks, Paul
 
Create a query. In the first field put the following expression:
DataType:Left([NameOfYourField],4)
In the second field, put this expression:
MaxDataNumber:Val([NameOfYourField])
In the third field, put this expression:
NextHighestDataNumber:[MaxDataNumber] + 1
Next, click on the Sigma(looks like E) button on the toolbar at the top of
the screen. Then under MaxDataNumber, change Group By to Max.

Run the query. NextHighestDataNumber will be the values you are looking for.
 
Paul,

On reviewing my earlier answer, I see that in my haste I left of a )
from the end of the expression. Should be like this...
NumericalPart: Max(Val(Mid([YourField],InStr([YourField]," ")+1)))

Does your data actually have spaces in it between the alpha and numeric
portions, as shown in your original example?

Is the Alpha portion always 4 characters, followed by a space, followed
by a number? If so, this could be simplified to...
NumericalPart: Max(Val(Mid([YourField],6)))
 
THANKS works great!

Steve Schapel said:
Paul,

On reviewing my earlier answer, I see that in my haste I left of a )
from the end of the expression. Should be like this...
NumericalPart: Max(Val(Mid([YourField],InStr([YourField]," ")+1)))

Does your data actually have spaces in it between the alpha and numeric
portions, as shown in your original example?

Is the Alpha portion always 4 characters, followed by a space, followed
by a number? If so, this could be simplified to...
NumericalPart: Max(Val(Mid([YourField],6)))

--
Steve Schapel, Microsoft Access MVP
Hey Steve,
Thanks for all the help unfortunately I only get zeroes in the second
column. The first column shows the 4 charater code fine but the second one
shows nothing but zeroes. Any further help is appreciated. Thanks again Paul

:
Click to expand...
Click to expand...
 
Thanks a big help

PC Datasheet said:
Create a query. In the first field put the following expression:
DataType:Left([NameOfYourField],4)
In the second field, put this expression:
MaxDataNumber:Val([NameOfYourField])
In the third field, put this expression:
NextHighestDataNumber:[MaxDataNumber] + 1
Next, click on the Sigma(looks like E) button on the toolbar at the top of
the screen. Then under MaxDataNumber, change Group By to Max.

Run the query. NextHighestDataNumber will be the values you are looking for.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

pas926 said:
I'm using Access 2000 and I have a field which contains sample data as
follows: LAHA 2, LAHA 55, HONO 33, HONO 77. With each of these I want to
find the next highest number. But when I sort I get the following, LAHA 1,
LAHA 12, LAHA 2, LAHA 21. Since this field combines text and numbers, is
there anyway to find the next highest number or basically the last number
used. Thanks in advance. Any help is appreciated. Paul
Click to expand...
Click to expand...
 
well, the expression worked when i tested it before posting, so i'm stumped.
but i see that Steve gave you a solution that's not only good, but actually
works the first time - whew, i'm off the hook! :)
 
Back
Top