Access Sort of hyphen in mixed character strings -Alpha, Numeric, Special Characters

[Kc-Mass]

In a standard Ascii table a dash or hyphen is decimal 45. A period or dot is
decimal 46.
If I sort a table or recordset of mixed character string ascending in
Access, those strings beginning with a dot or period are first; those
beginning with a dash or hyphen are sorted as though the hyphen is not there
(that is the next non-hyphen character controls the sort order). Those that
are alpha or numeric are sorted in their normal order without regard to the
presence of a hyphen.

I have found nothing documenting this in Access but Excel's help file (check
sort order) specifically says that Excel will simply ignore the hyphens.

1. Is this the case for Access?

2. Is there a way of making Access sorts conform to ascii (I know its
Microsoft but really!)?

Thx

Kevin

[fredg]

On Sun, 21 Jan 2007 13:41:16 -0500, Kc-Mass wrote:

In a standard Ascii table a dash or hyphen is decimal 45. A period or dot is
decimal 46.
If I sort a table or recordset of mixed character string ascending in
Access, those strings beginning with a dot or period are first; those
beginning with a dash or hyphen are sorted as though the hyphen is not there
(that is the next non-hyphen character controls the sort order). Those that
are alpha or numeric are sorted in their normal order without regard to the
presence of a hyphen.

I have found nothing documenting this in Access but Excel's help file (check
sort order) specifically says that Excel will simply ignore the hyphens.

1. Is this the case for Access?

2. Is there a way of making Access sorts conform to ascii (I know its
Microsoft but really!)?

Thx

Kevin

In a query, add a new column:
SortThis:Asc([Fieldname])

Sort on this column Asc or Desc.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Kc-Mass]

Thanks very much Fred - great solution. If I want to, in a query, to select
from a table of possible values can I ask for >= asc(FieldName) and
<=asc(fieldName) and get back all the legitimateasci values between first
and last fieldName?

Thx Much

"fredg" <fg******@example.invalidwrote in message
news:14***************************@40tude.net...

On Sun, 21 Jan 2007 13:41:16 -0500, Kc-Mass wrote:

>In a standard Ascii table a dash or hyphen is decimal 45. A period or dot
is
decimal 46.
If I sort a table or recordset of mixed character string ascending in
Access, those strings beginning with a dot or period are first; those
beginning with a dash or hyphen are sorted as though the hyphen is not
there
(that is the next non-hyphen character controls the sort order). Those
that
are alpha or numeric are sorted in their normal order without regard to
the
presence of a hyphen.

I have found nothing documenting this in Access but Excel's help file
(check
sort order) specifically says that Excel will simply ignore the hyphens.

1. Is this the case for Access?

2. Is there a way of making Access sorts conform to ascii (I know its
Microsoft but really!)?

Thx

Kevin

In a query, add a new column:
SortThis:Asc([Fieldname])

Sort on this column Asc or Desc.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[fredg]

On Sun, 21 Jan 2007 15:25:45 -0500, Kc-Mass wrote:

Thanks very much Fred - great solution. If I want to, in a query, to select
from a table of possible values can I ask for >= asc(FieldName) and
<=asc(fieldName) and get back all the legitimateasci values between first
and last fieldName?

Thx Much

"fredg" <fg******@example.invalidwrote in message
news:14***************************@40tude.net...

>On Sun, 21 Jan 2007 13:41:16 -0500, Kc-Mass wrote:

*** Snipped ***

I'm not at all sure of what you're asking.
As I understand your question, you would first create a new query
column:

NewColumn:Asc([FieldName])

The above column returns the ascii value of just the first character
in the field.

If you know the ascii values, to filter the field, you could use, for
example....
Between 45 and 65

Or, if you don't know the ascii values, you could use....
Between asc("-") and asc("A")

Either will return records that start with "-" through "A".

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Kc-Mass]

Let me try to be clearer.

I have a table of authority ranges. The authority ranges are coded, as
examples, as AA12 to XCDR, F.78 to F234, F-23 to F-81, etc. Those
authority ranges are tied to Roles. If you have one or more roles in the
database you are given one or more authority ranges. I have a table with
the roles and the attendent ranges of authorities, stated as upper and lower
bounds. I also have a lookup table of all possible individual authorities
AA12, AA13, etc. What I need to do is create a new table with roles and
individual authorities. I have a module that does all of that through a
series of queries.

