Problem in sorting data that has ntext as the data type

I am using SQL Server 2008.

The field type in is ntext.  

The way the data currently sorts in my listbox in my Access form.

DISTRICT
1
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
NA

I would like it to sort by:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
NA

The goal is that when I use this DISTRICT field in forms, I can sort ascending and have it look like the second example above.
SashaskiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Try this:

SELECT dbo_District.District_Id, Format(dbo_District.District,"00"), 2 FROM dbo_District
UNION SELECT 0, "<All>", 1  FROM dbo_District
ORDER BY 3,2

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can try using CLng() on that field for display purposes such the it will sort correctly - as Numeric.

mx
0
 
dwe761Software EngineerCommented:
select DISTRICT from myTable
order by CAST(DISTRICT As int)
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Example:

SELECT DISTINCT CLng([FIELD1]) AS Expr1
FROM Table1;


mx
0
 
SashaskiAuthor Commented:
Database MX...thank you for the quick reply.

This is the code i currently have in the row source of my multi select listbox that contains the DISTRICT field.

SELECT 0 as District_Id,  "<All>" as District FROM tblzNull UNION ALL SELECT dbo_District.District_Id, dbo_District.District FROM dbo_District
ORDER BY District;

How would I incorporate your suggested code into this?

0
 
SashaskiAuthor Commented:
dwe761 - I tried this your code in SQL and received this result.

sg 529, Level 16, State 2, Line 1
Explicit conversion from data type ntext to int is not allowed.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:


SELECT 0 as District_Id,  "<All>" as District FROM tblzNull UNION ALL SELECT dbo_District.District_Id, CLng(dbo_District.District) FROM dbo_District
ORDER BY District;
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
oops

SELECT 0 as District_Id,  "<All>" as District FROM tblzNull UNION ALL SELECT dbo_District.District_Id, CLng(dbo_District.District) FROM dbo_District
ORDER BY CLng(District);
0
 
SashaskiAuthor Commented:
DatabaseMX:

I copied & pasted the code you posted above and received the following error message:

Data type mismatch in criteria expression.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
That's because of the NA  district.  You can't do that.  It's either Numeric text or it's not.

mx
0
 
SashaskiAuthor Commented:
<<oops

SELECT 0 as District_Id,  "<All>" as District FROM tblzNull UNION ALL SELECT dbo_District.District_Id, CLng(dbo_District.District) FROM dbo_District
ORDER BY CLng(District);
>>

when running this code...received this error


Untitled.jpg
0
 
SashaskiAuthor Commented:
<<That's because of the NA  district.  You can't do that.  It's either Numeric text or it's not.

Can I replace the existing NA with 18?  
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Standby ...

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this

SELECT 0 as District_Id,  "<All>" as District FROM dbo_District
UNION ALL SELECT dbo_District.District_Id, CLng(Replace (dbo_District.District,"NA",18)  ) FROM dbo_District
ORDER BY 2
0
 
SashaskiAuthor Commented:
Thank you for your help!

Am I placing this in the row source of my listbox still?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes ... but is that working?

mx
0
 
SashaskiAuthor Commented:
No.  It returned this as the result when I ran it.
District_Id      District
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
0      <All>
17      1
8      10
5      11
11      12
9      13
10      14
2      15
7      16
12      17
16      18
1      2
3      3
6      4
15      5
4      6
13      7
14      8
18      9
0
 
HainKurtSr. System AnalystCommented:
try this:

order by iif(District,"NA",0,Clng(District))
0
 
HainKurtSr. System AnalystCommented:
or

order by isnumeric(District), iif(District,"NA",0,Clng(District))

or

order by isnumeric(District) desc, iif(District,"NA",0,Clng(District))

to put NA at the beginning/end...
0
 
HainKurtSr. System AnalystCommented:
oops sorry

order by iif(District="NA",0,Clng(District))
order by isnumeric(District), iif(District="NA",0,Clng(District))
order by isnumeric(District) desc, iif(District="NA",0,Clng(District))
0
 
HainKurtSr. System AnalystCommented:
order by isnumeric(District), iif(District="NA",0,Clng(District))

District
1
3
5
12
NA

order by iif(District="NA",0,Clng(District))

District
NA
1
3
5
12
0
 
SashaskiAuthor Commented:
HainKurt:

order by iif(District="NA",0,Clng(District))
order by isnumeric(District), iif(District="NA",0,Clng(District))
order by isnumeric(District) desc, iif(District="NA",0,Clng(District))

when I replaced the order by with any one of the three instances above, I received an error "The ORDER BY expression includes fields that are not included in the query...

DatabaseMX

SELECT dbo_District.District_Id, Format(dbo_District.District,"00"), 2 FROM dbo_District
UNION SELECT 0, "<All>", 1  FROM dbo_District
ORDER BY 3,2

WORKED Beautifully!

For the sake of my learning process, would I have avoided this if the field type would have been int originally?

Will I need to carry this code to any other forms/reports that include the Districts field where I need to sort?  This is of great concern as my next step is to create a report using the Districts field and it will need to be sorted as well.

Thank you!

0
 
HainKurtSr. System AnalystCommented:
my posts should work!

what is the query, maybe you are using different columns...

order by iif(District="NA",0,Clng(District))
order by isnumeric(District), iif(District="NA",0,Clng(District))
order by isnumeric(District) desc, iif(District="NA",0,Clng(District))
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"For the sake of my learning process, would I have avoided this if the field type would have been int originally?"

Yes.

"Will I need to carry this code to any other forms/reports that include the Districts field where I need to sort? "
I think the issue is mainly because of a List box.  And no easy way to get 'numbers' right justified either.  That's why I used Format.  However, if you don't care about justified ... and would rather not have the leading zero ... then do this:

SELECT dbo_District.District_Id, Format(dbo_District.District,"#0"), 2 FROM dbo_District
UNION SELECT 0, "<All>", 1  FROM dbo_District
ORDER BY 3,2
0
 
SashaskiAuthor Commented:
Lesson learned on the ntext to int for numerical items.  In fact, it originated as a number type in Access, after running the upsize wizard, it changed a lot of my field types on the SQL side and I didn't bother to go through each field and check.  Another lesson learned!

Glad to hear this is an isolated text box issue.

I tried the code you placed above, it reverted back to the old sort.  I really don't mind the leading 0, in fact it looks a little cleaner in the code.  Is there a way I can change the table data to the same so they match?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Opps ... yep   #0 won't work ... in fact, I tried that earlier ... !

"Glad to hear this is an isolated text box issue."
>> List Box ... and 'more or less'

" Is there a way I can change the table data to the same so they match?"
If you mean to have the leading zero ... then an Update query I suppose (make a backup first)

UPDATE dbo_District
SET dbo_District.District= Format([District],"00");

Don't forget to close the Q now :-)

mx
0
 
SashaskiAuthor Commented:
I'm going to close this issue and open another (please give me advice if I'm doing this correctly as I'm new to asking questions)...

The initial problem I was having with the list box not sorting properly, you have resolved hence this Q is closed.

I've just tried sorting this same field in a query to prepare for the report and it is not going to sort properly.  As there will be many more forms & reports in the future utilizing this field, I think the need to change it to an int type is SQL will resolve future problems with sorting on this field...

Again, please advise 1) if you think changing this ntext field to int in SQL will alleviate future problems AND if I should open a new question based on that.

Thanks so much for your support DabaseMX!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Again, please advise 1) if you think changing this ntext field to int in SQL will alleviate future problems AND if I should open a new question based on that."

Yes ... and ... Yes :-)

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.