Format date as "d mmm" Robin Chapple <robinski[ at ]westnet.com.au> 05.08.2006 03:49:13 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

Re: Format date as "d mmm" "tina" <nospam[ at ]address.com> 05.08.2006 04:35:53 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 "Robin Chapple" <robinski[ at ]westnet.com.au> wrote in message news:l358d2pebbejj3oe38agaiiftqbtp5nesa[ at ]4ax.com... [Quoted Text] > 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

Re: Format date as "d mmm" fredg <fgutkind[ at ]example.invalid> 05.08.2006 05:06:34 On Sat, 05 Aug 2006 13:49:13 +1000, Robin Chapple wrote: [Quoted Text] > 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? -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail

Re: Format date as "d mmm" Robin Chapple <robinski[ at ]westnet.com.au> 05.08.2006 06:35:58 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 On Sat, 05 Aug 2006 04:35:53 GMT, "tina" <nospam[ at ]address.com> wrote: [Quoted Text] >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 > > >"Robin Chapple" <robinski[ at ]westnet.com.au> wrote in message >news:l358d2pebbejj3oe38agaiiftqbtp5nesa[ at ]4ax.com... >> 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 >

Re: Format date as "d mmm" Robin Chapple <robinski[ at ]westnet.com.au> 05.08.2006 11:22:58 1st Jan preferred but 1 Jan acceptable On Fri, 4 Aug 2006 22:06:34 -0700, fredg <fgutkind[ at ]example.invalid> wrote: [Quoted Text] >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?

Re: Format date as "d mmm" "tina" <nospam[ at ]address.com> 05.08.2006 14:56:09 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" <robinski[ at ]westnet.com.au> wrote in message news:eke8d2lbht5i9n39h1lsfpivk85lf45qbj[ at ]4ax.com... [Quoted Text] > 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 > > > On Sat, 05 Aug 2006 04:35:53 GMT, "tina" <nospam[ at ]address.com> wrote: > > >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 > > > > > >"Robin Chapple" <robinski[ at ]westnet.com.au> wrote in message > >news:l358d2pebbejj3oe38agaiiftqbtp5nesa[ at ]4ax.com... > >> 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 > > >

Re: Format date as "d mmm" fredg <fgutkind[ at ]example.invalid> 05.08.2006 17:52:23 On Sat, 05 Aug 2006 21:22:58 +1000, Robin Chapple wrote: [Quoted Text] > 1st Jan preferred but 1 Jan acceptable > > On Fri, 4 Aug 2006 22:06:34 -0700, fredg <fgutkind[ at ]example.invalid> > wrote: > >>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? 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") -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail

Re: Format date as "d mmm" Robin Chapple <robinski[ at ]westnet.com.au> 05.08.2006 20:58:10 Thanks Tina, That was what the doctor ordered and will be useful for other applications. Grateful thanks, Robin Chapple On Sat, 05 Aug 2006 14:56:09 GMT, "tina" <nospam[ at ]address.com> wrote: [Quoted Text] >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" <robinski[ at ]westnet.com.au> wrote in message >news:eke8d2lbht5i9n39h1lsfpivk85lf45qbj[ at ]4ax.com... >> 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 >> >> >> On Sat, 05 Aug 2006 04:35:53 GMT, "tina" <nospam[ at ]address.com> wrote: >> >> >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 >> > >> > >> >"Robin Chapple" <robinski[ at ]westnet.com.au> wrote in message >> >news:l358d2pebbejj3oe38agaiiftqbtp5nesa[ at ]4ax.com... >> >> 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 >> > >> >

Re: Format date as "d mmm" Robin Chapple <robinski[ at ]westnet.com.au> 05.08.2006 21:25:54 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 On Sat, 5 Aug 2006 10:52:23 -0700, fredg <fgutkind[ at ]example.invalid> wrote: [Quoted Text] >On Sat, 05 Aug 2006 21:22:58 +1000, Robin Chapple wrote: > >> 1st Jan preferred but 1 Jan acceptable >> >> On Fri, 4 Aug 2006 22:06:34 -0700, fredg <fgutkind[ at ]example.invalid> >> wrote: >> >>>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? > >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")

Re: Format date as "d mmm" John Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 05.08.2006 22:47:05 On Sat, 05 Aug 2006 16:35:58 +1000, Robin Chapple <robinski[ at ]westnet.com.au> wrote: [Quoted Text] >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. 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]

Re: Format date as "d mmm" fredg <fgutkind[ at ]example.invalid> 05.08.2006 22:51:28 On Sun, 06 Aug 2006 07:25:54 +1000, Robin Chapple wrote: [Quoted Text] > 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 > > On Sat, 5 Aug 2006 10:52:23 -0700, fredg <fgutkind[ at ]example.invalid> > wrote: > >>On Sat, 05 Aug 2006 21:22:58 +1000, Robin Chapple wrote: >> >>> 1st Jan preferred but 1 Jan acceptable >>> >>> On Fri, 4 Aug 2006 22:06:34 -0700, fredg <fgutkind[ at ]example.invalid> >>> wrote: >>> >>>>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? >> >>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") 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail

Re: Format date as "d mmm" "tina" <nospam[ at ]address.com> 06.08.2006 00:29:48 you're welcome :) "Robin Chapple" <robinski[ at ]westnet.com.au> wrote in message news:gg1ad2dl3lcbs9bmjm3ecl7pjotrjmbpul[ at ]4ax.com... [Quoted Text] > Thanks Tina, > > That was what the doctor ordered and will be useful for other > applications. > > Grateful thanks, > > Robin Chapple > > On Sat, 05 Aug 2006 14:56:09 GMT, "tina" <nospam[ at ]address.com> wrote: > > >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" <robinski[ at ]westnet.com.au> wrote in message > >news:eke8d2lbht5i9n39h1lsfpivk85lf45qbj[ at ]4ax.com... > >> 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 > >> > >> > >> On Sat, 05 Aug 2006 04:35:53 GMT, "tina" <nospam[ at ]address.com> wrote: > >> > >> >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 > >> > > >> > > >> >"Robin Chapple" <robinski[ at ]westnet.com.au> wrote in message > >> >news:l358d2pebbejj3oe38agaiiftqbtp5nesa[ at ]4ax.com... > >> >> 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 > >> > > >> > > >

Re: Format date as "d mmm" Robin Chapple <robinski[ at ]westnet.com.au> 06.08.2006 03:31:20 Thanks John, This minor exercise has become very interesting. Robin On Sat, 05 Aug 2006 16:47:05 -0600, John Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> wrote: [Quoted Text] >On Sat, 05 Aug 2006 16:35:58 +1000, Robin Chapple ><robinski[ at ]westnet.com.au> wrote: > >>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. > >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]