Complex Selection Criterion

[Guest]

I have a database of jobs that are performed on a regular basis. Once a week
I need to print out any job that is due. I know the date the jobs were
entered [Start] in the DB, the interval in days that they need to be
performed [Interval], and I prompt the user for a report date [RD] for the
group of reports. I'd like to print out anything that falls into this 7 day
window. I cannot come up with a good selection criterion.

I noticed that if you take the integer part of the expression ([Start] –
[RD]) / [Interval]) and compare it to the integer part of the same thing 7
days later, ([Start] – [RD] + 7 / [Interval]), the two will be equal if the
report is not due and unequal when it is due. In other words the interval
has rolled over during the week in question.

Thus, if the following expression is true then the reports are due.
INT ([Start] – [RD]) / [Interval] <> INT ([Start] – [RD] + 7) / [Interval]

I just can’t figure out a way to put this into a selection query. Anybody
have any ideas?

 

[Ken Snell \(MVP\)]

Parameters [RD] DateTime;
SELECT *
FROM TableName
WHERE INT ([Start] - [RD]) / [Interval] <> INT ([Start] - [RD] + 7) /
[Interval] = True;

 

[James A. Fortune]

I have a database of jobs that are performed on a regular basis. Once a week
I need to print out any job that is due. I know the date the jobs were
entered [Start] in the DB, the interval in days that they need to be
performed [Interval], and I prompt the user for a report date [RD] for the
group of reports. I'd like to print out anything that falls into this 7 day
window. I cannot come up with a good selection criterion.

I noticed that if you take the integer part of the expression ([Start] –
[RD]) / [Interval]) and compare it to the integer part of the same thing 7
days later, ([Start] – [RD] + 7 / [Interval]), the two will be equal if the
report is not due and unequal when it is due. In other words the interval
has rolled over during the week in question.

Thus, if the following expression is true then the reports are due.
INT ([Start] – [RD]) / [Interval] <> INT ([Start] – [RD] + 7) / [Interval]

I just can’t figure out a way to put this into a selection query. Anybody
have any ideas?

If the following illustration describes your situation:

Start
+
Start RD Interval RD + 6
--0--------[X---------0-----------X--]
| |
|<-----Interval---->|

then use

WHERE DateAdd("d", [Interval], [Start]) BETWEEN RD AND DateAdd("d", 6, [RD])

If

Start
+
Start RD - 6 Interval RD
--0--------[X---------0-----------X--]
| |
|<-----Interval---->|

then use

WHERE DateAdd("d", [Interval], [Start]) BETWEEN DateAdd("d", -6, [RD])
AND [RD]

If the week must start on a certain weekday then use:

WHERE LEDay(DateAdd("d", [Interval], [Start]), x) = LEDay([RD], x)

where x = the number of the weekday (Sunday = 1 through Saturday = 7) on
which the week starts. That is, a due date is included if it falls in
the same week as RD with the week starting on a selected weekday.

LEDay:

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

I'm using a shotgun approach because I'm not sure just yet how to
interpret your inequality. If none of those work I can look at your
inequality again (with the original parentheses' locations since what
you wrote afterward should never equate). BTW, Ken's idea is excellent,
but you might want to double check those parentheses first.

James A. Fortune
(e-mail address removed)