	AllExperts > Using MS Access
	Search

About Scottgem
Expertise
I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience
I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

You are here: Experts > Computing/Technology > Business Software > Using MS Access > Compare dates in Access VBA

Using MS Access - Compare dates in Access VBA

Expert: Scottgem - 6/30/2009

Question
Hello,
my question is regarding comparing dates in VBA.
I need to calculate eligibility_date based on a hire_date. Eligibility_date can be either Jan 1st or July 1st after 1 year from hire_date. Here is my code, that is about 6 month off what the date should be.
tempDate = DateAdd("yyyy", 1, HireDate)
tempYear = DatePart("yyyy", tempDate)
stringDate = "1231" & tempYear
compareDate = CDate(Format(stringDate, "##/##/####"))
testdate = DateAdd("d", 1, compareDate)
stringDate2 = "0630" & tempYear
compareDate2 = CDate(Format(stringDate2, "##/##/####"))
testdate2 = DateAdd("d", 1, compareDate2)

       If Format(HireDate, "mm/dd") <= Format(testdate, "mm/dd") Then
           EligDate = testdate
       Else
       If Format(HireDate, "mm/dd") >= Format(testdate, "mm/dd") Then
       EligDate = testdate2
       Else
       If Format(HireDate, "mm/dd") > Format(testdate, "mm/dd") And Format(HireDate, "mm/dd") < Format(testdate2, "mm/dd") Then
EligDate = testdate2
       End If
Any help is appreciated.
thanks

Answer
I would do this differently

tempDate = DateAdd("yyyy", 1, HireDate)
tempYear = DatePart("yyyy", tempDate)

If Month(tempDate) < 7 Then
    CompareDate = DateSerial(tempYear,6,30)
Else
    CompareDate = DateSerial(tempYear,12,31)
End If

I really don't see any need for any additional code.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Add to this Answer Ask a Question