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

#### jdp103

##### Registered User
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
Have a figure like 99 for the not prioritized ones.

Col

#### jdp103

##### Registered User
I would love to make it 99 for Not Prioritized items, but they are insisting they use NP.

#### Mile-O

##### Back once again...
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
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!!!!

R

#### Rich

##### Guest
ColinEssex said:

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

#### jdp103

##### Registered User
ColinEssex said:

Tell them they'll have what they're bloody given and be thankful for it!!!!
Sure, I'll tell them that! And by the way, you have any job openings?

#### ColinEssex

##### Old registered user
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

#### ColinEssex

##### Old registered user
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
Yes, I think that is the best way to handle it. Thanks everyone for your help!!

#### namliam

##### The Mailman - AWF VIP
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 .... ????

Regards

The Mailman

#### Travis

##### Registered User
I agree with Mailman,

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

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

#### Fornatian

##### Dim Person
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.