Using IIf on a Continuous Form

[Holly_Associated]

Good Morning Experts,

I have a continuous form sfrmAddress which is based on my table tblAddressList.
What I am trying to achieve concerns the date fields DateFrom and DateTo.
DateFrom is a bound text box txtDateFrom
I am trying to put an IIf function in the Control Source of unbound text box txtDateTo

=IIf([txtDateFrom]=[qryAddressHistory1]![LatestFromDate],Date(),[tblAddressList]![DateTo])


qryAddressHistory1 returns the LatestFromDate , so the most recent address.
The idea being if it is their most recent address, they have lived there until today, if it is not their most recent address it will display the date contained in the table (when they moved out or DateTo).

I have tried this a few different ways, double and triple checked my references and I'm usually OK with IIf so I don't understand where it's going wrong!

Any help much appreciated

Edit: Sorry I should add I'm getting the #Name? error all the way down the column when I open the form.

 

[CazB]

Obviously I don't know how your queries are set up, but have you tried putting the function into the query instead - so that the query works it out, rather than working it out on the form? You can then just display the calculated field on the form...
eg:
EndDate: IIf(DateFrom=LatestFromDate,Date(),DateTo)

 

[pr2-eugin]

Continuous Forms works differently than regular forms specially with unbound controls.. Try CazB's suggestion.. (Not tested, but) I think it might be the way to go..

 

[Holly_Associated]

Oh Caz you are a genius, I couldn't see the wood for the trees!
Worked a charm
Thank you!