Problem Sorting Text Field

[OhBother30]

I am running into a little trouble sorting a text field in my database. The field contains customer names (entered last name, first name) and business names. The problem I am having is mostly with business names. I will need to produce a report that is sorted in alpha order to do an inventory on the customer files.
My report is not sorting in correct order. For example "A CUT ABOVE LAWN CARE SERVICE INC" is sorting above "AAA APPLIANCE SERVICE INC". It evedently has something to do with spaces.

Any information or advice would be appreciated. Thanks in advance!

 

[TLady]

I think you'll have to write an Update Function which will index fields and run it every time before actual query run.

TIA

 

[OhBother30]

I ended up using this code to repove the spaces for sorting. It seems to work great.
OhBother

Function ReplaceInString(ByVal StringIn As String, _
CharReplaced As String, _
CharToReplaceTo As String) As String
Dim intInstr As Integer
Dim strTemp As String
intInstr = InStr(StringIn, CharReplaced)
Do While intInstr > 0
strTemp = strTemp & Left(StringIn, intInstr - 1) & CharToReplaceTo
StringIn = Mid(StringIn, intInstr + Len(CharReplaced))
intInstr = InStr(StringIn, CharReplaced)
Loop
strTemp = strTemp & StringIn
ReplaceInString = strTemp
End Function