Solved

Problem in sorting data that has ntext as the data type

Posted on 2011-09-07
28
562 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:Sashaski
  • 12
  • 10
  • 5
  • +1
28 Comments
 
LVL 75
ID: 36498543
You can try using CLng() on that field for display purposes such the it will sort correctly - as Numeric.

mx
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36498545
select DISTRICT from myTable
order by CAST(DISTRICT As int)
0
 
LVL 75
ID: 36498554
Example:

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


mx
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Sashaski
ID: 36498585
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
 

Author Comment

by:Sashaski
ID: 36498608
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
 
LVL 75
ID: 36498626


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
 
LVL 75
ID: 36498635
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
 

Author Comment

by:Sashaski
ID: 36498647
DatabaseMX:

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

Data type mismatch in criteria expression.
0
 
LVL 75
ID: 36498663
That's because of the NA  district.  You can't do that.  It's either Numeric text or it's not.

mx
0
 

Author Comment

by:Sashaski
ID: 36498693
<<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
 

Author Comment

by:Sashaski
ID: 36498699
<<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
 
LVL 75
ID: 36498736
Standby ...

0
 
LVL 75
ID: 36498780
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
 

Author Comment

by:Sashaski
ID: 36498796
Thank you for your help!

Am I placing this in the row source of my listbox still?
0
 
LVL 75
ID: 36498821
Yes ... but is that working?

mx
0
 

Author Comment

by:Sashaski
ID: 36498843
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36498919
try this:

order by iif(District,"NA",0,Clng(District))
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36498943
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36498968
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36498973
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36498983
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
 

Author Comment

by:Sashaski
ID: 36499113
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36499153
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
 
LVL 75
ID: 36499166
"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
 

Author Comment

by:Sashaski
ID: 36499197
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
 
LVL 75
ID: 36499242
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
 

Author Comment

by:Sashaski
ID: 36499272
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
 
LVL 75
ID: 36499379
"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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question