The queries basically select from the table of all authorities, those
authorities that are >= the low bound of the range and <= the high bound of
the range and insert them in the new table with the attendent role ID.

The problem is that MS Access sees the third range in the second sentence
above, "F-23 to F-81" as being included in the second range, "F.78 to F234".
That is true because Access reads those in any ordered comparison (=,>,<) as
being "F23 to F81" and pretends the hyphen isn't there. It is only the
hyphenated authorities that are creating problems. If Access viewed these
in conformance with ascii table values it would see the elements of the
range "F-23 to F-81" as being all less than and therefore outside the range
"F.78 to F234".

Any help appreciated.

"fredg" <fg******@example.invalidwrote in message
news:vi****************************@40tude.net...

On Sun, 21 Jan 2007 15:25:45 -0500, Kc-Mass wrote:

>Thanks very much Fred - great solution. If I want to, in a query, to
select
from a table of possible values can I ask for >= asc(FieldName) and
<=asc(fieldName) and get back all the legitimateasci values between first
and last fieldName?

Thx Much

"fredg" <fg******@example.invalidwrote in message
news:14***************************@40tude.net.. .

>>On Sun, 21 Jan 2007 13:41:16 -0500, Kc-Mass wrote:

*** Snipped ***

I'm not at all sure of what you're asking.
As I understand your question, you would first create a new query
column:

NewColumn:Asc([FieldName])

The above column returns the ascii value of just the first character
in the field.

If you know the ascii values, to filter the field, you could use, for
example....
Between 45 and 65

Or, if you don't know the ascii values, you could use....
Between asc("-") and asc("A")

Either will return records that start with "-" through "A".

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[fredg]

On Mon, 22 Jan 2007 06:19:18 -0500, Kc-Mass wrote:

Let me try to be clearer.

I have a table of authority ranges. The authority ranges are coded, as
examples, as AA12 to XCDR, F.78 to F234, F-23 to F-81, etc. Those
authority ranges are tied to Roles. If you have one or more roles in the
database you are given one or more authority ranges. I have a table with
the roles and the attendent ranges of authorities, stated as upper and lower
bounds. I also have a lookup table of all possible individual authorities
AA12, AA13, etc. What I need to do is create a new table with roles and
individual authorities. I have a module that does all of that through a
series of queries.

The queries basically select from the table of all authorities, those
authorities that are >= the low bound of the range and <= the high bound of
the range and insert them in the new table with the attendent role ID.

The problem is that MS Access sees the third range in the second sentence
above, "F-23 to F-81" as being included in the second range, "F.78 to F234".
That is true because Access reads those in any ordered comparison (=,>,<) as
being "F23 to F81" and pretends the hyphen isn't there. It is only the
hyphenated authorities that are creating problems. If Access viewed these
in conformance with ascii table values it would see the elements of the
range "F-23 to F-81" as being all less than and therefore outside the range
"F.78 to F234".

Any help appreciated.

"fredg" <fg******@example.invalidwrote in message
news:vi****************************@40tude.net...

>On Sun, 21 Jan 2007 15:25:45 -0500, Kc-Mass wrote:

>>Thanks very much Fred - great solution. If I want to, in a query, to
select
from a table of possible values can I ask for >= asc(FieldName) and
<=asc(fieldName) and get back all the legitimateasci values between first
and last fieldName?

Thx Much

"fredg" <fg******@example.invalidwrote in message
news:14***************************@40tude.net. ..
On Sun, 21 Jan 2007 13:41:16 -0500, Kc-Mass wrote:

*** Snipped ***

I'm not at all sure of what you're asking.
As I understand your question, you would first create a new query
column:

NewColumn:Asc([FieldName])

The above column returns the ascii value of just the first character
in the field.

If you know the ascii values, to filter the field, you could use, for
example....
Between 45 and 65

Or, if you don't know the ascii values, you could use....
Between asc("-") and asc("A")

