By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

Update/replace in MS Access query

P: 3
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
1 Week Ago #1
Share this Question
Share on Google+
7 Replies


P: 65
What's wrong with Replace?

Expand|Select|Wrap|Line Numbers
  1. Update
  2.     YourTable
  3. Set
  4.     [YourField] = Replace([YourField], "Univ", "University")
However, this will also rename, say, Universal to Universityersal, so you may need some expanded and more sophisticated filtering.
1 Week Ago #2

P: 3
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.
1 Week Ago #3

P: 65
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.
1 Week Ago #4

P: 3
This can be done using Instr()?
1 Week Ago #5

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

Expand|Select|Wrap|Line Numbers
  1. Update
  2.     YourTable
  3. Set
  4.     [YourField] = Replace([YourField], " Univ ", " University ")
1 Week Ago #6

NeoPa
Expert Mod 15k+
P: 31,606
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.
1 Week Ago #7

Rabbit
Expert Mod 10K+
P: 12,383
What about the phrases:
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]%'
1 Week Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.