Replace All?

[Moebius]

I have a report that uses dozens of individual text fields. In each text field there is a label that will say something such as [0399S]/[0399P]. What I want to change is the 99 to 00 so that the text field now uses the formula [0300S/0300P]. Is there a fast way of doing this? What I really want to do is do a "Replace all" but I can't seem to find a feature to do that. I'm using Access 2000.

 

[Pat Hartman]

You have a much larger problem than changing label captions if you are using data as label names. Your database is not normalized. The table design is more like that of a spreadsheet than a proper relational data base.

Anyway, you can loop through the fields collection looking for label controls and use the Replace() function to change the captions.

 

[Moebius]

sorry about the confusion...

actually, each text field is using different columns from an existing table... i.e. there's a text field called 0399S and there's another filed called 0300S... now there's literally dozens of different columns in my existing table so i simply took the first set up text fields with their respective column names, and copied them all over the report... but to change what column the text field is looking at is a little more complicated...

so, basically i took my first text field, which was a function of [0399S]/[0399P]... and copied it everywhere... but now i need to change the next set of records to [0300S]/[0300P]... and so i'm looking for a replace all tool in the reports screen to change the functions

does this make it any clearer?

 

[Pat Hartman]

The fact that you are doing this reinforces my statement that you have a design issue. Stop, back up, normalize. Your problem will disappear. A column named 0399S makes no sense. You have made a table that looks like a spreadsheet rather than a relational table. Do a little reading on normalization.

Access has no change all that works on objects. You can buy a product called SpeedFerret or search for shareware (it does exist) but I don't think that even it will do what you want. You can use the method I suggested in my previous post though.

 

[Tiro Cupidus]

I created a report recently that is very similar to yours I believe. I never did find a good way to replace all for my next column of text fields (as I had it set up) to perform a slightly different query. The whole time I was thinking I was going about it in an overly-difficult way. I think Pat's first suggestion would have sped things up a bit.

That brings to mind another question. Is it possible to create a report from code? I know it would have been easier doing something similar with asp or the like.