Multiple Dlookups to find one value

  • Thread starter Thread starter Pepper
  • Start date Start date
P

Pepper

Help.

I am trying to create a dlookup that looks at two fields
on a form and then does 2 dlookups to return a date minus
7 days.

Basically, I want the lookup to see the quarter and week
that the deal is expected to come in and then return a
value that is one week before the start of that week.

In the form called "Pipeline", there are fields called
Week and Quarter. I also have a date table called "Dates"
that has the following structure:

ORDER_DATE Current_quarter Pipe_week
01-Feb-03 Q1 FY04 Week 01
02-Feb-03 Q1 FY04 Week 01
03-Feb-03 Q1 FY04 Week 01
04-Feb-03 Q1 FY04 Week 01
05-Feb-03 Q1 FY04 Week 01
06-Feb-03 Q1 FY04 Week 01
07-Feb-03 Q1 FY04 Week 01
08-Feb-03 Q1 FY04 Week 02
09-Feb-03 Q1 FY04 Week 02
10-Feb-03 Q1 FY04 Week 02
11-Feb-03 Q1 FY04 Week 02
12-Feb-03 Q1 FY04 Week 02
13-Feb-03 Q1 FY04 Week 02
14-Feb-03 Q1 FY04 Week 02

If, for instance, the form values were "Q1 FY04" for
Quarter and "Week 01" for Week, I would want the value
of "01-Feb-03" -7 to be displayed.

How do I accomplish that? I want to return the lowest
(min) value for the week.
 
There is a special function DMIN to do this. Its just like DLoopkup except
it will return the minimum value found. The syntax is

DMIN("[Order_Date]","Dates","[Current_Quarter]=Me.txtQuarter and
[PipeWeek]=Me.txtWeek")

There is also a DMAX, which will give the maximum result.

Kelvin
 
Back
Top