Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Michigan City, Indiana, USA
    Thanked 0 Times in 0 Posts

    IIf in control source (2003)

    I have a form called frmTutorInfo which contains SSN, Name, and address of our tutors. Some of these tutors are students and some are not. Those who are students would already have an address in another table called tblPermData. I would like to either use the address from tblPermData if their SSN is in there or create something that automatically updates the tblTutorData address with the tblPermData address if there is a difference.

    I tried the following expression: =IIf([txtSSN]=tblPermData!SID,tblPermData!LAddress,tblTutorData !TAddress) but am getting the #Name? error. What I'm trying to say is if the SSN on the form has a matching SSN in tblPermData, then use the address from tblPermData, otherwise, use the address from tblTutorData.

    Is there a way to maybe have a macro run on load of the form that would check to see if the two addresses match and update them if they don't? Then I could forget my IIF statement.

    Thanks for any help you can give.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: IIf in control source (2003)

    Create a query based on tblTutorData (I assume that's currently the record source of the form) and on tblPermData.
    Join them on SSN vs SID.
    Double click the join line and select the option to include ALL records from tblTutorData.
    Add the fields you need.
    Create a calculated column

    Address: IIf(IsNull([SID],[TAddress],[LAddress])

    Save this query and use it as record source for the form.
    Set the control source of the address text box to the Address field you added.

Posting Permissions

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