I have a form, frmInformation, based on a table, tblInformation. A record in tblInformation has a customer reference number, RefNo, and a date code, DateCode as the key fields. Each record also has a project number, ProjNo. The rest of the fields on the form are mostly calculated totals, which are all working except two.
The two that arent working require a value, CumPrin from another table, tblDataTable. tblDataTable has project number, ProjNo, and date code, DateCode as the key fields. For each project number there are 60 set date codes, and each record has a corresponding CumPrin.
This is where it becomes slightly difficult. In frmInformation, when a date code and project number are entered I need the corresponding value for the CumPrin to be returned from tblDataTable to the form so it can be used in the calculations. But because there are only 60 records stored for each project in tblDataTable there will not always be an exact match for the datecode from frmInformation, so it will fall between two date codes then. In this case I need the CumPrin to be returned from the smaller of these two date codes.
I have tried using the DLookup function to return the CumPrin value but I cant get it to work. Could anyone please help!
The two that arent working require a value, CumPrin from another table, tblDataTable. tblDataTable has project number, ProjNo, and date code, DateCode as the key fields. For each project number there are 60 set date codes, and each record has a corresponding CumPrin.
This is where it becomes slightly difficult. In frmInformation, when a date code and project number are entered I need the corresponding value for the CumPrin to be returned from tblDataTable to the form so it can be used in the calculations. But because there are only 60 records stored for each project in tblDataTable there will not always be an exact match for the datecode from frmInformation, so it will fall between two date codes then. In this case I need the CumPrin to be returned from the smaller of these two date codes.
I have tried using the DLookup function to return the CumPrin value but I cant get it to work. Could anyone please help!
