Contacts are not being sorted alphabetically.

[access2010]

Could you please tell me how to correct the sorting in our Access Database of Contacts / Companies?

Thank you
Marylyn

 

[pbaldy]

There's a hidden tab or something at the beginning. If I paste into Notepad after entering a dash, I get:

- Estx Weingrox

 

[CJ_London]

Seems like the same question here

Sorting of data not working

Could we please have assistance in our Access 2003 sorting problem? When the form opens, we would like the form to be sorted by, Analyst Name / Analyst Company, but for some reason the form has two sorting groups that are not being properly sorted. The same problem occurs at; The top left...

www.access-programmers.co.uk

 

[Pat Hartman]

@access2010 If you look at the answer to the earlier question, you should realize that the problem is not Access but some data entry problem.

 

[access2010]

Thank you for your suggestions.
Would using a filter such as below help to overcome our problem and if yes would the code be placed in the Table's Field or on the Form?

Is Null Or Not Like "*[!a-z OR "" ""]*"

Thank you
Marylyn

 

[plog]

Filtering only effects ordering in that it eliminates entire records from showing in the results. Is that what you want? Or do you want all records to show and in the order you expect them to?

The most likely solution is to fix your data.

 

[June7]

Why would a filter help with sort?

The hidden character is ascii 9 (horizontal tab).

Best solution would be to find out why this is happening and prevent it.

Meanwhile, a Replace UPDATE can fix the data.

UPDATE Investment_Analyst_T SET Analyst_Name = Replace(Analyst_Name, Chr(9), "")

 

[jdraw]

I created a query to show the first char of Analyst_Name and its ASC value.

Query1 Query1

A_FChar	asc_X	Analyst_Name
A	65​	Alex Creet
A	65​	Aaron Neil
A	65​	Afilippo Flomooo
A	65​	Aaron Bkoski
A	65​	ADAM TDLE
A	65​	AARON KSLR
A	65​	Adam Bkham
A	65​	ALEX THOMSON
A	65​	Adam Sden
A	65​	Ahmad Shthaa
$	36​	$_STl
A	65​	Akash Twnii
A	65​	ALEX BSTIN
A	65​	ADAM BTTY
	9​	Mike Ulz
	9​	Esell Weingrod
A	65​	Adam Fisch
	9​	Estx Weingrox
	9​	Mal Ulzt

 

[access2010]

Thank you all for your suggestions. We now enter the names in a text document and then copy it to the database.

Thank you
Marylyn

 

[jdraw]

Did you ever determine what caused the leading tab (ASC 9) character?

 

[Minty]

That seems inefficient and won't necessarily solve the issue - the database is designed for data entry and can be used to remove and sanitise inputs.

You could check for non-alphanumeric characters at the start of the text in the after-update event.

 

[access2010]

Did you ever determine what caused the leading tab (ASC 9) character?

jdraw​

The volunteers had taken the data from a web sheet that we had received and a bad character was picked up. Now we enter the data into notepad and copy the data into the database.

 

[jdraw]

Thanks for responding. However, I agree with Minty and June7, you could use your "web sheet" and confirm there were no bad characters before accepting the input. Seems you have added extra steps, but as long as it works for you, problem solved.

 

[Pat Hartman]

If you are going to type the data anyway, type it directly into the Access form where you can validate it.

Also, when you copy stuff from a web page, sometimes pasting it into notepad will get rid of extraneous characters. Then you can save the notepad file, link to it and use an append query that screens out the bad characters. But, copy and paste into an Access table allows the bad characters to be included and you can't edit them out on the way in.