Replace Function!

[snafoo4]

Suppose I have a query with 2 Columns, A and B. I want to make column C, which is the same as column B, only every occurance of a bit of text is replace by the data in column A.

I know that there is a replace function which you can use in VBA, but I don't know how to make that work in the query. I have tried using the Replace function as the criteria in Query Design View, but that didn't work.

Can anybody help?

 

[Chris RR]

OK, I'll give you the lecture first, then try to help. If you can always create what would go into Column C by combining bits of stuff from A and B, you should not have a Column C in your table at all. Period. Just build the "C" data when you need to use it, whether that's on a form or in a report.

Then, if you're determined to do this (or if this would just be an initial value for "C", to be changed by the users), you need to add column C to your table design, then create a simple update query to populate it. This sounds like it would not be very efficient, but it actually is much more likely to give you correct data, and is not as resource intensive as you think.

If you want to automatically fill in "C" whenever anyone enters A & B, you can do this easily in your form, but there may be a more elegant way, I don't know.

 

[KDg]

Hi sanfoo,

i guess Replace made its way into 2k, and i only have 97. However, as you can use any other function in a query i don't see why you shouldn't be able to use it also, i just can't test that. Rather than putting it in the criteria it should go in as a field. In the QBE grid you'd out something like:-

ColumnC: Replace(string, search_string, replace_string[, start[, count[, compare_mode]]])


HTH

Drew