Backoff x days from a date

[apcray]

I have a future date (MM/DD/YY) and a lead time that can
be expressed in either days or months. I want to use a
query to provide a date that is 'Future date minus lead
time'

Then I want to display only those dates that are past or
within the current month.

Any ideas how I can accomplish this?

 

[Allen Browne]

Your table will have fields such as these:
FutureDate Date/Time
Period Number
PeriodType Text

PeriodType would add "d" if the period is a number of days, or "m" if the
period is in months.

Your query will then have this WHERE clause:
WHERE DateAdd([PeriodType], -[Period], [FutureDate])
<= DateAdd("m", 1, Date() - Day(Date()) + 1)

 

[John Vinson]

I have a future date (MM/DD/YY) and a lead time that can
be expressed in either days or months. I want to use a
query to provide a date that is 'Future date minus lead
time'

Then I want to display only those dates that are past or
within the current month.

Any ideas how I can accomplish this?

How can you determine whether the lead time is in days or months? If
there's a lead time of 7, does that mean it's due next Thursday, or
next May, and how can the query tell?

Assuming that you've got a field named Unit containing "Days" or
"Months", you could put a calculated field in your table:

Future date minus lead time: DateAdd(IIF([Unit] = "Days", "d", "m"),
-[Lead Time], [Future date])

The Dateadd function's first argument is a string indicating the time
unit to add or subtract - "d" is days, "m" is months, "s" is seconds
and so on. See the online Help after opening the VBA editor to get the
right help file.