Find and Replace in Formulas

[Tammy]

Hi All

I've got a report that I run monthly and will need to add a month and delete the previous month. The formulas will need to be altered to add the new month. There are probably 30 formulas that will need to be altered. I wondering if anyone has encounted a way to find and replace in the design view of a report? I don't want to change the data in the database just the formulas. For example:

This formula will need to be

=IIf([PK Contract Type Description]="Pending",[AccessTotalsJan 15]*0.75,IIf([PK Contract Type Description]="Prospect",[AccessTotalsJan 15]*0.25,IIf([PK Contract Type Description]="Proposal",[AccessTotalsJan 15]*0.67,[AccessTotalsJan 15])))

This formula

=IIf([PK Contract Type Description]="Pending",[AccessTotalsFeb 15]*0.75,IIf([PK Contract Type Description]="Prospect",[AccessTotalsFeb 15]*0.25,IIf([PK Contract Type Description]="Proposal",[AccessTotalsFeb 15]*0.67,[AccessTotalsFeb 15])))

Doing this manually on a monthly basis will be time consuming. Any way that I can do a find and replace in the formulas (find Jan replace with Feb)?

Thanks in advance.



Tammy

[amrut]

The best option is to use a form with months in a combobox (and years in another ) and replace the month in the report's formula by Forms!YourFormName!YouComboBoxName.
Select the month on form and run the report. Once set, you do not have to change the formula at all

[Tammy]

Thats interesting, Ill have to think about that. It may work but it may not.

I would really like to see if there is a Find and Replace option.

Tammy

[amrut]

It may work but it may not.

Are you using a table or query as record source for your report ?

[Tammy]

I'm using a query and its pretty complex. They wanted to see the months across the top and projects down the side. So each month I have to add a new month across the top and update all the sum formulas to pick up the new month.

Tammy