If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Format Date in Report
I have a report (Certificate) which I would like the date to convert from say
2.2.10 to 2nd February 2010. The Field on the Report is [Date of Request] Could someone kindly reply with the correct syntax to include the formatting in this text Box? Also, if say the date is 3.2.10. can this be converted to 3rd February 2010, ie can you tell Access to record 2nd, 3rd etc? Many thanks for any help |
#2
|
|||
|
|||
Format Date in Report
Access doesn't have the ability to put the ordinal suffixes onto numbers.
You'll have to write your own function to do that, something like: Function FormatOrdinalDate(InputValue As Variant) As String Dim strDay As String If IsDate(InputValue) Then Select Case Day(InputValue) Case 1, 21, 31 strDay = Day(InputValue) & "st" Case 2, 22 strDay = Day(InputValue) & "nd" Case 3, 23 strDay = Day(InputValue) & "rd" Case Else strDay = Day(InputValue) & "th" End Select FormatOrdinalDate = strDay & " " & _ Format(InputValue, "mmmm yyyy") End If End Function -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Roger Bell" wrote in message ... I have a report (Certificate) which I would like the date to convert from say 2.2.10 to 2nd February 2010. The Field on the Report is [Date of Request] Could someone kindly reply with the correct syntax to include the formatting in this text Box? Also, if say the date is 3.2.10. can this be converted to 3rd February 2010, ie can you tell Access to record 2nd, 3rd etc? Many thanks for any help |
Thread Tools | |
Display Modes | |
|
|