I have a table with a field -- Code -- containing the following values:
+1233
1233
-1233
w-aaa
wcc
wcc'
wcc k
wcc!
wcc,
wcc'a
wcch
wccm
wcc'
w-zzz
These are shown above in Ascending order if ordered with the A|Z button on the Table Datasheet Toolbar. Also this is the order when SQL = SELECT Code FROM Test ORDER BY Code.
Immediate Window ?"+1233" < "-1233" gives me True.
As I can set a database sort order in Tools->Options, with about 30 choices, I presume that Access has tables to convert from ASCII to a sort order differing by language.
Is it possible for me to create and use such a table?
OldBirdman
4  1687  ChipR 1,287
Recognized Expert Top Contributor
Does anyone know where to find the options equivalent to the tools->options in Access 2007? I can't find it at all.
ChipR 1,287
Recognized Expert Top Contributor
The only thing I can come up with so far to compare numbers accurately when mixed with strings is to use the IsNumeric() function to sort the numbers from the text and sort them separately. That does handle the "+" correctly. Otherwise I don't see how any string value for + is going to sort +1, 1, +2, 2, +3, 3 correctly, or anything similar.
First, I notice a typo in the "Sorted" list originally presented. The second occurrance of wcc' should be wcc's in the second from bottom item. Sorry.
For true numeric strings, IsNumeric would allow creating a second field in the table and storing the numeric portion in that field. Then the table could be sorted by ORDER BY LeadingNumeric, Code. If my new field -- LeadingNumeric-- were filled by LeadingNumeric = Val(Code), and non-numeric strings had the value 9999999999 to force the alpha data to the end, Unless the numeric were imbedded in the string, i.e. X+1233X, X1233X, X-1233X, then the original problem occurs.
This doesn't address the problems with special characters, nor the fact that I may not want the Upper and Lower case alphabetic characters to be considered identical.
Original question - can I define my own sort order?
Sorry, OldBirdman, I too am not aware of any way that a custom sort order table can be defined and applied at run-time that would change the way Access sorts alpha-numeric data. When I need to define custom ordering I create calculated fields in the underlying query that reflect the components of the sort, then combine these to create a single custom field that can be sorted using the default alphanumeric sorting.
-Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Jeremy Howard |
last post by:
Hello everyone,
I'm not a database guru so I'm sorry if this is a dumb question but here it
goes...
I have this sql query that I'm trying to run against a table on a Sql 2k
server:
SELECT *
FROM ChangeTable
|
by: John Baker |
last post by:
Hi:
I have a query that includes a calculation. where I am computing the number of minutes
between two times as in :
Minute(-) This results in the number of Minutes more than a rounded hour.
I want to show this in the query result as a two digit field. This works fine when the
result is greater than 10 minutes, but when its less than 10 minutes the single character
appears. For example, 4:05 minus 4:00 comes up with 5. I would like...
|
by: ZRexRider |
last post by:
Hi,
I have an application that uses MS-SQL Linked tables. I have a utility
routine that drops all links and re-establishes them. It works great
when my connection string is a DSN connection but I would like to
change it to DSN-Less so users don't have to set up ODBC connections on
each workstation.
If I use the following DSN
|
by: PengYu.UT |
last post by:
Hi,
Sometimes, I write = instead of == in if conditions by mistakes. Is
there any way the gcc compiler can give me a warning?
Best wishes,
Peng
|
by: Martin Jørgensen |
last post by:
Hi,
- - - - - - - - - - - - - - -
#include <iostream>
#include <string>
#include <map>
using namespace std;
int main()
{
| | |
by: Greg Strong |
last post by:
Hello All,
The short questions are
1 Do you know how to make DSN connection close in Access to Oracle 10g
Express Edition?
&/or
2 Do you know how to make a DSN-less pass-through query work from
|
by: ek |
last post by:
I have the following class:
template<typename I>
class test {
public:
test(I i) : pp(i) {}
I getpp() const {
return pp;
}
|
by: desktop |
last post by:
I get this error when I run my program:
red_black_tree.cpp:188: error: no matching function for call to
‘std::less<MyObj<int::less(MyObj<int>&, MyObj<int>&)’
/usr/lib/gcc/i486-linux-gnu/4.1.2/../../../../include/c++/4.1.2/bits/stl_function.h:224:
note: candidates are: std::less<MyObj<int::less()
/usr/lib/gcc/i486-linux-gnu/4.1.2/../../../../include/c++/4.1.2/bits/stl_function.h:224:
note: std::less<MyObj<int::less(const...
|
by: gnidoc |
last post by:
This is a small perl script I wrote to check mirror lists (files filled with one URL per line). It prints " http://..." when an URL can't be retrieved and just prints the url when everything goes fine. The code is pretty straightforward and it works well until I am trying to redirect its output.
There is one thing I can't figure out. As far as I know the program called 'less' prints its input on the screen providing a nice interface for...
|
by: ukfusion |
last post by:
I have an sql query which has several fields and no matter how hard i try i cant get the less than statement to work....i originally tried between but that wouldnt work either.
$yourpricesearch is the name of the drop box with in the search form with the price values of 5.00, 10.00, 15.00 etc...i did it this way as i wasn't sure if when i put the AND in when the value of $yourpricesearch was empty would cause problems so im only putting the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
