Results 1 to 8 of 8

Thread: Find a price for particular date range in Access 2010

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find a price for particular date range in Access 2010

    I am using Access 2010.

    I have two tables, one with date ranges and prices and another table with a listing of sales by date.

    I need to build a query that give me a list of sales for the price in the date range. So...

    Table 1
    Start Date End Date Price
    1/1/2012 1/7/2012 $5.25
    1/8/2012 1/12/2012 $8.95

    Table 2
    Load# Date
    25 1/3/2012
    26 1/10/2012

    Query
    Load# Date Price
    25 1/3/2012 $5.25

    So, because the load#25 from Table2 date falls within 1/1/2012 and 1/7/2012 of the Table1 it will get a price of $5.25.

  2. #2
    MrExcel MVP boblarson's Avatar
    Join Date
    Nov 2008
    Location
    Portland, Oregon, USA
    Posts
    1,964
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find a price for particular date range in Access 2010

    Bob Larson
    Former Access MVP (2008-2010, 2011)

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find a price for particular date range in Access 2010

    Ummmm...I dont know how to use sequel... Is there a similar way to do it with a query?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find a price for particular date range in Access 2010

    I was thinking of an Iif statement. The "Start Date" is actually the first day of the week. So i was thinking of something like If the "Load date" is >= to the "StartDate" and <= to the "End Date" then Price.

    Any ideas for something similar?

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,526
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Find a price for particular date range in Access 2010

    Ummmm...I dont know how to use sequel... Is there a similar way to do it with a query?
    Sure, you do, you just don't know that you know!

    When you create a Query using the Query Builder in Access, all that is is a GUI representation to build SQL code.
    - The SELECT fields are the fields you elect to show in your Query.
    - The FROM source is the Table or Query you are using as the Data Source in your Query.
    - The WHERE values are what you enter in your Criteria row under each field.

    If you build a simple Query in Access, try switching to SQL view, and you will see the SQL code that you just built. Likewise, if you write SQL code directly and paste it into the SQL window, much of the time you can switch back to Design View and see what it would look like in Query Builder (caveat: there are some complex SQL queries that cannot be represented in Query Builder).

    So, armed with this knowledge, there are a few ways you should be able to go about doing this:

    1. Study the link Bob sent you and look and see what criteria is being put on what fields. Go into Query Builder, and follow the same logic to build your query.

    - or -

    2. Copy the SQL code from the link Bob sent, and replace the tables and field names with your table and field names. Start a new query, switch to SQL view, and copy and paste that SQL code there.

    Give it a shot and see how you do. If you run into problems, post back here and copy and paste the SQL code from the query you tried to create.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Dec 2011
    Posts
    536
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find a price for particular date range in Access 2010

    Hi,
    In your query, bring in "Load #" and "Date" from table 1. Create a new column with the following syntax:

    Effective Price: iif([table 2].[date] between [table 1].[start date] and [table 1].[end date], “Yes”, “No”)

    Bring in both tables 1 and 2 into this query, but do not link the two tables. In the criteria of the column you just created, type in "Yes". As long as there are no overlapping date ranges, you will get a unique price for every load # and date.

  7. #7
    New Member
    Join Date
    Feb 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find a price for particular date range in Access 2010

    Hello

    I tried your code and Access 2013 keeps making the Yes and NO into fields. I have the same situation as OP but not sure if this solved.

    I always get this
    Code:
    IIf([testDates]![AdoptDate] Between [TestDateRanges]![StartDate] And [TestDateRanges]![EndDate],[“Yes”],[“No”])
    where "Yes" turns into ["Yes"] and cannot remove them.

    Any ideas?

    I would think finding if a date is in a date range would be a common thing.

    Thanks.

  8. #8
    New Member
    Join Date
    Feb 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find a price for particular date range in Access 2010

    Ok, I think I found what works for me.

    Thanks to BobLarson's post above and his link to http://www.baldyweb.com/OverLap.htm

    I have a date and I need to see what Fiscal Year range it is in and then pull the Fiscal Year number.
    Code:
    SELECT 
    testDates.AdoptDate, 
    TestDateRanges.FY, 
    [testDates]![AdoptDate]<=[TestDateRanges]![EndDate] And [testDates]![AdoptDate]>=[TestDateRanges]![StartDate] AS Expr1
    
    FROM TestDateRanges, testDates
    
    WHERE ((([testDates]![AdoptDate]<=[TestDateRanges]![EndDate] And [testDates]![AdoptDate]>=[TestDateRanges]![StartDate])=-1));
    The trick is the -1 criteria which limits the many to many join.
    Last edited by Poduska; Jan 13th, 2018 at 07:06 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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