How to compare ddmm of date with today's ddmm?

[thirtydash9]

I have been searching through the posts; apologies if this is already out there.

I have a database which calculates continuing education credits earned following passage of an exam.

People are required to earn x credits per year, based on the anniversary of passing the exam (gradDate).

Each time someone earns credits, I will write to them and tell them how many more credits they need to earn by the next anniversary date (the next anniversary of gradDate).

To do this, I need to compare the day and month of gradDate with today's date and month, in order to know whether the next anniversary is this year or next. If this involves converting the value to a numerical format, I then need to convert it back to date format and add the appropriate yyyy value.

Any ideas?

Thanks!!!

 

[misscrf]

First off, I need to know the format of your field in the table. (If there is no format it shouldn't matter, but if there is and we are just formatting it differently on a report or form, we need to calculate based on the table format, not the end format.)

Next, are you calculating this in the "build" for a form control/report control, or in vb?

The formula will adjust a bit.

Here is what I came up with. You may need to mess with it a bit. I am using vb as my language:

IF DateDiff("yyyy",date,[yourdatefield]) = 1 Then
'Yes response or mark/checkbox - whatever you want here
Else
'No - next year.
end if

Understand that this only bring up someone that falls within 1 year of this calculation: today - yourdate will have to equal 1 year (anything under or below that, I don't think will be caught.)

Let me know if I have helped or just confused you further!




misscrf

It is never too late to become what you could have been ~ George Eliot

 

[thirtydash9]

misscrf, Thanks for your reply.

Actually, I realized that I was asking the wrong question.

What I was trying to do was figure out the next anniversary date of gradDate. Silly me was trying to strip the mm and dd values from gradDate and do it that way.

I was able to solve it much more simply by creating a "do..while..loop" statement and attaching it to the On Format event of the detail section of the report.

For anyone interested, here it is:
-------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim dteGradDate As Date
Dim dteGradDatePlusThreeYrs As Date

dteGradDate = Me.txtGradDate
dteNextAnnivDate = DateAdd("yyyy", 1, dteGradDate)

Do While dteNextAnnivDate < date
dteNextAnnivDate = DateAdd("yyyy", 1, dteNextAnnivDate)
Loop

Me.txtNextAnnivDate = dteNextAnnivDate

End Sub
---------------------

Thanks again for your help.

fiddlr

 

[thirtydash9]

oops, I was a bit hasty hitting the submit button.
i messed up my variables.
here's the code again.
-----------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim dteGradDate As Date
Dim dteNextAnnivDate As Date

dteGradDate = Me.txtGradDate
dteNextAnnivDate = DateAdd("yyyy", 1, dteGradDate)


Do While dteNextAnnivDate < date
dteNextAnnivDate = DateAdd("yyyy", 1, dteNextAnnivDate)
Loop

Me.txtNextAnnivDate = dteNextAnnivDate

End Sub

 

[misscrf]

Excellent. Glad you solved it.

misscrf

It is never too late to become what you could have been ~ George Eliot