Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: lookup a range and return a result

    I have a table called [aPerformancePeriod]
    This table is made up of fields [ID] [PerformancePeriod] [PPStartDate] [PPEndDate]
    It holds 10 records which stores the start and end dates of each performanc period eg:

    ID=1 [PerformancePeriod] = PP1 [PPStartDate] = 1/07/2009 [PPEndDate] = 31/12/2009

    ID=1 [PerformancePeriod] = PP2 [PPStartDate] = 1/1/2010 [PPEndDate] = 30/6/2010

    ..........etc

    *******
    I have another table called [cJSDetails2JSMODetails]
    In addition to other fields it has a field called [ActualMOEndDate] this holds the end date of the Job Seeker.

    I need to know in what [PerformancePeriod] did the job Seeker end.

    So if they ended on 5/1/2010 they would have ended during PP2.

    Can this be done as a Query? Which would be great if it could be done.

    Or can it be only be done as a calculaton in a form?

    I was thinking about a DLookup but just cant work out how to get it to look at a range.

    Any help will be greatly appreciated.
    Last edited by Karen Day; 01-09-10 at 23:02.

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Try something like this in a query:

    SELECT PerformancePeriod
    FROM aPerformancePeriod
    WHERE PPStartDate <= ActualMOEndDate AND PPEndDate >= ActualMOEndDate

    The syntax is not exact but it should get you in the right direction. (I hope!)

    C

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Dear Karen,

    This query will returns the information you're looking for:
    Code:
    SELECT aPerformancePeriod.PerformancePeriod, cJSDetails2JSMODetails.ActualMOEndDate
    FROM aPerformancePeriod, cJSDetails2JSMODetails
    WHERE cJSDetails2JSMODetails.ActualMOEndDate Between [aPerformancePeriod].[PPStartDate] And [aPerformancePeriod].[PPEndDate]
    If you want to use a DLookup function in code, you can:

    1. Create a query named Qry_PerformancePeriods:
    Code:
    SELECT cJSDetails2JSMODetails.MO_ID, aPerformancePeriod.PerformancePeriod, cJSDetails2JSMODetails.ActualMOEndDate
    FROM aPerformancePeriod, cJSDetails2JSMODetails
    WHERE cJSDetails2JSMODetails.ActualMOEndDate Between [aPerformancePeriod].[PPStartDate] And [aPerformancePeriod].[PPEndDate]
    2. Use the following code in your application (here assuming the code is in the class module of a form that has a MO_ID field):
    Code:
        Dim strCriteria As String
        Dim strPerformancePeriod As String
        
        strCriteria = "MO_ID = " & Me!MO_ID
        strPerformancePeriod = Nz(DLookup("PerformancePeriod", "Qry_PerformancePeriods", strCriteria), "")
    You can also build the query on the fly (here assuming the code is in the class module of a form that has a MO_ID field):
    Code:
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strPerformancePeriod As String
    
        strSQL = "SELECT cJSDetails2JSMODetails.MO_ID, aPerformancePeriod.PerformancePeriod, cJSDetails2JSMODetails.ActualMOEndDate " & _
                 "FROM aPerformancePeriod, cJSDetails2JSMODetails " & _
                 "WHERE (cJSDetails2JSMODetails.MO_ID = " & Me!MO_ID & ") AND " & _
                 "(cJSDetails2JSMODetails.ActualMOEndDate Between [aPerformancePeriod].[PPStartDate] And [aPerformancePeriod].[PPEndDate])"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            If Not .EOF Then strPerformancePeriod = !PerformancePeriod
            .Close
        End With
        Set rst = Nothing
    Have a nice day!

Posting Permissions

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