Dear Frank:
May I butt in?
Consider arranging these alphabetically:
AA
AAA
BB
These are already alphabetical. Well, "numbers" that are stored as
text use exactly the same rule:
11
111
22
Now you and I know 111 does NOT fall between 11 and 22 in "numerical"
sequence. But in alphabetical sequence it certainly does! Because
alphabetical sequence uses a completely different rule, comparing
character by character from left to right.
Numerical sequence actually works as though it were comparing
character by character, but it assumes sufficient leading zeros to
make all the strings the same length! In fact, text values containing
only digits can be made to sort in numeric order by changing them this
way. Thus, the above example changes to:
011
111
022
Now this is obviously NOT in order any way you look at it. But, the
first step is to add leading zeros so all the strings are the same
length. Now, this would sort alphabetically:
011
022
111
See, it works when all the number strings are the same length. But
our minds tend to automatically "add" the leading zeros, or spaces, or
whatever, before making the comparison. Just think how, when an
accountant uses his preprinted sheets, he must write the numbers in
right to left so as to leave leading spaces, so the columns line up.
Your mind is trained to do things like that automatically. But a
computer doesn't make such leaps of intellect, even though humans
pretty much presume them. Our presumptions cause us to misunderstand
the computer!
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
John,
Sorry, I am confused. So my field name is Number (text field which contains
all the numbers), Where do I put this calculated field? Is the number field
in my query? would this be my calculated field - SearchMember: Val([Member
#])
I sure sound pretty dumb, but believe it or not, I am an MCP, w2000&w2000
server. I know windows very well, this stuff I do not.I am obviously lacking
the language structure. I guess it is hopeless. Thanks for trying, I guess I
will continue to research as best I can.
Sincerely confused,
Frank
John Vinson said:
It is a text field. I truly believe the only purpose of this field is the
member certificate #, which is the # they use to sign for dinner, pay dues
etc. I know how to convert it to a number field, but I think I should try
and find out from the developers if this will cause any problems. The whole
program is actually a problem. Poor design, very poor!
Thanks
...
my intent. No, I did not know Between "700" and "800" will select the
following Members, for example:"7010" "7999" etc ...?
It will because Text values are handled differently than numbers.
Considered AS A TEXT STRING, the string "7010" is in fact larger than
the string "700" and less than the string "800", in precisely the same
way that the string "HABA" is alphabetically greater than the string
"HAA" and less than the string "IAA".
If you want the member number to be considered numerically, create a
Query based on your table and put a calculated field by typing
SearchMember: Val([Member #])
(or whatever the field name is). This will be a Number field, on which
you can use a criterion
BETWEEN 700 AND 800
Note - *no* quotemarks; numbers use no delimiter, Text values need
quotes.
