Date formating

  • Thread starter Thread starter BW
  • Start date Start date
B

BW

I need to convert a date into a string containing the following format. 14th
day of November, 2005. Is anyone aware of a function already written to
accomplish this?


Thanks
Brent
 
BW said:
I need to convert a date into a string containing the following format. 14th
day of November, 2005. Is anyone aware of a function already written to
accomplish this?
Click to expand...


Here's a way to get that result:

=NumberOrdinal(Day(datefield) & " day of " &
Format(datefield, "mmmm, yyyy")

Here's the code for the function:

Public Function NumberOrdinal(ByVal intValue As Long) As
String
If (intValue Mod 100) \ 10 = 1 Then
NumberOrdinal = intValue & "th"
Else
Select Case intValue Mod 10
Case 1
NumberOrdinal = intValue & "st"
Case 2
NumberOrdinal = intValue & "nd"
Case 3
NumberOrdinal = intValue & "rd"
Case 4, 5, 6, 7, 8, 9, 0
NumberOrdinal = intValue & "th"
End Select
End If
End Function
 
Works great!

Thanks for all your help.

Marshall Barton said:
Here's a way to get that result:

=NumberOrdinal(Day(datefield) & " day of " &
Format(datefield, "mmmm, yyyy")

Here's the code for the function:

Public Function NumberOrdinal(ByVal intValue As Long) As
String
If (intValue Mod 100) \ 10 = 1 Then
NumberOrdinal = intValue & "th"
Else
Select Case intValue Mod 10
Case 1
NumberOrdinal = intValue & "st"
Case 2
NumberOrdinal = intValue & "nd"
Case 3
NumberOrdinal = intValue & "rd"
Case 4, 5, 6, 7, 8, 9, 0
NumberOrdinal = intValue & "th"
End Select
End If
End Function
Click to expand...
 
Marshal,
pardon the stupidity but where are these elements applied? I have formatted
a report that will print out a training certificate and need the date
formatted as the original poster. The report get's its data from a query and
the field TrainingDate which is formatted as 99/99/0000;0;_

I am assuming the =NumberOrdinal piece is attached to the field generating
the output.
 
RoadKyng said:
pardon the stupidity but where are these elements applied? I have formatted
a report that will print out a training certificate and need the date
formatted as the original poster. The report get's its data from a query and
the field TrainingDate which is formatted as 99/99/0000;0;_

I am assuming the =NumberOrdinal piece is attached to the field generating
the output.
Click to expand...


The function goes into a standard module and the expression
goes in a report text box. Just change the name of the
field to TrainingDate and make sure the text box has a
different name.

I am not sure what you are referring to by the word "Field".
Fields are columns in a table or query. the thingies you
use on a form or report to display values are called
"Controls" (a text box is a type of control).

Also your "format" is an input mask. A date format is
applied when the value is displayed in a text box (as long
as the field is a Date type and not a Text type).
 
Thanks for the reply.

I am jsut starting to get into the use of modules and public functions. I
need to research on how to call a public function or module from a private
function.
I assume a public function or module is one that can be used by all private
functions by calling and assigning the variables used in the public
function/module. (confused yet?)

I tried it on a form and could see the code in the private function
assigning the variable I had set up in a public module with the correct
value. But when I tried to call this value later in another private function
it gave me a null value.

I still have alot to learn and very much appreciate the time you and the
other MVP's here give to us amatures.
 
RoadKyng said:
I am jsut starting to get into the use of modules and public functions. I
need to research on how to call a public function or module from a private
function.
I assume a public function or module is one that can be used by all private
functions by calling and assigning the variables used in the public
function/module. (confused yet?)

I tried it on a form and could see the code in the private function
assigning the variable I had set up in a public module with the correct
value. But when I tried to call this value later in another private function
it gave me a null value.

I still have alot to learn and very much appreciate the time you and the
other MVP's here give to us amatures.
Click to expand...


I'm not sure if there was a question in there. About all I
can say at this point is that most procedures in standard
modules are public. Public Functions can be called from
just about anywhere in Access (including queries and text
box expressions). Public Subs can be only be called from
VBA procedures.

By default, procedures (both Functions and Subs) in Class
modules (including form and report modules) are private.
Private functions can only be called from within the same
module. Note that Public procedures in a class module are
methods (something that you are unlikly to use without a lot
more experience) of the class.

For both functions and subs, you should normally provide
values to the procedure by using arguments, not by setting
global variables.

If you have a specific question, post back with more details
about what you are trying to do.
 
Back
Top