Error Formual

[mjhopler]

Disclaimer – I am an excel person now trying to learn Access. I do not have the knowledge to read VBA and do all my work through design view.

I have two forms both sharing the same sub-form (I am aware there should not be spaces in the names, but this is what I have to work with).

Forms 1: Trigger Test


Form 2: Trigger Test Annualized
SubForm: General Comment

In each of the forms, I have a control named Test Period which returns data in the Format of YearQuarter (i.e. 2009Q1, 2009Q2, 2009Q3, 2009YE). My formula in the General Comment subform looks to the Test Period control, finds the last character and returns a number based on it (returns 3 if 1, 6 if 2, 9 if 3 and 12 if E to represent the number of month from beginning of year to the end of that quarter). The formula I have works fine in the Trigger Test Annualized form, but returns #Name? in the Trigger Test form. My goal is if the formula returns an error, to leave blank. Below is my formula. What is wrong with it?

=IIf(IsError([Forms]![Trigger Test Annualized]![Test Period]),"",IIf(Right([Forms]![Trigger Test Annualized]![Test Period],1)="e",12,IIf(Right([Forms]![Trigger Test Annualized]![Test Period],1)="1",3,IIf(Right([Forms]![Trigger Test Annualized]![Test Period],1)="2",6,IIf(Right([Forms]![Trigger Test Annualized]![Test Period],1)="3",9," False")))))

Furthermore, if possible, I would like the formula to be in the main form rather than in a subform, but I don’t know how to tie it to the Primary Key (Property ID) so that the number changes as I change records. To work around this, I have entered it in the subform which I link it to the PropertyID based on the master child relationship.

Any guidance?

[mjhopler]

I found a work around, but don't know how to mark as solved. How do I mark it solved?

[RuralGuy]

How about sharing your work around so others will benefit. Follow the link in my sig to mark the thread as Solved.

[mjhopler]

Not really sure how I fixed it. My original problem was that when I added a text box to create the formula in the main form, it was not linking to all the records (it returned the answer for the first record, but showed that answer in all records. When I tried it again to show someone my problem, it worked fine. So I don't really know why it would work before and now it does, but it does so I moved on.

[RuralGuy]

Thanks for the update.