Either will return records that start with "-" through "A".

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

You can use the replace function to replace both the dot and the
hyphen just for sorting purposes. You would use, for example, an "a"
to replace the hyphen, and a higher ascii value "b" to replace the
dot.
Here is the SQL of an example Select query that sorts a column with
both dot and hyphens as well as normal data.

You can adapt this to whatever you need to do.

SELECT tblBasicData.YourField,
Replace(Replace([YourField],"-","a"),".","b") AS SortThis
FROM tblBasicData
WHERE (((tblBasicData.YourField) Is Not Null))
ORDER BY Replace(Replace([YourField],"-","a"),".","b");

Data As Entered Data Sorted
AA12 AA12
XCDR D.23
F.78 F234
F234 F-23
F-23 F-81
F-81 F.78
D.23 H-20
D-23 H-23
H-20 H.20
H.20 XCDR
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Kc-Mass]

Many Thanks

"fredg" <fg******@example.invalidwrote in message
news:17******************************@40tude.net.. .

On Mon, 22 Jan 2007 06:19:18 -0500, Kc-Mass wrote:

>Let me try to be clearer.

I have a table of authority ranges. The authority ranges are coded, as
examples, as AA12 to XCDR, F.78 to F234, F-23 to F-81, etc. Those
authority ranges are tied to Roles. If you have one or more roles in the
database you are given one or more authority ranges. I have a table with
the roles and the attendent ranges of authorities, stated as upper and
lower
bounds. I also have a lookup table of all possible individual authorities
AA12, AA13, etc. What I need to do is create a new table with roles and
individual authorities. I have a module that does all of that through a
series of queries.

The queries basically select from the table of all authorities, those
authorities that are >= the low bound of the range and <= the high bound
of
the range and insert them in the new table with the attendent role ID.

The problem is that MS Access sees the third range in the second sentence
above, "F-23 to F-81" as being included in the second range, "F.78 to
F234".
That is true because Access reads those in any ordered comparison (=,>,<)
as
being "F23 to F81" and pretends the hyphen isn't there. It is only the
hyphenated authorities that are creating problems. If Access viewed
these
in conformance with ascii table values it would see the elements of the
range "F-23 to F-81" as being all less than and therefore outside the
range
"F.78 to F234".

Any help appreciated.

"fredg" <fg******@example.invalidwrote in message
news:vi****************************@40tude.net. ..

>>On Sun, 21 Jan 2007 15:25:45 -0500, Kc-Mass wrote:

Thanks very much Fred - great solution. If I want to, in a query, to
select
from a table of possible values can I ask for >= asc(FieldName) and
<=asc(fieldName) and get back all the legitimateasci values between
first
and last fieldName?

Thx Much

"fredg" <fg******@example.invalidwrote in message
news:14***************************@40tude.net.. .
On Sun, 21 Jan 2007 13:41:16 -0500, Kc-Mass wrote:
>
*** Snipped ***

I'm not at all sure of what you're asking.
As I understand your question, you would first create a new query
column:

NewColumn:Asc([FieldName])

The above column returns the ascii value of just the first character
in the field.

If you know the ascii values, to filter the field, you could use, for
example....
Between 45 and 65

Or, if you don't know the ascii values, you could use....
Between asc("-") and asc("A")

Either will return records that start with "-" through "A".

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

You can use the replace function to replace both the dot and the
hyphen just for sorting purposes. You would use, for example, an "a"
to replace the hyphen, and a higher ascii value "b" to replace the
dot.
Here is the SQL of an example Select query that sorts a column with
both dot and hyphens as well as normal data.

You can adapt this to whatever you need to do.

SELECT tblBasicData.YourField,
Replace(Replace([YourField],"-","a"),".","b") AS SortThis
FROM tblBasicData
WHERE (((tblBasicData.YourField) Is Not Null))
ORDER BY Replace(Replace([YourField],"-","a"),".","b");

Data As Entered Data Sorted
AA12 AA12
XCDR D.23
F.78 F234
F234 F-23
F-23 F-81
F-81 F.78
D.23 H-20
D-23 H-23
H-20 H.20
H.20 XCDR
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail