Access97: Code for a field in my form

[meg450]

I'm hoping someone can help me. I have a form with a field called Effective Change Date. I also have a Pay Period Lookup Table with fields called Pay Period Date and Pay Period to Deduct Fee. <br><br>Example of Pay Period Lookup Table:<br>Pay Period Date Pay Period to Deduct Fee<br>12/25/00 - 01/07/00 01/21/00<br>01/08/00 - 01/21/00 02/04/00<br>01/22/00 - 02/04/00 02/18/00<br><br>When we make a change in the Effective Change Date field I would like it to look in the Pay<br>Period Lookup Table, Pay Period Date field and find where the Effective Change Date would fall between and then find what Pay Period to Deduct Fee. Does anyone know if this can be done and how? Thanks a million in advance.<br>

 

[elizabeth]

If you restructure your Pay Period Lookup table like this:<br><br>PayPeriodStart PayPeriodEnd PeriodToDeductFee<br>12/25/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;01/07/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;01/21/00 <br><br>You could use a combobox with an underlying query with a criteria of being between the start & end date. The PeriodToDeductFee needs to be the first, bound column. In general, you try to make each data field atomic, rather than a conglomeration of multiple pieces of information.<br><br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;