MS Access doesn't sort text fields properly when there are hyphens

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

Guest

Hi
Could someone please help me?

I have a table in an Access database with only one text field. The MS
Access is sorting the table's data as the list below:
text field
H-
H1
H2
HÃ
HB
HC
HD
HP-
HPFA
HPFA-L.X
HP-Y-L.X
ZA
Z-P
ZZZZ

However, the value HP-Y-L.X should be placed before the value HPFA due to
the hyphen's value in the ASCII table.

Does someone know what is happening?

Thanks a lot for your help,
 
If this data is allways the same, I would create a seprate table for these
vaues and make this field a look up field. Then use the primary key field to
sort? You put the data in the table in the order you want. Maybe that will
work.
 
Hi B,

thanks for your answer.

Unfortunately the data is not always the same. I already had thought on the
solution described by you but I would have to make many changes in my
application. So I was wondering if this issue wasn't a MS Access bug because
the data that I retrieve comes from the Oracle database and there the data is
sorted properly.

Thanks,
 
Here is a clip from a knowledge base link:
Microsoft Jet version 4.0 uses a new sorting order that treats hyphens (-)
and single quotation marks (') as special characters. For example, Co-op and
Coop are now considered equal when sorting. Also O'Connor is treated the same
as OConnor.
<<

Here is a link to the entire article:
http://support.microsoft.com/?id=236952
 
Thank you very much datAdrenaline!

It seems that I'll have to change my application using some workaround for
this issue. :(

But your information was very helpful.

Thanks again!
 
You're welcome Teresa!

You may want to use the Replace() function to create column in a query...
Something like:
Replace([YourFieldName],"-","~") ... this will substitute a "~" for all the
"-"

Then just sort on that newly created column. You may even wish to set the
columns visibility to NON-visible (unchecked)
 
I don't have words to thank you!!!!

You solved all of the problem that I had with the hint about the Replace
function. I replaced the "-" by "|" and the MS Access sorted my text list as
I wanted.

Thank you very much!!!
--
Teresa


datAdrenaline said:
You're welcome Teresa!

You may want to use the Replace() function to create column in a query...
Something like:
Replace([YourFieldName],"-","~") ... this will substitute a "~" for all the
"-"

Then just sort on that newly created column. You may even wish to set the
columns visibility to NON-visible (unchecked)

--
Regards,
Brent Spaulding
datAdrenaline


Teresa said:
Thank you very much datAdrenaline!

It seems that I'll have to change my application using some workaround for
this issue. :(

But your information was very helpful.

Thanks again!
Click to expand...
Click to expand...
 
You're are welcome! Always glad to help out!
--
Regards,
Brent Spaulding
datAdrenaline


Teresa said:
I don't have words to thank you!!!!

You solved all of the problem that I had with the hint about the Replace
function. I replaced the "-" by "|" and the MS Access sorted my text list as
I wanted.

Thank you very much!!!
--
Teresa


datAdrenaline said:
You're welcome Teresa!

You may want to use the Replace() function to create column in a query...
Something like:
Replace([YourFieldName],"-","~") ... this will substitute a "~" for all the
"-"

Then just sort on that newly created column. You may even wish to set the
columns visibility to NON-visible (unchecked)

--
Regards,
Brent Spaulding
datAdrenaline


Teresa said:
Thank you very much datAdrenaline!

It seems that I'll have to change my application using some workaround for
this issue. :(

But your information was very helpful.

Thanks again!
--
Teresa


:

Here is a clip from a knowledge base link:


Microsoft Jet version 4.0 uses a new sorting order that treats hyphens (-)
and single quotation marks (') as special characters. For example, Co-op and
Coop are now considered equal when sorting. Also O'Connor is treated the same
as OConnor.
<<

Here is a link to the entire article:
http://support.microsoft.com/?id=236952

--
Regards,
Brent Spaulding
datAdrenaline


:

Hi
Could someone please help me?

I have a table in an Access database with only one text field. The MS
Access is sorting the table's data as the list below:
text field
H-
H1
H2
HÃ
HB
HC
HD
HP-
HPFA
HPFA-L.X
HP-Y-L.X
ZA
Z-P
ZZZZ

However, the value HP-Y-L.X should be placed before the value HPFA due to
the hyphen's value in the ASCII table.

Does someone know what is happening?

Thanks a lot for your help,
Click to expand...
Click to expand...
Click to expand...
 
For what it's worth: I've seen printouts from several sources
(insurance companies and other vendors) that follow the same sorting
rules. It seems to be something of a standard.
 
Back
Top