Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Unanswered: Retrieve value where date value is between two date fields

    I have two tables, tbl_POLines and tblRF_FiscalPeriods (fiscal months). My company's fiscal month is not in line with calendar month due to being a subsidiary of a subsidiary of a parent company. In tbl_POLines, I have the fields [PONumber] and [LineReceiptDate]. In tblRF_FiscalPeriods I have the fields [FiscalPeriodName], [BeginPeriodDate], [EndPeriodDate].

    I am attempting to pull [FiscalPeriodName] where [LineReceiptDate] is between [BeginPeriodDate] and [EndPeriodDate]. I have unsuccessfully attempted to do this in a Select query using DLookup with multiple criteria.

    Any thoughts?
    Last edited by DCooksey; 12-22-14 at 18:02. Reason: Clarifying situation

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by DCooksey View Post
    I am attempting to pull [FiscalPeriodName] where [LineReceiptDate] is between [BeginPeriodDate] and [EndPeriodDate]. I have unsuccessfully attempted to do this in a Select query using DLookup with multiple criteria.
    Can you post the SQL expression of the query you tried?
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming you have stored date vales in a datetime column, and not text then it should be something like:-

    Code:
    select  A.PONumber, A.LineReceiptDate, C.FiscalPeriodName from tbl_POLines as A, tblRF_FiscalPeriods as C
    where A.LineReceiptDate between C.BeginPeriodDate and C.EndPeriodDate
    you may be able to use a join
    Code:
    select  A.PONumber, A.LineReceiptDate, C.FiscalPeriodName from tbl_POLines as A
    leftjoin  tblRF_FiscalPeriods as C on A.LineReceiptDate between C.BeginPeriodDate and C.EndPeriodDate
    stylistically Im never to happy when I see begin and end limits in a table such as this as it means there is a risk you get problems if due to user error the data overlaps or is missing. ferinstance if the user has lazily copied data and forgotten a month or a few days in the month . assuming you do go down this route and you are providing the initial data take on then get the users to sign off that they have seen this sort of set up data and they have checked it is compliant. If accountants want to hold a necklace party because of data errors, they can do it on their own time, in their own premises far, far away from where I work
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2012
    Posts
    2
    healdem,

    Thank you for your assistance. I figured the answer would result in a self-inflicted forehead slap...and it did. The problem I had was that I did not reference the unjoined table fields correctly. The forehead slap beats beating my head against a brick wall for two hours.

    I agree with your concern regarding begin and end dates. I have AfterUpdate script to cover most bad data entry errors. Considering how the Db is utilized and by whom, what I have in place should prevent nearly all errors. The fiscal periods table is updated once a year after Finance publishes the end of month dates, so only twelve lines are created for each year. If Finance provides the wrong date, it's their error not my team's.

    Thanks again!

Posting Permissions

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