Ascending Sort Problem Again

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

Guest

I previously posted this problem:

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

I received several solutions all of which have worked BUT recently stopped
working and are giving me the error message "Data type mismatch in criteria
expression" I haven't change anything with the table or field. I tried it on
a new table and it works fine. HELP

One type of code I was given to use is as follows:

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)

Any help is greatly appreciated.
Thanks, Paul
 
Create a query based on your table. Put the following expression in the
first field of the query:
FirstFourChars:Left([NameOfField],4)
Put this expression in the second field:
NextNumber:Val([NameOfField])+1

Click on the Sigma (looks loke E) button in the toolbar at the top of the
screen. Under NextNumber, change Group By to Max.

When you run your query you will get:
HONO 78
LAHA 56

If you want to use 78 for HONO, put this in your code:
DLookup("[NameOfField]","NameOfAboveQuery","[NameOfField] = 'HONO'")
 
Thanks for the help, was going well until I hit the Sigma button. When I went
to run the query I got the "data type mismatch in criteria expression" error
message. I appreciate your help.
Paul

PC Datasheet said:
Create a query based on your table. Put the following expression in the
first field of the query:
FirstFourChars:Left([NameOfField],4)
Put this expression in the second field:
NextNumber:Val([NameOfField])+1

Click on the Sigma (looks loke E) button in the toolbar at the top of the
screen. Under NextNumber, change Group By to Max.

When you run your query you will get:
HONO 78
LAHA 56

If you want to use 78 for HONO, put this in your code:
DLookup("[NameOfField]","NameOfAboveQuery","[NameOfField] = 'HONO'")

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


pas926 said:
I previously posted this problem:

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

I received several solutions all of which have worked BUT recently stopped
working and are giving me the error message "Data type mismatch in criteria
expression" I haven't change anything with the table or field. I tried it on
a new table and it works fine. HELP

One type of code I was given to use is as follows:

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)

Any help is greatly appreciated.
Thanks, Paul
Click to expand...
Click to expand...
 
I received several solutions all of which have worked BUT recently stopped
working and are giving me the error message "Data type mismatch in criteria
expression" I haven't change anything with the table or field. I tried it on
a new table and it works fine. HELP
Click to expand...

Check the Criteria line in your query grid. This message means that
you're using a text value as a criterion for a numeric field, or some
other data type mismatch. If you don't see the problem right away,
feel free to open the Query in SQL view and post the SQL text to a
message here.

I doubt that it's the sort clause that's at fault; it's probably
something else in the query.

John W. Vinson[MVP]
 
Run the query before pressing the Sigma button and see if you get a number
that is one more than each LAHA and HONO record under next number.

Steve
PC Datasheet


pas926 said:
Thanks for the help, was going well until I hit the Sigma button. When I went
to run the query I got the "data type mismatch in criteria expression" error
message. I appreciate your help.
Paul

PC Datasheet said:
Create a query based on your table. Put the following expression in the
first field of the query:
FirstFourChars:Left([NameOfField],4)
Put this expression in the second field:
NextNumber:Val([NameOfField])+1

Click on the Sigma (looks loke E) button in the toolbar at the top of the
screen. Under NextNumber, change Group By to Max.

When you run your query you will get:
HONO 78
LAHA 56

If you want to use 78 for HONO, put this in your code:
DLookup("[NameOfField]","NameOfAboveQuery","[NameOfField] = 'HONO'")

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


pas926 said:
I previously posted this problem:

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,
Click to expand...
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

I received several solutions all of which have worked BUT recently stopped
working and are giving me the error message "Data type mismatch in criteria
expression" I haven't change anything with the table or field. I
Click to expand...
tried it
on
a new table and it works fine. HELP

One type of code I was given to use is as follows:

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)

Any help is greatly appreciated.
Thanks, Paul
Click to expand...
Click to expand...
Click to expand...
 
Thanks Steve, I found the problem, someone had entered a blank record and
after I removed that the query worked fine. Thanks again for all your help!!
Paul

PC Datasheet said:
Run the query before pressing the Sigma button and see if you get a number
that is one more than each LAHA and HONO record under next number.

Steve
PC Datasheet


pas926 said:
Thanks for the help, was going well until I hit the Sigma button. When I went
to run the query I got the "data type mismatch in criteria expression" error
message. I appreciate your help.
Paul

PC Datasheet said:
Create a query based on your table. Put the following expression in the
first field of the query:
FirstFourChars:Left([NameOfField],4)
Put this expression in the second field:
NextNumber:Val([NameOfField])+1

Click on the Sigma (looks loke E) button in the toolbar at the top of the
screen. Under NextNumber, change Group By to Max.

When you run your query you will get:
HONO 78
LAHA 56

If you want to use 78 for HONO, put this in your code:
DLookup("[NameOfField]","NameOfAboveQuery","[NameOfField] = 'HONO'")

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


I previously posted this problem:

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

I received several solutions all of which have worked BUT recently stopped
working and are giving me the error message "Data type mismatch in
criteria
expression" I haven't change anything with the table or field. I tried it
on
a new table and it works fine. HELP

One type of code I was given to use is as follows:

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)

Any help is greatly appreciated.
Thanks, Paul
Click to expand...
Click to expand...
Click to expand...
 
Hi Jon,
Thanks for the response. A user had entered a blank record and that was
causing that error message. Thanks for you help. Paul
 
Back
Top