1st, 1st Floor, 1 ....update them all into 1st 1

[ironj32]

I have a field, [_COMPLETE DATABASE].Floor, where i have multiple different formats... 1st, 1st floor, 2nd floor, 1, 2, etc... i would like to format all of the entries to equal 1st, 2nd, 3rd, 4th, 5th, etc... i know i will need to run an update query but am not sure what the SQL should be.
thanks in advance!

 

[lespaul]

do the floors go greater than 9? like floors 10 and 11?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ironj32]

yes they do.

 

[traingamer]

Use the val function to return just the numeric portion and format accordingly.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Golom]

Build a function

Public NumSuffix(Num as Long) As String
   Dim LastDigit As String   
   NumSuffix = "th"
   If Num < 4 Or Num > 20 Then
      LastDigit = Right(Format(Num,"0"), 1)
      Select Case LastDigit
         Case "1":   NumSuffix = "st"
         Case "2":   NumSuffix = "nd"
         Case "3":   NumSuffix = "rd"
      End Select
   End If
End Function

And then

UPDATE [_COMPLETE DATABASE] 

SET [Floor] = Val([Floor]) & NumSuffix(Val([Floor]))

which should work OK as long as you don't have more than 110 floors.

 

[ironj32]

how come when i run the val function i get a "data type mismatch in criteria expression" warning box?

SELECT [_COMPLETE DATABASE].Floor
FROM [_COMPLETE DATABASE]
ORDER BY Val(Floor);


i do have some records in the table that are "Ground", "Main", "Skyway".

would this be causing it?

 

[PHV]

If Floor may hold Null:
ORDER BY Val(Floor & "")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ironj32]

thanks PHV, but that gives me the results just as if i was running a normal select query with no criteria.
i need to get all the records that start out with numbers
3rd, 2nd floor, 1, 12th, etc... and i need them to drop the suffix's
so results will look like 3, 2, 1, 12

 

[PHV]

SELECT Val(Floor & "")
FROM [_COMPLETE DATABASE]
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ironj32]

that's what i needed. thanks!