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

Rabu, 30 Januari 2013

RE: [MS_AccessPros] Formatting a new date field

Robin-

Sorry, missing a close paren:

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

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)

From: Robin Chapple [mailto:robinski@mymail.net.au]
Sent: Wednesday, January 30, 2013 11:48 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Formatting a new date field

Thanks John,

I am really out of my depth with this. Some of the text is shown in red and
I know that means that there is a problem. I don't know where to look.

I have added some screen captures to our web site.

Please click here. http://www.rotary9790.org.au/test/test.asp>

Many thanks,

Robin Chapple

At 30/01/2013 06:56 PM, you wrote:

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

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

Yahoo! Groups Links

[Non-text portions of this message have been removed]