MS Access Comparing Dates

[Elias824]

So I have two Dates the [Interview Date] and Intake Date. I need a way of comparing two dates so our little clinic can see the progress of people after 1-4, 5-8, 9-12, and 12+ months. So I need something like this, for a function in access


if ([Interview Date]- 4 Months) =< ([Intake Date])
1-4 Months

if ([Interview Date]- 8 Months) =< ([Intake Date]) and <= ([interview Date] - 4 Months)
5-8 Months

if ([Interview Date]- 12 Months) =< [Intake Date] and <= ([interview Date] - 8 Months) 9-12 Months

if ([Interview Date]-12 Months) < ([Intake Date])
12+

Anyways I have no idea how to do deal with dates like this, im not sure if there is any reasonable way to do it.

 

[KLin]

This would be best in a function in a module.

1. open up database
2. open VBA editor (alt-f11 will open it)
3. Click Insert in menu bar, then click module.
4. Double click the module and copy and paste the following code into it:

Public Function GetProgress(ByVal InterviewDate As Date, ByVal IntakeDate As Date) As String
Dim Result As Long

Result = DateDiff("m", InterviewDate, IntakeDate)
Select Case Result

Case 0 To 4
    GetProgress = "1-4 Months"
Case 5 To 8
    GetProgress = "5-8 Months"
Case 9 To 12
    GetProgress = "9-12 Months"
Case Else
    GetProgress = "12+ Months"
End Select

End Function

5. Save the module and give it a name(modGlobal would be a good name)
6. In your query, create an expression field referencing the GetProgress function and pass in the fields that are the interview and intake dates
i.e Progress: GetProgress(InterviewDate, IntakeDate)

That will return a string value telling you the month range for the calculated number of months between the interview date and the intake date for each record in your query.

 

[KLin]

So did that work for you, OP? Let me know.

 

[Elias824]

oh man I forgot I posted this, ill have to try it tommrow im not at work right now. Ill let you know.

 

[Elias824]

As far as I can tell it seems to work perfectly although it has enlightened me as to another issue, but that is a forum post for another day. Thanks much!

 

[KLin]

If you need any more help, feel free to ask.