Update/replace in MS Access query

[BR1134]

Hi,

I'm trying to create a query in MS Access to partially update or replace a text field.
I need to remove "Univ" and replace it with "University" from that record without changing anything else.
Can I do this without using replace() or using if()?

Thanks

[cactusdata]

What's wrong with Replace?

Update
    YourTable
Set
    [YourField] = Replace([YourField], "Univ", "University")

However, this will also rename, say, Universal to Universityersal, so you may need some expanded and more sophisticated filtering.

[BR1134]

Some records already have "UNIVERSITY" and when I use REPLACE(), they turn into "UNIVERSITYERSITY". I couldn't remove "UNIV" using Instr() ."UNIV" can be anywhere, at the beginning or end of string.

[cactusdata]

That's what I wrote:

However, this will also rename, say, Universal to Universityersal, so you may need some expanded and more sophisticated filtering.

and, of course, University to Universityersity etc.

How, and to what degree, to add further parsing and filtering is up to you, as we don't have your data.

[BR1134]

This can be done using Instr()?

[cactusdata]

Perhaps. That depends on your data.
If Univ is a full word, try including spaces.

Update
    YourTable
Set
    [YourField] = Replace([YourField], " Univ ", " University ")

[NeoPa]

We can't tell you what your question should be.

From your understanding of the data you're dealing with, what rules need to be applied to convert it to what you require?

Without any understanding of your data how can we tell you what your question should be? Sure, we can help once you have a question, but if you only have a half-thought question then all that will happen is it will change every time we give an answer that isn't for the full question - that you haven't told us yet.

What I'm saying here is fairly obvious. It isn't complicated. You need to get the question right before you can expect an answer to it that works.

[Rabbit]

What about the phrases:

• nonuniversal
• University univ
• www.onlineschool.univ
• Call 1-800-555-UNIV

These are contrived examples but these are the things you need to think about when it comes to data entered by humans. If you don't think it can happen, you're going to be unpleasantly surprised one day.

Why do you need to replace it in the first place? Is this a nice to have feature? Or is it a critical feature? If it's the former, I would suggest not bothering because it might be more trouble than it's worth.

A complete solution would require a VBA function that utilizes a complex regex or custom built parsing algorithm. A 90% solution could be one that only uses Replace() on those rows that have '%univ%' and not '%univ[a-z]%'