Correct code for challenging dates?

[thekruser]

What I have:
tblCourse
-CourseID (PK)
-FromDate (Course start date)
-ToDate (Course completion date)

What I am trying to do:
I have a need to compare dates to the current date and return a value based on that comparison. Here is what I am currently using (I know it is not the best way, just the only way I could figure it out at the moment):

Status: IIf(Format([tblCourse.ToDate],'yyyy')=Format(Now(),'yyyy'),'Current','Expired')

Can someone please help me figure out the correct way to do this? There are several different criteria I need to figure out in relation to the current date:

1. Is the completion date within the same Calendar Year


2. Is the completion date within the same Fiscal Year (1 Oct to 30 Sep)
3. Is the completion date within the current Calendar Year semi-annual period
4. Is the completion date within the current Fiscal Year semi-annual period
5. How many days since the course completed (for past courses)
6. How many days until the course completes (current/future courses)

The problem:
I know these series of codes are all going to be formatted similarly and once I know where to start I can probably figure it out...I just don't know where to begin.

Thank you in advance for your help. Have a great day!

[ssanfu]

Where/how are you trying to do this calculation? In a query, in code, .....

In your example, "Format" only affects the way something is *displayed*.
If I were comparing two dates (years), I would use:

Status: IIf(Year([tblCourse.ToDate]) = Year(Date()),'Current','Expired')

1. Is the completion date within the same Calendar Year
2. Is the completion date within the same Fiscal Year (1 Oct to 30 Sep)
3. Is the completion date within the current Calendar Year semi-annual period
4. Is the completion date within the current Fiscal Year semi-annual period
5. How many days since the course completed (for past courses)
6. How many days until the course completes (current/future courses)

What results do you want for each of these?

1) Current, Expired (see above)
2) yes/no?
3) yes/no?
4) yes/no?
5) number of days - use the DateDiff() function
6) number of days - use the DateDiff() function

.

[thekruser]

@ssanfu

Thanks for the reply. All of the code will wind up in a query that feeds a report.

The code you provided worked perfectly for what I was trying to accomplish in output 1.

For outputs 2 through 4, yes/no would work great. I can tweak it if I get to the point I need to.

The DateDiff() function you mentioned for output 5 is perfect, but I do not know how to code it properly. Can you give me a few pointers?

Thanks again for all of your help. I truly appreciate it.

[weekend00]

I suggest you create a few function to get the fiscal year, semi fiscal year, semi calendar year, to make your query clear and neat.

for example:

Function fiscalYear(ddate As Date) As Long
'fiscalyear(11/1/2010) = 2010,
'fiscalyear(06/1/2010) = 2009
    If Month(ddate) >= 10 Then
        fiscalYear = Year(ddate)
    Else
        fiscalYear = Year(ddate) - 1
    End If
End Function
Function fiscalSemiYear(ddate As Date) As Long
    If Month(ddate) < 4 Then
        fiscalSemiYear = Year(ddate) * 2 - 2
    ElseIf Month(ddate) >= 4 And Month(ddate) <= 9 Then
        fiscalSemiYear = Year(ddate) * 2 - 1
    Else
        fiscalSemiYear = Year(ddate) * 2
    End If
End Function
Function SemiYear(ddate As Date) As Long
'semiyear(11/1/2010) = 2010 * 2 + 1 = 4021
'semiyear(06/1/2010) = 2010 * 2 + 0 = 4020
    If Month(ddate) >= 7 Then
        SemiYear = Year(ddate) * 2 + 1
    Else
        SemiYear = Year(ddate) * 2
    End If
End Function

then in your query, you can use following expressions:

1. Is the completion date within the same Calendar Year                              
     year(todate)=year(date())
2. Is the completion date within the same Fiscal Year (1 Oct to 30 Sep)          
     fiscalYear(todate)=fiscalYear(date())
3. Is the completion date within the current Calendar Year semi-annual period 
     semiYear(todate)=semiYear(date())
4. Is the completion date within the current Fiscal Year semi-annual period      
     fiscalSemiYear(todate)=fiscalSemiYear(date())
5. How many days since the course completed (for past courses)                   
     datediff("y", todate, date())
6. How many days until the course completes (current/future courses)            
     datediff("y",date(), todate)

[thekruser]

@weekend00

That is amazing! I don't understand the majority of it, however. How would one incorporate quarters (fiscal and annual) into this? I didn't list that earlier as the goal was for me to figure that out myself so I could learn what I was doing. However, I know less than nothing about VBA so I am affraid that is not a viable option in this case. Thank you for your time and help.

[thekruser]

@weekend00

Actually, I might be able to figure it out if you could please explain the logic in the multiplication. Ultimately, I need to code something that will allow me to challenge dates in my db against date() and return a value if it is within the past, current, or next fiscal/calendar quarter, semi-annual, and/or annual period as well as give a count to/from in days/months. Just trying to take it one step at a time.

[weekend00]

The multiplication is to give every semi-year a unique number. so if you want quarter, we need to multiple the year by 4, e.g.

Function QtrYear(ddate As Date) As Long
'qtryear(1/1/2010) = 2010 * 4 + 0 = 8040
'qtryear(4/1/2010) = 2010 * 4 + 1 = 8041
'qtryear(8/1/2010) = 2010 * 4 + 2 = 8042
'qtryear(11/1/2010) = 2010 * 4 + 3 = 8043
'qtryear(1/1/2011) = 2011 * 4 + 0 = 8044
    If Month(ddate) <4 Then
        qtrYear = Year(ddate) * 4 
    ElseIf Month(ddate) <7 Then
        qtrYear = Year(ddate) * 4 +1
    ElseIf Month(ddate) <10 Then
        qtrYear = Year(ddate) * 4 +2
    Else
        qtrYear = Year(ddate) * 4 +3
    End If
End Function
 
Function fiscalQtrYear(ddate As Date) As Long
    fiscalQtrYear=Qtryear(ddate)-1
End Function