=IIf(IsNull([nCreateDate]),"",[nCreateDate] & " at " & [nCreateTime])
Is there a way to cause the date in that expression to show like the
following: ?
Monday, November 24th, 2008 ?
Thanks in advance for your help!
magmike
Oops! I typed too soon. I found the answer:
=IIf(IsNull([nCreateDate]),"",Format([nCreateDate],"dddd, mmmm d,
yyyy") & " at " & [nCreateTime])
magmike
Well,magmike, you better look again.
You asked to display the date with an ordinal ending, i.e.
Monday, November 24th, 2008
What in your expression above will display the day as the 24th (or
3rd, or 2nd, or 1st, etc)? I don't see anything.
If you're content with Monday, November 24, 2008, I am.
If you want an answer to your original message, post back.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -
- Show quoted text -
Yep, you are right. How would I get the "th" on the end of the date?
You can adapt this multi-format code to whatever format you wish.
Paste the below function into a module:
Comment out the inappropriate part of the code, and remove the comment
from the way you wish to display the data.
As written, below, it will display a date as
Tuesday, November 25th, 2008
Watch out for word wrap on the longer lines.
Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. Novermber 13th, 2000
' MoIn determines Month Format, i.e. "mmm" for "Feb" or "mmmm" for
"February"
If IsNull(DateIn) Then
DateOrdinalEnding = ""
Exit Function
End If
Dim dteX As String
dteX = DatePart("d", DateIn)
dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")
' November 13th, 2000
' DateOrdinalEnding = Format(DateIn, MoIn) & " " & dteX & ", " &
Format(DateIn, "yyyy")
' **********************
' Tuesday, November 25th, 2008
DateOrdinalEnding = Format(DateIn, "dddd, " & MoIn) & " " & dteX & ",
" & Format(DateIn, "yyyy")
' **********************
' 17th day of September 2003'
'DateOrdinalEnding = dteX & " day of " & Format(DateIn, " " & MoIn & "
yyyy")
'***********************
' Friday 4th of July, 2008
' DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " of " &
Format(DateIn, " " & MoIn & ", yyyy")
' **********************
' Friday 4th July, 2008
' DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " " &
Format(DateIn, " " & MoIn & ", yyyy")
' **********************
' "Day of MONTH, Year" 4th of JULY, 2008
' DateOrdinalEnding = dteX & " of " & UCase(Format(DateIn, " " & MoIn
& ", yyyy"))
End Function
____________________
Then call it from a query:
NewDate
ateOrdinalEnding([DateField],"mmmm")
or from an unbound text control on a form or report:
=DateOrdinalEnding([DateField],"mmmm")
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -
- Show quoted text -
