Results 1 to 2 of 2

Thread: DLookup problem

  1. #1
    Join Date
    Feb 2003

    Unanswered: DLookup problem

    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!

  2. #2
    Join Date
    Feb 2002
    I think you are going to need a query and some code to do this.

    Something like in the after update event of the form, do a lookup on the query that may return a date.

    If it does not return a date then do something else.

    I think a sample database would be useful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts