Access Sort Order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a directory database that I have sorted alphabetically. My problem is
with last names that have prefixes (such as De La Cruz). Our organization's
standards say this should fall between names with Dek... and Dem..., but
Access treats the space as a space and places the names before Dea... The
standards suggested inserting a hard space so the computer would ignore the
break in the name.

How do I insert a hard space in the table data? Will it work if I do? Or
is there a better solution to this?

Thanks,
kc
 
I'm answering part of my own question. I figured out how to get the hard
space in the data, but it did not effect the sort order.

So I still need to find a solution to this problem. Any one have any ideas?

Thanks,
kc
 
kc said:
I have a directory database that I have sorted alphabetically. My
problem is with last names that have prefixes (such as De La Cruz).
Our organization's standards say this should fall between names with
Dek... and Dem..., but Access treats the space as a space and places
the names before Dea... The standards suggested inserting a hard
space so the computer would ignore the break in the name.

How do I insert a hard space in the table data? Will it work if I
do? Or is there a better solution to this?

Thanks,
kc
Click to expand...

I think your only answer is a sort order filed where you put the name in
and delete the spaces. Use it just for sorting.
 
Like this --
Sort_Filed: Replace([YourLastNameField]," ","")


De La Cruz will then be DeLaCruz and sort like you want.
 
Change the Order By clause to something like:

Order by Replace([Last Names]," ","");

You'll need to do this in the SQL view and if you have a lot of names, it's
going to be slow.
 
Jerry said:
Change the Order By clause to something like:

Order by Replace([Last Names]," ","");

You'll need to do this in the SQL view and if you have a lot of
names, it's going to be slow.
Click to expand...

I like that idea, even if I did not think of it. However if the OP will
be using it often, I might suggest using that formula to create a new field
with the edited names to speed things up.
 
Agreed if there are many thousands of records. Indexing such a field should
work much faster then running a sort with a function. KC could do a one-time
update of the records then have the form populate both fields for new
records. Probably a good idea to run an update query every once in a while in
case someone changes their last name.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Joseph Meehan said:
Jerry said:
Change the Order By clause to something like:

Order by Replace([Last Names]," ","");

You'll need to do this in the SQL view and if you have a lot of
names, it's going to be slow.
Click to expand...

I like that idea, even if I did not think of it. However if the OP will
be using it often, I might suggest using that formula to create a new field
with the edited names to speed things up.
Click to expand...
 
Jerry said:
Agreed if there are many thousands of records. Indexing such a field
should work much faster then running a sort with a function. KC could
do a one-time update of the records then have the form populate both
fields for new records. Probably a good idea to run an update query
every once in a while in case someone changes their last name.
Click to expand...

Very true, forgetting that is one of those things that tend to come
back and bite.
Jerry said:
Change the Order By clause to something like:

Order by Replace([Last Names]," ","");

You'll need to do this in the SQL view and if you have a lot of
names, it's going to be slow.
Click to expand...

I like that idea, even if I did not think of it. However if the
OP will be using it often, I might suggest using that formula to
create a new field with the edited names to speed things up.

I have a directory database that I have sorted alphabetically. My
problem is with last names that have prefixes (such as De La Cruz).
Our organization's standards say this should fall between names
with Dek... and Dem..., but Access treats the space as a space and
places the names before Dea... The standards suggested inserting
a hard space so the computer would ignore the break in the name.

How do I insert a hard space in the table data? Will it work if I
do? Or is there a better solution to this?

Thanks,
kc
Click to expand...
Click to expand...
Click to expand...
 
Back
Top