469,917 Members | 1,729 Online

# Comparing one date against a list of dates

I have two completely unrelated tables, one for reviews and another for
pay periods. The reviews table has a review date in it, and the pay
periods table is just a list of the beginnings of pay periods.

What I need to do is take a given review date and find out which date
in my pay periods table it's closest to, above or below.

Any help is greatly appreciated.

-Josh

Nov 13 '05 #1
5 3268
If you don't want to use code, you can do it with a query.
Set one of the fields to abs(datediff(your two fields)) and then aggregate
to take the 'min' value, or sort and take the first record.

David

<jn****@gmail.com> wrote in message
I have two completely unrelated tables, one for reviews and another for
pay periods. The reviews table has a review date in it, and the pay
periods table is just a list of the beginnings of pay periods.

What I need to do is take a given review date and find out which date
in my pay periods table it's closest to, above or below.

Any help is greatly appreciated.

-Josh

Nov 13 '05 #2
What do you want to do if the review date falls exactly in the middle
between two paydates? This won't matter if the number of days in a pay
period is an odd number, but if it's an even number, you'll need to
decide what to do. For example, if your payperiods are 14 days apart,
you have paydates of 9/9/05 & 9/23/05, and a review date of 9/16/05,
the review date would be 7 days from each paydate. Would you want it
to display 9/9 or 9/23?

Nov 13 '05 #3
correction to above, it will matter if the number of days between two
pay dates is ODD, not even.

With that in mind, here's a function you can use:

Public Function ReviewPayDate(ReviewDate) As Date
Dim dtPeriodBefore, dtPeriodAfter As Date
'Handle situation when ReviewDate is empty
If IsNull(ReviewDate) Then Exit Function
dtPeriodBefore = Nz(DMax("PeriodBeginDate", "tblPayPeriods",
"PeriodBeginDate <= #" & ReviewDate & "#"), 0)
dtPeriodAfter = Nz(DMin("PeriodBeginDate", "tblPayPeriods",
"PeriodBeginDate >= #" & ReviewDate & "#"), 0)
'Handle situation if no pay period is found for either before or after
the review date
If dtPeriodBefore = 0 Or dtPeriodAfter = 0 Then Exit Function
'Determine which date to send back to the user
If DateDiff("d", dtPeriodBefore, ReviewDate) < DateDiff("d",
ReviewDate, dtPeriodAfter) Then
'use the earlier pay date
ReviewPayDate = dtPeriodBefore
ElseIf DateDiff("d", dtPeriodBefore, ReviewDate) > DateDiff("d",
ReviewDate, dtPeriodAfter) Then
'use the later pay date
ReviewPayDate = dtPeriodAfter
Else
'the ReviewDate is smack dab in the middle of two pay periods, so
use the earlier date
ReviewPayDate = dtPeriodBefore
End If
End Function

Paste the function into a module in your db.
Replace PeriodBeginDate with the name of your pay period begin date
field.
Replace tblPayPeriods with the name of your pay period table.

Then you can use the function in queries, on forms & on reports if
you'd like.

Let me know how it works out!

Jana

Nov 13 '05 #4
sorry, you'll have to fix some line wrapping after you paste :(

Nov 13 '05 #5
thanks a lot for the info. i'll try it out.

-josh

Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.