1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 97 Lookup Value Between Two Dates

Discussion in 'Business Applications' started by rmoreno, Jan 11, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. rmoreno

    rmoreno Thread Starter

    Joined:
    Jan 11, 2011
    Messages:
    4
    Hi. I have an old Access 97 database that I have tasked to update.

    I have two table files, tbl_Orders and tbl_FiscalCalendar. The tbl_FiscalCalendar has 3 fields, [StartFiscalMM], [StopFiscalMM] and [FiscalYear].

    How do I create a query that takes the tbl_Orders.[Date] field and finds the correct tbl_FiscalCalendar.[FiscalYear] value between the [StartFiscalMM] and [StopFiscalMM] fields?

    Any help would be most appreciated.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    rmoreno, welcome to the Forum.
    First of all in the query you need find the Month and Year of the tbl_Orders.[Date], so add 2 new columns with the following Headings
    Month:Format([Date], "mm")
    Year:Format([Date], "yy") or Year:Format([Date], "yyyy")

    By the way it is not good to use just Date as a Field name as Access can confuse it the the System date called Date. It might be an idea to change it to OrderDate.
     
  3. rmoreno

    rmoreno Thread Starter

    Joined:
    Jan 11, 2011
    Messages:
    4
    Already thought of that. The problem is that our company's fiscal year starts in December. So any activity that occurs in Dec-2010 is technically part of the 2011 fiscal year. That is why I created a separate lookup table so that I can apply the correct fiscal year value.

    In Excel I could use a VLOOKUP function to match the order date with the correct Fiscal Year value, however, in Access VLOOKUP is not possible. Any ideas?

    Thanks
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well that was only the first part. You also need a CalendarYear in the tbl_FiscalCalendar table.
    Now you add the tbl_FiscalCalendar table to the Query with no Links to the other table.
    Now in the Year Columns Criteria Row add the tbl_FiscalCalendar.[CalendarYear]
    In the Month Column add in to the Criteria Row
    Between tbl_FiscalCalendar.[StartFiscalMM] AND tbl_FiscalCalendar.[StopFiscalMM]
    I have posted an example of this previously but it is Access 2000.
     
  5. rmoreno

    rmoreno Thread Starter

    Joined:
    Jan 11, 2011
    Messages:
    4
    Thanks for the suggestion. I tried applying what you suggested but I get the following error box:

    Invalid bracketing of name '[Date] between [StartFiscalMM] and [StopFiscalMM]'.

    What does that mean?
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Like I said try changing the name of the field called date to OrderDate.
     
  7. rmoreno

    rmoreno Thread Starter

    Joined:
    Jan 11, 2011
    Messages:
    4
    Sorry about the delay to your response.

    I tried your suggestion about changing the [Date] field to [OrderDate]. I still get the same error:

    "Invalid bracketing of name '[Date] between [StartFiscalMM] and [StopFiscalMM]'

    Can you provide me a simple example please. It is obvious that I am doing something very, very wrong but cannot see what it is.

    Thanks
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you provide a zipped copy of your database with no data in it?
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/974125

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice