ACCESS FEED: RE: [MS_AccessPros] Formatting a new date field

Selasa, 29 Januari 2013

RE: [MS_AccessPros] Formatting a new date field

Robin-

You could do it in a complex expression, but it would be easier to write a
little function.

Public Function NextAnniversary(datChartered As Date) As Date
Dim intMonth As Integer, intDay As Integer, intYear As Integer

intMonth = Month(datChartered)
intDay = Day(datChartered)
intYear = Year(Date)

If (intMonth > Month(Date)) Or _
((intMonth = Month(Date)) And (intDay > Day(Date)) Then
intYear = intYear + 1
End If

NextAnniversary = DateSerial(intYear, intMonth, intDay)

End Function

Call that from a query on your table like this:

Anny: NextAnniversary([DateChartered])

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Wednesday, January 30, 2013 1:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Formatting a new date field

That should be:

""" From a field that shows 27/3/1927 how do I get a field that shows
27/3/2013? """

Of course!

At 30/01/2013 11:23 AM, you wrote:
>I have a table where the date that a club was chartered is stored in a
>field called "DateChartered" by some stroke of genius!
>
>I am preparing a list for this year that will record that on the
>"CharteredDate" day and month but this calendar year in date format,
>because we will sort on that field. A separate field will record that
>the date is the anniversary.
>
> From a field that shows 27/3/1927 how do I get a field that shows
27/3/3013?
>
>Many thanks,
>
>Robin Chapple
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

------------------------------------

Yahoo! Groups Links