Changing a text field to numeric for sorting

[jdp103]

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]

Have a figure like 99 for the not prioritized ones.

Col

 

[jdp103]

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

 

[Mile-O]

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.

 

[Rich]

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]

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!!!!

 

[Rich]

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]

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]

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]

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]

Yes, I think that is the best way to handle it. Thanks everyone for your help!!

 

[namliam]

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]

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]

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.