Change date in Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like one field in my report to read 1st day of June 2007 or whatever
the date is, the date stored in the table and query is 1 June 2007. How can
I get these dates to change automatically in the report? Thank you so much
for yur help.
 
I would like one field in my report to read 1st day of June 2007 or whatever
the date is, the date stored in the table and query is 1 June 2007. How can
I get these dates to change automatically in the report? Thank you so much
for yur help.
Click to expand...

Copy and Paste this function into a new module:


Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. 30th day of Novermber 2000
' MoIn determines Month Format, i.e. "mmm" for "Nov" or "mmmm" for
"November".

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")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")

End Function
==============

You can call it from an unbound text control on your report:
= DateOrdinalEnding([DateField],"mmmm")
 
Thank you for your quick response. The section of the code below shows in
red, won't step through.


dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")
Click to expand...


fredg said:
I would like one field in my report to read 1st day of June 2007 or whatever
the date is, the date stored in the table and query is 1 June 2007. How can
I get these dates to change automatically in the report? Thank you so much
for yur help.
Click to expand...

Copy and Paste this function into a new module:


Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. 30th day of Novermber 2000
' MoIn determines Month Format, i.e. "mmm" for "Nov" or "mmmm" for
"November".

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")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")

End Function
==============

You can call it from an unbound text control on your report:
= DateOrdinalEnding([DateField],"mmmm")
Click to expand...
 
Thank you for your quick response. The section of the code below shows in
red, won't step through.

dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")
Click to expand...

fredg said:
I would like one field in my report to read 1st day of June 2007 or whatever
the date is, the date stored in the table and query is 1 June 2007. How can
I get these dates to change automatically in the report? Thank you so much
for yur help.
Click to expand...

Copy and Paste this function into a new module:

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. 30th day of Novermber 2000
' MoIn determines Month Format, i.e. "mmm" for "Nov" or "mmmm" for
"November".

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")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")

End Function
==============

You can call it from an unbound text control on your report:
= DateOrdinalEnding([DateField],"mmmm")
Click to expand...
Click to expand...

When posting emails. long lines often get incorrectly wrapped.

These 2 lines should be all on one long line:
dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

These 2 lines should be on oneline:
DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")
 
Good Morning,
Sorry that I am so slow, but I have the code in the module and the field
in the report as an unbound text box. I placed
=DateOrdinalEnding([GraduationDate],"mmmm") in the control source and I am
still getting an error. An Enter Parameter Value box pops up asking for
DateOrdinalEnding. What am I missing?


fredg said:
Thank you for your quick response. The section of the code below shows in
red, won't step through.

dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")
Click to expand...

fredg said:
On Fri, 29 Jun 2007 06:56:03 -0700, born2prazeHim wrote:

I would like one field in my report to read 1st day of June 2007 or whatever
the date is, the date stored in the table and query is 1 June 2007. How can
I get these dates to change automatically in the report? Thank you so much
for yur help.

Copy and Paste this function into a new module:

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. 30th day of Novermber 2000
' MoIn determines Month Format, i.e. "mmm" for "Nov" or "mmmm" for
"November".

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")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")

End Function
==============

You can call it from an unbound text control on your report:
= DateOrdinalEnding([DateField],"mmmm")
Click to expand...
Click to expand...

When posting emails. long lines often get incorrectly wrapped.

These 2 lines should be all on one long line:
dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

These 2 lines should be on oneline:
DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")
Click to expand...
 
Good Morning,
Sorry that I am so slow, but I have the code in the module and the field
in the report as an unbound text box. I placed
=DateOrdinalEnding([GraduationDate],"mmmm") in the control source and I am
still getting an error. An Enter Parameter Value box pops up asking for
DateOrdinalEnding. What am I missing?


fredg said:
Thank you for your quick response. The section of the code below shows in
red, won't step through.

dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")

:

On Fri, 29 Jun 2007 06:56:03 -0700, born2prazeHim wrote:

I would like one field in my report to read 1st day of June 2007 or whatever
the date is, the date stored in the table and query is 1 June 2007. How can
I get these dates to change automatically in the report? Thank you so much
for yur help.

Copy and Paste this function into a new module:

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. 30th day of Novermber 2000
' MoIn determines Month Format, i.e. "mmm" for "Nov" or "mmmm" for
"November".

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")

DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")

End Function
==============

You can call it from an unbound text control on your report:
= DateOrdinalEnding([DateField],"mmmm")
Click to expand...

When posting emails. long lines often get incorrectly wrapped.

These 2 lines should be all on one long line:
dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")

These 2 lines should be on oneline:
DateOrdinalEnding = dteX & " day of" & Format(DateIn, " " & MoIn & "
yyyy")
Click to expand...
Click to expand...

I just re-tried that code in a report and it worked fine for me.

Things to look out for....

1) Is [GraduationDate] included in the report's record source?
Is it spelled correctly?

2) Try copying the DateOrdinalEnding function name directly from the
function in the module and pasting it into the control source of a NEW
unbound control, then fill in the field name and month format
arguments. (Sometimes doing this will be all you need do)

3) Make sure the name of the module is not exactly the same as the
name of the function (DateOrdinalEnding) within the module.

3) Did you correct any line wrap errors I mentioned in my previous
reply?

4) Compile the module. Does it compile OK or do you get an error.

5) Open the module and place a Breakpoint on the

Public Function DateOrdinalEnding( ... etc ...)

line.

When you run the report, and the code window opens, step through the
code, line by line. Let us know which line it errors on. You can hover
the cursor over each of the variables (DateIn, MoIn, etc,) and read
the value as you progress.

6) If you still have a problem, Copy and Paste into a reply the exact
code you have in the module, as well as the exact control source of
the control.
 
Back
Top