Field based on Date Range

LFC

Registered User.
Local time
Today, 08:08
Joined
Jul 22, 2010
Messages
43
I have a table with a process date. I have another table in which each record has a start date and an end date. I was wondering how to make a field in the table with the process date that would tie back to the other table based on the date range. I've tried using SQL in the lookup for the field and I could select options, but I want it to auto:
Code:
SELECT Week_ID, Week_Start, Week_End, FY_Month, FY_Quarter, LHW_All_Claims_IMP_tbl.[Process Date] FROM CI_Calender_FY2010, LHW_All_Claims_IMP_tbl WHERE (LHW_All_Claims_IMP_tbl.[Process Date] BETWEEN CI_Calender_FY2010.Week_Start AND CI_Calender_FY2010.Week_End);
 
It sounds like you want a non-equi join. Here's an example, pulling an appropriate rate:

SELECT tblActivity.OrderNum, tblActivity.DateField, tblRates.Rate
FROM tblActivity LEFT JOIN tblRates ON tblActivity.DateField Between tblRates.StartDate and tblRates.EndDate;
 
It's still not working. It's doing the same thing as before, where I can select what I want from a combo box on each field but doesn't automatically populate them. Below is your SQL that I modified. It looks right to me but I have no idea. Any Idea's?
Code:
SELECT CI_Calender_FY2010.Week_ID, CI_Calender_FY2010.Week_Start, CI_Calender_FY2010.Week_End, CI_Calender_FY2010.FY_Month, CI_Calender_FY2010.FY_Quarter, LHW_All_Claims_IMP_tbl.[Process Date] FROM LHW_All_Claims_IMP_tbl LEFT JOIN CI_Calender_FY2010 ON LHW_All_Claims_IMP_tbl.[Process Date] Between CI_Calender_FY2010.Week_Start And CI_Calender_FY2010.Week_End;

Thanks
 
Can you post the db, or a representative sample?
 
I pretty much just deleted everything but I think the basic idea is there. It's the Week_ID field in the LHW_All_Claims_IMP_tbl that has the lookup. Thanks.
 

Attachments

Were you able to figure anything out with the lookup?
 
Sorry, lost track of the thread. I realize now you're trying to do this in a lookup field, which I don't use:

http://www.mvps.org/access/lookupfields.htm

In any event, I wouldn't store the value from the calendar table in the main table. The process date is all you need to get the related info, so it can be looked up as in the query above.
 
Ah. That works perfect. Thanks for the help.
 
Glad we got it working for you.
 
There is one thing that is a problem though. In the SQL a message would pop up that would say No And operator in between expression :'LHW_All_Claims_IMP_tbl.[Process Date] Between CI_Calender_FY2010.Week_Star'. the sql looks exactly like the one pasted above. I corrected this by adding parentheses but for some reason the parentheses are removed sometimes and i have to go back in and put them back in. Do you know why that happens?
 
I actually discovered that the parentheses were required myself; should have mentioned it (though in my experience, yours is the first db that's required them). I don't know why they'd get removed. Sometimes going into Design view of a query will do that type of thing, but that shouldn't affect this query since it can't go into design view anyway.
 

Users who are viewing this thread

Back
Top Bottom