Trying to get 61 to 61st - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Trying to get 61 to 61st

Jerry Fowler

3 years ago

Ok I have a database for a Ferternal organization and they want to send out birthday cards each month so I have a query for members with birthday each month and their age.  Everything works Yay!!!

my issue right now is that they want to add the members age on the card like "Happy <Age> Birthday" so right now its printing "Happy 54 Birthday"  they would like the 54 to be 54th.  So in the query I am calculating their age as:

AgeEOY: CStr(DateDiff("yyyy",[MembershipActivePlusEmailQ]![Birthday],DateSerial(Year(Date()),12,31)))

and that is working fine and converting it to a string so I can Concatenate the rest onto it.  So I'm trying this

AgeEOY2: IIf(Right([AgeEOY],1)=1,[AgeEOY]=([AgeEOY]) & "st",IIf(Right([AgeEOY],1)=2,[AgeEOY]=[AgeEOY] & "nd",IIf(Right([AgeEOY],1)=3,[AgeEOY]=[AgeEOY] & "rd",[AgeEOY]=[AgeEOY] & "th")))

and all I'm getting is "0" as a result

	Adam Schwanz @Reply 3 years ago
You've made it a string, Try putting quotations around the string values. IIf(Right([AgeEOY],1)="1"

	Adam Schwanz @Reply 3 years ago
Actually your changing AGEEOY value too instead of just making the result IIf(Right([AgeEOY],1)="1",[AgeEOY] & "st",IIf

	Alex Hedley @Reply 3 years ago
Helper function `myOrdinal` on Row Number in a Query

Jerry Fowler OP @Reply

3 years ago

Adam your 2nd (no pun intended) option worked like a charm thanks. So was it that I was changing [AgeEOY] each time?

Also Alex are you talking about using a Function like:

Function myOrdinal (D as Integer) as String

    Dim Suffix as String

    Select Case D

        Case 1, 21, 31: Suffix = "st"

        Case 2, 22: Suffix = "nd"

        Case 3, 23: Suffix = "rd"

        Case Else: Suffix = "th"

    End Select

    myOrdinal = D & Suffix

End Function

Thanks to both of you

Jerry Fowler

	Sandra Truax @Reply 3 years ago
You can also do something similar in a Query with an IIF function: Suffix: IIf([D]=1 Or [D]=21 Or [D]=31,"st",IIf([D]=2 Or [d]=22,"nd",IIf([d]=3 Or [d]=33,"rd","th")))

	Richard Rost @Reply 3 years ago
Or use a switch function.

	Jerry Fowler OP @Reply 3 years ago
Was thinking today and also have to check for when a number ends in 11,12,13 as it then becomes a "th"

	Alex Hedley @Reply 3 years ago
That's covered in the else You could also use Currency to English in Lesson 3 of Access Check Register Seminar

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View