Access 97 Lookup Value Between Two Dates

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

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.
 

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
 

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.
 

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?
 

OBP

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

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
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Can you provide a zipped copy of your database with no data in it?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top