Format date as "d mmm"

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a birthday list which I need to publish without the birth year
displayed.

How do I show 1st Jan 1970 as 1st Jan?

Thanks,

Robin Chapple
 
your subject line shows the correct formatting syntax. presumably you
wouldn't have posted a question unless that format was not returning the
value the way you want to see it, so suggest you explain what you've tried,
and where you tried it, and what return you *are* getting, so we can help
you troubleshoot.

hth
 
I have a birthday list which I need to publish without the birth year
displayed.

How do I show 1st Jan 1970 as 1st Jan?

Thanks,

Robin Chapple
Click to expand...

Do you mean display it as 1 Jan or literally as 1st Jan?
 
The requirement is to sort a birthday list on month and day so that
the month is ignored. I have achieved that this way:

DayNo: DatePart("d",[Birthday])

MonthNo: DatePart("m",[Birthday])

I then sort on MonthNo and DayNo. These fields are not displayed.

I then need a field that shows the birthday without the year.

I have tried this:

Event: DatePart("d mmm",[Birthday])

which gives #error

Thanks for your interest.

Robin Chapple
 
ok. from your example, it appears you're creating a calculated field in a
query. try the following, as

Event: Format([Birthday], "d mmm")

btw, rather than creating a Month field and a Day field in the query to sort
on, you can get the same month/day sort with the following calculated field,
as

SortBy: Format([Birthday],"mmdd")

hth


Robin Chapple said:
The requirement is to sort a birthday list on month and day so that
the month is ignored. I have achieved that this way:

DayNo: DatePart("d",[Birthday])

MonthNo: DatePart("m",[Birthday])

I then sort on MonthNo and DayNo. These fields are not displayed.

I then need a field that shows the birthday without the year.

I have tried this:

Event: DatePart("d mmm",[Birthday])

which gives #error

Thanks for your interest.

Robin Chapple


your subject line shows the correct formatting syntax. presumably you
wouldn't have posted a question unless that format was not returning the
value the way you want to see it, so suggest you explain what you've tried,
and where you tried it, and what return you *are* getting, so we can help
you troubleshoot.

hth
Click to expand...
Click to expand...
 
1st Jan preferred but 1 Jan acceptable
Click to expand...

To display the date of birth as 1st Jan:

Copy and Paste the below function into a new module.

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date, i.e. 13th Jan, 2nd Feb, etc.
' 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")

DateOrdinalEnding = dteX & Format(DateIn, " " & MoIn)
End Function
==================

You can call it from a query:
BirthdayOn:DateOrdinalEnding([DateOfBirth],"mmm")

or directly in a report or on a form, using an unbound text control:
=DateOrdinalEnding([DateOfBirth],"mmm")
 
Thanks Tina,

That was what the doctor ordered and will be useful for other
applications.

Grateful thanks,

Robin Chapple

ok. from your example, it appears you're creating a calculated field in a
query. try the following, as

Event: Format([Birthday], "d mmm")

btw, rather than creating a Month field and a Day field in the query to sort
on, you can get the same month/day sort with the following calculated field,
as

SortBy: Format([Birthday],"mmdd")

hth


Robin Chapple said:
The requirement is to sort a birthday list on month and day so that
the month is ignored. I have achieved that this way:

DayNo: DatePart("d",[Birthday])

MonthNo: DatePart("m",[Birthday])

I then sort on MonthNo and DayNo. These fields are not displayed.

I then need a field that shows the birthday without the year.

I have tried this:

Event: DatePart("d mmm",[Birthday])

which gives #error

Thanks for your interest.

Robin Chapple


your subject line shows the correct formatting syntax. presumably you
wouldn't have posted a question unless that format was not returning the
value the way you want to see it, so suggest you explain what you've tried,
and where you tried it, and what return you *are* getting, so we can help
you troubleshoot.

hth


I have a birthday list which I need to publish without the birth year
displayed.

How do I show 1st Jan 1970 as 1st Jan?

Thanks,

Robin Chapple
Click to expand...
Click to expand...
Click to expand...
 
Fred,

I like the opportunity that this provides if I get it right. I've done
something wrong.

I think that I have followed your instructions with this result:

http://www.rotary9790.org.au/test/datefunction.html

I really would like this to work.

AND BTW the application locked and I had to use the Task manager to
exit.

Thanks,

Robin

1st Jan preferred but 1 Jan acceptable
Click to expand...

To display the date of birth as 1st Jan:

Copy and Paste the below function into a new module.

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date, i.e. 13th Jan, 2nd Feb, etc.
' 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")

