Solved

Problem in sorting data that has ntext as the data type

Posted on 2011-09-07
28
528 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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:HainKurt
ID: 36498919
try this:

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

Expert Comment

by:HainKurt
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:HainKurt
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 - Access MVP) 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:HainKurt
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:HainKurt
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Row-Level Security 2 20
Why wont the Phone List report sort by Last Name? 2 15
Service Statictic 11 14
Code editor Problem 8 16
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server views 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 Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now