Combining Text and Date Values

[Guest]

I'm trying to follow the instructions below from a posting by Doug Steele on
1/25/2005. (Search For: Combining Text And Date Vaules). At the time, I
followed John's suggestion without reading the entire thread and I am just
now finding that this doesn't work in all cases.

I want to try Doug's suggestion and, I understand what I need to do as far
as changing the query, but I am not sure of where I need to be to "write my
own function". Can someone help?

Thanks.

------------------------
Doug's suggestion from posting follows:

You'll have to write your own function, along the lines of:

Function OrdinalDate(ValueIn As Long) As String

Select Case ValueIn
Case 1, 21, 31
OrdinalDate = ValueIn & "st"
Case 2, 22
OrdinalDate = ValueIn & "nd"
Case 3, 23
OrdinalDate = ValueIn & "rd"
Case Else
OrdinalDate = ValueIn & "th"
End Select

End Function

Change your query to

LegalDate: "This " & OrdinalDate(DatePart("d", [OrderDate])) & " day of " &
Format([OrderDate],"mmmm") & ", " & Format([OrderDate],"yyyy") & "."

 

[Douglas J. Steele]

1. Create a new module.

2. Copy the function code into the module. That's the following:

Function OrdinalDate(ValueIn As Long) As String

Select Case ValueIn
Case 1, 21, 31
OrdinalDate = ValueIn & "st"
Case 2, 22
OrdinalDate = ValueIn & "nd"
Case 3, 23
OrdinalDate = ValueIn & "rd"
Case Else
OrdinalDate = ValueIn & "th"
End Select

End Function

3. Save the module (say, as mdlOrginalDate). The name of the module isn't
critical, as long as it's not the same as any of the functions or subs in
your database.

4. Change the query as I indicated.

 

[Guest]

Thank you, Doug! I created the module, changed the query, and everything is
working properly now.

1. Create a new module.

2. Copy the function code into the module. That's the following:

Function OrdinalDate(ValueIn As Long) As String

Select Case ValueIn
Case 1, 21, 31
OrdinalDate = ValueIn & "st"
Case 2, 22
OrdinalDate = ValueIn & "nd"
Case 3, 23
OrdinalDate = ValueIn & "rd"
Case Else
OrdinalDate = ValueIn & "th"
End Select

End Function

3. Save the module (say, as mdlOrginalDate). The name of the module isn't
critical, as long as it's not the same as any of the functions or subs in
your database.

4. Change the query as I indicated.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to follow the instructions below from a posting by Doug Steele
on
1/25/2005. (Search For: Combining Text And Date Vaules). At the time, I
followed John's suggestion without reading the entire thread and I am just
now finding that this doesn't work in all cases.

I want to try Doug's suggestion and, I understand what I need to do as far
as changing the query, but I am not sure of where I need to be to "write
my
own function". Can someone help?

Thanks.

------------------------
Doug's suggestion from posting follows:

You'll have to write your own function, along the lines of:

Function OrdinalDate(ValueIn As Long) As String

Select Case ValueIn
Case 1, 21, 31
OrdinalDate = ValueIn & "st"
Case 2, 22
OrdinalDate = ValueIn & "nd"
Case 3, 23
OrdinalDate = ValueIn & "rd"
Case Else
OrdinalDate = ValueIn & "th"
End Select

End Function

Change your query to

LegalDate: "This " & OrdinalDate(DatePart("d", [OrderDate])) & " day of "
&
Format([OrderDate],"mmmm") & ", " & Format([OrderDate],"yyyy") & "."