Changing a text field to numeric for sorting (1 Viewer)

jdp103

Registered User.
Local time
Today, 19:47
Joined
Sep 26, 2003
Messages
46
I have a field called Priority where the user will give it a numeric value to state the Priority level, i.e. Priority 1, Priority 2, etc. However, I had to make it a text field because they wanted to be able to input "NP" for Not Prioritized. The problem is that they want a report that sorts by the different priorities. When the report runs, it sorts 1, 10, 11, 12, 2, 21, 21, 3, etc. instead of 1, 2, 3, 4. I've tried making another field which converts it to a number, but then I get an error for the NPs, plus I can't select it to sort on.

Any ideas??
 

ColinEssex

Old registered user
Local time
Today, 23:47
Joined
Feb 22, 2002
Messages
9,110
Have a figure like 99 for the not prioritized ones.

Col
 

jdp103

Registered User.
Local time
Today, 19:47
Joined
Sep 26, 2003
Messages
46
I would love to make it 99 for Not Prioritized items, but they are insisting they use NP.
 

Mile-O

Back once again...
Local time
Today, 23:47
Joined
Dec 10, 2002
Messages
11,316
Now, if you had a table, tblPriorities with this structure:

tblPriorities
PriorityID
Priority

where PriorityID is an autonumber and Priority is the description, you can have the best of both worlds.
 
R

Rich

Guest
Function RemoveAlphas(ByVal AlphaNum As Variant)
Dim Clean As String
Dim Pos, A_Char$

Pos = 1
If IsNull(AlphaNum) Then Exit Function

For Pos = 1 To Len(AlphaNum)

A_Char$ = Mid(AlphaNum, Pos, 1)
If A_Char$ >= "0" And A_Char$ <= "9" Then
Clean$ = Clean$ + A_Char$
End If
Next Pos

RemoveAlphas = Clean$

End Function
 

ColinEssex

Old registered user
Local time
Today, 23:47
Joined
Feb 22, 2002
Messages
9,110
jdp103 said:
I would love to make it 99 for Not Prioritized items, but they are insisting they use NP.

Tell them they'll have what they're bloody given and be thankful for it!!!!:eek:
 
R

Rich

Guest
ColinEssex said:


Tell them they'll have what they're bloody given and be thankful for it!!!!:eek:
Have you ever tried arguing logic with someone who doesn't want to accept it?
 

jdp103

Registered User.
Local time
Today, 19:47
Joined
Sep 26, 2003
Messages
46
ColinEssex said:


Tell them they'll have what they're bloody given and be thankful for it!!!!:eek:

Sure, I'll tell them that! And by the way, you have any job openings? :)
 

ColinEssex

Old registered user
Local time
Today, 23:47
Joined
Feb 22, 2002
Messages
9,110
Rich said:

Have you ever tried arguing logic with someone who doesn't want to accept it?

You need to be persuasive and smooth talk them into your way of thinking the convince them it was their idea in the first place.

Its easier when YOU know what you're talking about and they don't:rolleyes:
 

ColinEssex

Old registered user
Local time
Today, 23:47
Joined
Feb 22, 2002
Messages
9,110
Actually, you can implement Mile-O's suggestion and they won't even know it records a number in the table. They'll only see the NP on reports and forms.
 

jdp103

Registered User.
Local time
Today, 19:47
Joined
Sep 26, 2003
Messages
46
Yes, I think that is the best way to handle it. Thanks everyone for your help!!
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:47
Joined
Aug 11, 2003
Messages
11,696
Sorry to join in the discussion so late but i think i have something to add.... non the less....

Why not use val() ???
SomeField = "NP"
?val(somefield)
0

Maybe in conjunction with iif???
SomeField = "NP"
?iif(isnumeric(somefield),val(somefield),-1)
-1
SomeField = "1"
?iif(isnumeric(somefield),val(somefield),-1)
1
SomeField = "99"
?iif(isnumeric(somefield),val(somefield),-1)
99
Or format():
?format(somefield,"00")
NP
SomeField = "1"
?format(somefield,"00")
01

Just to sort on .... ????:rolleyes:

Regards

The Mailman
 

Travis

Registered User.
Local time
Today, 16:47
Joined
Dec 17, 1999
Messages
1,332
I agree with Mailman,

Pad leading Zeros. However I would suggest more then two.

Select * From
Order By Format([Priority Field Name],'0000000000')

In a world of characters who needs numbers [Calculas Rules]
 

Fornatian

Dim Person
Local time
Today, 23:47
Joined
Sep 1, 2000
Messages
1,396
I agree that in the long run a lookup table with coded responses would be better than the current situation, but as a stop gap this will work well:

SELECT Code
FROM Table3
ORDER BY IIf(Code="NP",99,CInt(Code));

Assuming that all your codes are full numbers and "NP" is a single exception.
 

Users who are viewing this thread

Top Bottom