form

  • Thread starter Thread starter Evelyn Ballantyne
  • Start date Start date
E

Evelyn Ballantyne

I have a text box on the form giving the year of the next
anniversary
i would like to add st, nd, rd,th, when its 1st, 2nd 3rd
and 4th. I can get this with 1 2 3 and 4 but how can i
say only use the last digit when it reaches 21, 101 etc?
this is the code i am using in the text box on the form

=IIf([Text92]=1,"st",IIf([Text92]=2,"nd",IIf([Text92]
=3,"rd"," ")))

the form's source is a query

thank you in anticipation***
 
Evelyn

How would you describe the "rule" you (personally) use to decide what prefix
follows something like the 23rd anniversary?

I would:
rule out all the 'teens', and any number ending in "0", or "4" through
Click to expand...
"9" (don't all those use "th"?).
inspect the far right digit, applying your 1==>st, 2==>nd, 3==>rd rule
Click to expand...

Take a look at the Right() function for more ideas.

Good luck

Jeff Boyce
<Access MVP>
 
Hi Evelyn

The following function is give you the required results:

Function fCardinalDate(bytDay As Byte) As String
Select Case bytDay
Case 1, 21, 31
fCardinalDate = bytDay & "st"
Case 2, 22
fCardinalDate = bytDay & "nd"
Case 3, 23
fCardinalDate = bytDay & "rd"
Case Else
fCardinalDate = bytDay & "th"
End Select

End Function
 
Jeff, Thank you so much. I have come up eith this but I
am sure there is a better way. I tried using greater than
10 and less then 14 but it shows "th" on all instances

=IIf([Text92]=11,"th",IIf([Text92]=12,"th",IIf([Text92]
=13,"th",IIf(Right([Text92],1)=1,"st",IIf(Right
([Text92],1)=2,"nd",IIf(Right([Text92],1)
=3,"rd","th"))))))

I need to get rid of all those Iif's
Evelyn
 
Back
Top