DateOrdinalEnding = dteX & Format(DateIn, " " & MoIn)
End Function
==================

You can call it from a query:
BirthdayOn:DateOrdinalEnding([DateOfBirth],"mmm")

or directly in a report or on a form, using an unbound text control:
=DateOrdinalEnding([DateOfBirth],"mmm")
Click to expand...
 
The requirement is to sort a birthday list on month and day so that
the month is ignored. I have achieved that this way:

DayNo: DatePart("d",[Birthday])

MonthNo: DatePart("m",[Birthday])

I then sort on MonthNo and DayNo. These fields are not displayed.
Click to expand...

You can get both constraints met by using one calculated field:

HappyHappy: DateSerial(Year(Date()), Month([Birthday]),
Day([Birthday]))

Sort it directly (it will sort chronologically by birthday
anniversary), and set its Format property to "d mmm".

You can't easily get 1st, 2nd, 3rd etc. without a fair bit of VBA code
- there's no simple Format setting to do so.

John W. Vinson[MVP]
 
Fred,

I like the opportunity that this provides if I get it right. I've done
something wrong.

I think that I have followed your instructions with this result:

http://www.rotary9790.org.au/test/datefunction.html

I really would like this to work.

AND BTW the application locked and I had to use the Task manager to
exit.

Thanks,

Robin

1st Jan preferred but 1 Jan acceptable

On Sat, 05 Aug 2006 13:49:13 +1000, Robin Chapple wrote:

I have a birthday list which I need to publish without the birth year
displayed.

How do I show 1st Jan 1970 as 1st Jan?

Thanks,

Robin Chapple

Do you mean display it as 1 Jan or literally as 1st Jan?
Click to expand...

To display the date of birth as 1st Jan:

Copy and Paste the below function into a new module.

Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date, i.e. 13th Jan, 2nd Feb, etc.
' 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")

DateOrdinalEnding = dteX & Format(DateIn, " " & MoIn)
End Function
==================

You can call it from a query:
BirthdayOn:DateOrdinalEnding([DateOfBirth],"mmm")

or directly in a report or on a form, using an unbound text control:
=DateOrdinalEnding([DateOfBirth],"mmm")
Click to expand...
Click to expand...

The highlighted line that begins with
dteX = dteX & Nz(Choose ... etc. and the next line
Abs(dteX)) Mod ... etc. should be all on one long line.

Email messages are usually wrapped at about 70 or so characters, so
you always need to be careful when copying and pasting.
 
you're welcome :)


Robin Chapple said:
Thanks Tina,

That was what the doctor ordered and will be useful for other
applications.

Grateful thanks,

Robin Chapple

ok. from your example, it appears you're creating a calculated field in a
query. try the following, as

Event: Format([Birthday], "d mmm")

btw, rather than creating a Month field and a Day field in the query to sort
on, you can get the same month/day sort with the following calculated field,
as

SortBy: Format([Birthday],"mmdd")

hth


Robin Chapple said:
The requirement is to sort a birthday list on month and day so that
the month is ignored. I have achieved that this way:

DayNo: DatePart("d",[Birthday])

MonthNo: DatePart("m",[Birthday])

I then sort on MonthNo and DayNo. These fields are not displayed.

I then need a field that shows the birthday without the year.

I have tried this:

Event: DatePart("d mmm",[Birthday])

which gives #error

Thanks for your interest.

Robin Chapple



your subject line shows the correct formatting syntax. presumably you
wouldn't have posted a question unless that format was not returning the
value the way you want to see it, so suggest you explain what you've tried,
and where you tried it, and what return you *are* getting, so we can help
you troubleshoot.

hth


I have a birthday list which I need to publish without the birth year
displayed.

How do I show 1st Jan 1970 as 1st Jan?

Thanks,

Robin Chapple
Click to expand...
Click to expand...
Click to expand...
 
Thanks John,

This minor exercise has become very interesting.

Robin

The requirement is to sort a birthday list on month and day so that
the month is ignored. I have achieved that this way:

DayNo: DatePart("d",[Birthday])

MonthNo: DatePart("m",[Birthday])

I then sort on MonthNo and DayNo. These fields are not displayed.
Click to expand...

You can get both constraints met by using one calculated field:

HappyHappy: DateSerial(Year(Date()), Month([Birthday]),
Day([Birthday]))

Sort it directly (it will sort chronologically by birthday
anniversary), and set its Format property to "d mmm".

You can't easily get 1st, 2nd, 3rd etc. without a fair bit of VBA code
- there's no simple Format setting to do so.

John W. Vinson[MVP]
Click to expand...
 
Back
Top