Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Teresa, Jul 28, 2006.

  1. Teresa

    Teresa
    Expand Collapse
    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

    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,
    --
    Teresa
     
  2. Loading...


  3. B

    B
    Expand Collapse
    Guest

    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.

    "Teresa" wrote:

    > 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,
    > --
    > Teresa
     
  4. Teresa

    Teresa
    Expand Collapse
    Guest

    RE: MS Access doesn't sort text fields properly when there are hyp

    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,
    --
    Teresa


    "B" wrote:

    > 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.
    >
    > "Teresa" wrote:
    >
    > > 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,
    > > --
    > > Teresa
     
  5. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    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


    "Teresa" wrote:

    > 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,
    > --
    > Teresa
     
  6. Teresa

    Teresa
    Expand Collapse
    Guest

    RE: MS Access doesn't sort text fields properly when there are hyp

    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


    "datAdrenaline" wrote:

    > 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
    >
    >
    > "Teresa" wrote:
    >
    > > 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,
    > > --
    > > Teresa
     
  7. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    RE: MS Access doesn't sort text fields properly when there are hyp

    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" wrote:

    > 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
    >
    >
    > "datAdrenaline" wrote:
    >
    > > 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
    > >
    > >
    > > "Teresa" wrote:
    > >
    > > > 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,
    > > > --
    > > > Teresa
     
  8. Teresa

    Teresa
    Expand Collapse
    Guest

    RE: MS Access doesn't sort text fields properly when there are hyp

    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" wrote:

    > 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" wrote:
    >
    > > 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
    > >
    > >
    > > "datAdrenaline" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > > "Teresa" wrote:
    > > >
    > > > > 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,
    > > > > --
    > > > > Teresa
     
  9. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    RE: MS Access doesn't sort text fields properly when there are hyp

    You're are welcome! Always glad to help out!
    --
    Regards,
    Brent Spaulding
    datAdrenaline


    "Teresa" wrote:

    > 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" wrote:
    >
    > > 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" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > > "datAdrenaline" wrote:
    > > >
    > > > > 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
    > > > >
    > > > >
    > > > > "Teresa" wrote:
    > > > >
    > > > > > 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,
    > > > > > --
    > > > > > Teresa
     
  10. Mark Lincoln

    Mark Lincoln
    Expand Collapse
    Guest

    Re: MS Access doesn't sort text fields properly when there are hyp

    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.

    Teresa wrote:
    > 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
    >
    >
    > "datAdrenaline" wrote:
    >
    > > 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
    > >
    > >
    > > "Teresa" wrote:
    > >
    > > > 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,
    > > > --
    > > > Teresa
     

Share This Page