Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002

    Question Unanswered: DLookup w/ Date Range Criteria

    Ok - I've got a table, Pay Dates, which has three fields:

    Pay Period Begin Date, Pay Period End Date, Date of Check.

    Data looks like this:

    PyBeg PyEnd PyDate
    2/9/02 2/22/02 3/01/02
    2/23/02 3/08/02 3/15/02
    3/09/02 3/22/02 3/29/02

    I'd like to use DLookup to find the date a paycheck will be recieved for work performed on a specific date, "EfDate". For example, work performed on 2/25/02 will be paid in check recieved 3/15/02. I'm trying to use DLookup to find this only returns the first value in the table:

    Date: DLookUp("PyDate","Pay Dates","[Queries].[Ef Date].[EF DATE] BETWEEN #" & "[Tables].[Pay Dates].[PdBeg]" & "#" And "#" & "[Tables].[Pay Dates].[PdEnd]" & "#")

    This only returns the first record in the Pay Dates table, regardless of the value of [EfDate]. Any ideas?? I've tried all I can think of, this is the closet I've gotten.



  2. #2
    Join Date
    Feb 2002
    Dublin, OH
    First, it you have your fields labeled as "PyBeg" and "PyEnd" at the top and "PdBeg" and "PdEnd" in your DLookUp criteria.

    If that isn't the problem . . .

    I've never been able to figure out why, but I've had similar problems using BETWEEN. Try using efDate >= PyBeg and EfDate <= PyEnd

    Also, you should be able to drop the "[Queries].[Ef Date]." and "[Tables].[Pay Dates]." parts of the criteria and be OK. But I don't think it should hurt anything having them there, either . . .
    Patrick Mildenberg

  3. #3
    Join Date
    Sep 2001
    Chicago, Illinois, USA
    Create the following, as a named query (say "qryDeterminePayDate") . . .

    SELECT PayDates.PdDate
    FROM EfDates, PayDates
    WHERE (((EfDates.EfDate) Between [PdBegin] And [PdEnd]));

    . . . and then use your DLookUP to grab the needed data from the results of the query.

  4. #4
    Join Date
    Feb 2002


    Used Guru's suggestion of the 2nd query to calculate the dates, and rather than using DLOOKUP in the original, I just linked the two queries and added the field "PyDate" to the output.

    Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts