replacement problem from MS Access 2000

[gilgalbiblewhee]

I encountered a problem in MS Access 2000. When I needed to replace '#' with letter 'c' there was a problem in first the 'find' and then the 'replace'. Is there any other way to do this?

 

[Uncle Gizmo]

I did an experiment, I used the find and replace function available in MS Access, and I was surprised to see that it actually interprets the hash as any number. Although being annoying in this particular case, that if you want to change the hash to something else you can't, it may be a useful feature if you want to search for numbers in a field. However I'm not too sure about that, because it only seemed to work in a number field, and not in a text field. If that's the case I can't really see the point! Maybe it's not a feature, just some sort of bug.

Anyway, the attached file contains a simple routine to replace the hash character with a "C".

 

[RoyVidar]

It's a feature, and not a bug

For some details, look up the VBA Like operator in the help files

The escape character(s), are [brackets], so for instance to replace C# with Db, enter: C[#] in the find what box, Db in the replace with box...

To take it a bit longer, you can build search patterns, like for instance [a-d0-9], which will allow characters a-d and numbers 0-9, making this an excellent "poor mans regexp"

 

[Uncle Gizmo]

Well! that works lovely!

I'm nearly impressed with Microsoft ....

I shall be using the find and replace a lot more now!

 

[RoyVidar]

Yeah - but don't forget it is equally strong both with the VBA like operator when coding (where you can search by pattern using this, and not be restricted to exact match, as with the instr function) and the like operator in queries.

...were sometext like "a[a-c][1-2][0-9]*"

which should give all combinations starting with a, next character in the range of a-c, next character in the range 1-2, next character in the range 0-9...