What character can I use to force a record to come after Z in sort

[DanJ]

I have a program in Access which creates transaction records in a table.
Normally, the Name of a person is used and that column is used to sort the
invoice detail records. Howver, I now want to add transactions which are not
based on the name and I want them to sort at the bottom of the list. I have
tried adding special characters like "[" to the beginning of the string, but
Access seems to ignore them in the sort. What character can I use so that
Access will always sort the record below Z. I would like it to be a
character that doesn't look odd, but still does the job. For example, I
don't want to add "zz_" to the string because this would confuse the
customer. I'd like to use some type of symbol or invisible character if
possible. Anyone know what character Access will not ignore and place at
the bottom of the sort order?

 

[John W. Vinson]

I have a program in Access which creates transaction records in a table.
Normally, the Name of a person is used and that column is used to sort the
invoice detail records. Howver, I now want to add transactions which are not
based on the name and I want them to sort at the bottom of the list. I have
tried adding special characters like "[" to the beginning of the string, but
Access seems to ignore them in the sort. What character can I use so that
Access will always sort the record below Z. I would like it to be a
character that doesn't look odd, but still does the job. For example, I
don't want to add "zz_" to the string because this would confuse the
customer. I'd like to use some type of symbol or invisible character if
possible. Anyone know what character Access will not ignore and place at
the bottom of the sort order?

Windows System... All Programs... Accessories... System Tools offers the
Character Map. I'd suggest either

{End}

since the left curly bracket is right after "z", or the vertical bar character
| which comes next after that.

 

[KARL DEWEY]

Try using '~' as it is ASCII 126 and lower case 'z' is 122.

 

[tina]

you might be able to set up a query with a calculated field to sort on,
leaving your "string" pristine - but without more details, i can't make a
specific suggestion. for instance, is there something about these additional
transaction records that distinguish them from the "normal" records?

hth

 

[DanJ]

The Ascii characters are apparently ignored when Access does an Alpha sort.
If you add a "{" character which is Chr(123), a sort will put that alpha
string at the top of the sort order even though 'z' is Chr(122). You can
experiment with this by opening any table with alpha data and adding a "{" in
front of the string. Then, click the sort button and you will seed that this
record will be before A instead of after z. Makes no sense to me.

I have a program in Access which creates transaction records in a table.
Normally, the Name of a person is used and that column is used to sort the
invoice detail records. Howver, I now want to add transactions which are not
based on the name and I want them to sort at the bottom of the list. I have
tried adding special characters like "[" to the beginning of the string, but
Access seems to ignore them in the sort. What character can I use so that
Access will always sort the record below Z. I would like it to be a
character that doesn't look odd, but still does the job. For example, I
don't want to add "zz_" to the string because this would confuse the
customer. I'd like to use some type of symbol or invisible character if
possible. Anyone know what character Access will not ignore and place at
the bottom of the sort order?

Windows System... All Programs... Accessories... System Tools offers the
Character Map. I'd suggest either

{End}

since the left curly bracket is right after "z", or the vertical bar character
| which comes next after that.

 

[DanJ]

The Ascii characters are apparently ignored when Access does an Alpha sort.
If you add a "{" character which is Chr(123), a sort will put that alpha
string at the top of the sort order even though 'z' is Chr(122). You can
experiment with this by opening any table with alpha data and adding a "{" in
front of the string. Then, click the sort button and you will seed that this
record will be before A instead of after z. Makes no sense to me.

Try using '~' as it is ASCII 126 and lower case 'z' is 122.
--
KARL DEWEY
Build a little - Test a little

I have a program in Access which creates transaction records in a table.
Normally, the Name of a person is used and that column is used to sort the
invoice detail records. Howver, I now want to add transactions which are not
based on the name and I want them to sort at the bottom of the list. I have
tried adding special characters like "[" to the beginning of the string, but
Access seems to ignore them in the sort. What character can I use so that
Access will always sort the record below Z. I would like it to be a
character that doesn't look odd, but still does the job. For example, I
don't want to add "zz_" to the string because this would confuse the
customer. I'd like to use some type of symbol or invisible character if
possible. Anyone know what character Access will not ignore and place at
the bottom of the sort order?

 

[Maarkr]

I would do kindof like Tina suggested... I would add a field like EndSort
(yes/no field) so you could just click the box at the form entry to identify
the sort at the end, and build a query grouping the EndSort=0 and EndSort=-1
for your report. May not be the best way depending on your situation, but it
may eliminate data problems due to remembering which character to use or
another user not knowing what it means...

 

[DanJ]

In my normal process, the transactions are added and sorted by name. There
are rare exceptions were there are additional summary transactions which need
to be added to the name list. I just wanted to add a character to the front
of those strings like a 'bullet' to highlight those and make them sort and
the bottom of the list when the customer chooses to do an alpha sort on name.
It does not matter what ascii character I choose, whether it's ascii number
is higher than z or lower than z, it always sorts with the non-alpha
characters before A.

you might be able to set up a query with a calculated field to sort on,
leaving your "string" pristine - but without more details, i can't make a
specific suggestion. for instance, is there something about these additional
transaction records that distinguish them from the "normal" records?

hth

I have a program in Access which creates transaction records in a table.
Normally, the Name of a person is used and that column is used to sort the
invoice detail records. Howver, I now want to add transactions which are not
based on the name and I want them to sort at the bottom of the list. I have
tried adding special characters like "[" to the beginning of the string, but
Access seems to ignore them in the sort. What character can I use so that
Access will always sort the record below Z. I would like it to be a
character that doesn't look odd, but still does the job. For example, I
don't want to add "zz_" to the string because this would confuse the
customer. I'd like to use some type of symbol or invisible character if
possible. Anyone know what character Access will not ignore and place at
the bottom of the sort order?

 

[KARL DEWEY]

In the past I added a number field and placed 1 or 0 to control the sort.
--
KARL DEWEY
Build a little - Test a little

The Ascii characters are apparently ignored when Access does an Alpha sort.
If you add a "{" character which is Chr(123), a sort will put that alpha
string at the top of the sort order even though 'z' is Chr(122). You can
experiment with this by opening any table with alpha data and adding a "{" in
front of the string. Then, click the sort button and you will seed that this
record will be before A instead of after z. Makes no sense to me.

Try using '~' as it is ASCII 126 and lower case 'z' is 122.
--
KARL DEWEY
Build a little - Test a little

I have a program in Access which creates transaction records in a table.
Normally, the Name of a person is used and that column is used to sort the
invoice detail records. Howver, I now want to add transactions which are not
based on the name and I want them to sort at the bottom of the list. I have
tried adding special characters like "[" to the beginning of the string, but
Access seems to ignore them in the sort. What character can I use so that
Access will always sort the record below Z. I would like it to be a
character that doesn't look odd, but still does the job. For example, I
don't want to add "zz_" to the string because this would confuse the
customer. I'd like to use some type of symbol or invisible character if
possible. Anyone know what character Access will not ignore and place at
the bottom of the sort order?

 

[John W. Vinson]

The Ascii characters are apparently ignored when Access does an Alpha sort.
If you add a "{" character which is Chr(123), a sort will put that alpha
string at the top of the sort order even though 'z' is Chr(122). You can
experiment with this by opening any table with alpha data and adding a "{" in
front of the string. Then, click the sort button and you will seed that this
record will be before A instead of after z. Makes no sense to me.

I guess setting OPTION COMPARE BINARY would be WAY overkill for this... :-{(