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 6 7748
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
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
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: The Bicycling Guitarist |
last post by:
Hi. I found the following when trying to learn if there is such a thing as a
non-breaking hyphen. Apparently Unicode has a ‑ but that is not
well-supported, especially in older browsers. Somebody...
|
by: Mark |
last post by:
My PHP script builds a table that is too wide to fit on the paper. Two
of the columns contain strings that are more lengthy than data in the
other columns.
I can get the table to fit by letting...
|
by: Matt |
last post by:
I would like to set the "list-style-type" to be a hyphen (-). How can I
accomplish this in a style sheet. I tried list-style-type: hyphen; and
list-style-type: dash; but neither worked. I also...
|
by: stevenkobes |
last post by:
If a word has a hyphen in it, IE will permit a line break at the
hyphen, but Firefox/Mozilla won't. Apparently the Firefox behavior is
standards-compliant, but it is not what I want. Is there a...
|
by: Bill Cohagan |
last post by:
I'm trying to generate class definitions from an XSD that contains something
like:
<xs:complexType name="foo" mixed="true">
<xs:choice minOccurs = "0" maxOccurs="unbounded">
<xs:element name =...
|
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB...
|
by: Niklas Uhlin |
last post by:
Why does the code below output this:
"v a" (118,32,97)
"w a" (119,32,97)
"v b" (118,32,98)
"w b" (119,32,98)
"v c" (118,32,99)
"w c" (119,32,99)
and not this (as one would expect if...
|
by: Rubin |
last post by:
1) I want to show a breaking hyphen in Mozilla 1.5.0.4
How do I do that?
"Unicode standard annex #14", <http://www.unicode.org/reports/tr14/>,
defines 4 breaking hyphens.
<quote>
Breaking...
|
by: artev |
last post by:
If sort this work:
var myarray= new Array(10,16,35,"0.1",8,4,22,19,1,22,35,9,26,38,40);
with code
function function1(a,b)
{return a - b}
var order02=new Array();...
|
by: william.hooper |
last post by:
There is a longer article about this subject here:
http://www.codeproject.com/useritems/SortedList_Bug.asp
See the main article and the reply thread started by Robert Rohde.
Alternatively look...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| | |