Form date field look up

[kingrexx]

I am creating a Form in Access 2000 to track request by month. However, we use an alternate calendar, so I need to check the Request Date to determine the New Month and Year. Here is the process flow:

Table1.Request_Date - updated on Form
AfterUpdate Select * From Table2 Where Table1.Request_Date => Table2.Start_Date and Table1.Request_Date <= Table2.End_Date

Should return Table2.Month and Table2.Year and those values go to Table1.New_Month and Table1.New_Year... Or an error if there are no matches.

I am stuck on how to code this in Access, so any help would be appreciated.
Thanks!

[FishVal]

Hello.

Could you clariy what do you mean by "alternate calendar"?
Are there any rules as for determining month and year of a date in alternate calendar?
Does Table2 contain these rules?

And the last but not the least - posting table(s) metadata would be nice.
Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Fish

[kingrexx]

The Alternate Calendar is basically built yearly and is used as a billing calendar. For example 12/06/08 - 01/02/09 would be considered Jan 09; 01/03/09 - 01/30/09 would be Feb 09; 01/31/09 - 03/06/09 would be Mar 09.

So, if we received a Request on 12/08/08, we would want to show it in the database table as New_Month = January; New_Year = 2009, and all of our reports would be based on those dates, and not the actual Request date.

[FishVal]

So in Table2 you have Start_Date, End_Date and, let us say, Billing_Date defining conversion rules.
You could use the following join:

Select Table1.*, Table2.Billing_Date From Table1 INNER JOIN Table2 ON Table1.Request_Date => Table2.Start_Date AND Table1.Request_Date <= Table2.End_Date;
 

Regards,
Fish

[kingrexx]

That should work.. What code do I need to add to get the Table2.Billing_Date value into the Table1.New_Month field?