Date Formula

[dzabor]

I need a formula to have an anniversay date returned basing it on a [date hired] field. Can you help me?

dzabor

 

[Mile-O]

The DateAdd() function is what you want.

 

[dzabor]

Date Formula needed?

How would the DateAdd field return a date of an anniversay? I need a date field with the current mm/dd/yyyy anniversay in it.

 

[Mile-O]

As an anniversary is a yearly event, the DateAdd() function would give you the 'anniversary' of any year


i.e.if Date Hired was today- 02-Jun-2003 then by using the function

=DateAdd("yyyy", 1, [Date Hired]) would return 02-Jun-2004

Of course, if this is not what you mean, more information than the minimal information you've supplied is going to be necessary.

 

[dzabor]

That is EXACTLY what I needed - Thanks!

 

[raskew]

Hmm...

How about if you have a table full of hire dates, and not all of them are in 2003?

A more generic approach might be:

Public Function Anniversary(ByVal pDate As Date, Optional pNextOne As Boolean = False) As Date
'*******************************************
'Name:      Anniversary (Function)
'Purpose:   Return this years' or the next
'           anniversary of an input date
'Source:    [url]http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48683[/url]
'Author:    raskew
'Goal:      Six or less lines of active code
'Inputs:    From debug (immediate) window:
'           Note: Today's date = 02-Jun-2003
'           HireDate = #4/23/99#
'           (1) ? Anniversary(HireDate, True)
'           (2) ? Anniversary(HireDate, False)
'           (3) ? Anniversary(HireDate)
'           HireDate = #8/23/99#
'           (4) ? Anniversary(HireDate, True)
'           (5) ? Anniversary(HireDate, False)
'           (6) ? Anniversary(HireDate)
'
'Output:    (1) 4/23/04
'           (2) 4/23/03
'           (3) 4/23/03
'           (4) 8/23/03
'           (5) 8/23/03
'           (6) 8/23/03
'*******************************************

Dim myDate As Date

myDate = pDate

'this line provides the anniversary date occuring this year
myDate = DateSerial(Year(Date), Month(myDate), day(myDate))

'this line allows the user to specify the next scheduled anniversary
'if this years' anniversary has already past
'note that the second and third options come into play only if pNextOne = True
myDate = Switch(pNextOne = False, myDate, myDate < Date, DateAdd("yyyy", 1, myDate), True, myDate)

Anniversary = myDate

End Function

 

[Peterson]

I tried =DateAdd("yyyy", 1, [Hire Date]) I added a bogus recorded 09/17/2007 to the field Hire Date in my table and the query shows nothing????

 

[raskew]

Would you please post your query SQL.

Bob

 

[Peterson]

I'm sorry, I have know idea what your talking about or how to do it.

 

[raskew]

Bob Peterson -

To post your query SQL, open your query in design view, click on the View icon (probably the far left in the toolbar) and select SQL view. That's your query SQL. Highlight it, then copy (Ctl-C).

Return to this forum, start a new post and then paste the query SQl (Ctl-V).

Seeing that you are posting in two different threads, I'm not sure where you are hung-up as stated in your private message. Please respond to the forum, not via PM.

Bob

 

[Peterson]

SELECT [Employee Master File].[Last Name], [Employee Master File].[First Name], [Employee Master File].[Employee #], [Employee Master File].[Hire Date], [Employee Master File].Status
FROM [Employee Master File]
WHERE (((Date() = DateAdd("m",11,[Employee Master File].[Hire Date])) AND (([Employee Master File].Status)="A"));

I now get Syntax error in query expression with this.

 

[Brianwarnock]

SELECT [Employee Master File].[Last Name], [Employee Master File].[First Name], [Employee Master File].[Employee #], [Employee Master File].[Hire Date], [Employee Master File].Status
FROM [Employee Master File]
WHERE (((Date() = DateAdd("m",11,[Employee Master File].[Hire Date])) AND (([Employee Master File].Status)="A"));

I now get Syntax error in query expression with this.

You have posted this on 2 threads that is very bad manners as people can waste their time and effort. I asked you to mark this thread as closed as you had opened another. I now wish that I had not bothered to respond

Brian

 

[Peterson]

Sorry, didn't know, as soon as I figure out how to close this I will.

 

[Brianwarnock]

Sorry, didn't know, as soon as I figure out how to close this I will.

In a post just say that you have opened another thread and people should not reply here.


Here is the other thread.

OK that should do it.

Cheers
Brian