Need to standardize some records so my queries will work.

[froggiebeckie]

The records that I'm working with are pulled weekly from another db, which has been modified by a boatload of folks, over the years. Every one of them had their little ways of doing things.
The data in "Product" field isn't always entered the same way. For example:
SS_11_0101__Z2 and SS 11__0101_Z2 and SS 11 0101 Z2 are all the same product, just apparently selected from different drop down boxes which (of course) are usually slightly different, usually a problem with the underscores.

This is causing me to miss some listings when I run my queries.
Fixing the main db is NOT an option.
What I need to do is figure out how to find any "_" (underscores) and replace them with a single space, in my db.

I run some searches here, but haven't found anything that triggered an "AHA!" moment.
Anyone have any recommendations about how to tackle this?
Update query, Replace() function or maybe a macro?

As always, thanks in advance for any suggestions you may have.

BeckieO

 

[Michael J Ross]

Yes youre on the right line, you need an update query, put your Product field in the query and in the update to part put

Replace([Product],"_"," ")

HTH

 

[Moniker]

In addition to using a Replace like mentioned above, since you only want one space between each one, use a replace again to remove multiple spaces. You know your data better than me, so here's how you would do it for two and three spaces. (I don't know how many spaces you may or may not have in there.)

[COLOR="Red"]'replaces all "_" with " "[/COLOR]
Product = Replace([Product],"_"," ")
[COLOR="Red"]'replaces "   " with " " (three spaces with one)[/COLOR]
Product = Replace([Product],"   "," ")
[COLOR="Red"]'replaces "  " with " " (two spaces with one)[/COLOR]
Product = Replace([Product],"  "," ")

 

[froggiebeckie]

First step works, but...

Thanks so much for your quick responses.
Moniker is right, I do end up with multiple spaces that need to be replaced with single spaces.
I've been able to successfully use the single Replace following Michael's instructions.
But when I try to use 'AND" to add more Replace functions in the UPDATE TO field, I get a return of 0 or -1, instead of the product name, when I run the query.
If I try to post the code from Moniker in SQL view, I get "Syntax Error" messages.

Do I need to make separate update queries or am I just missing a connection to do multiple Replaces?

Thanks, again,
BeckieO

 

[The_Doc_Man]

Fixing the main db is NOT an option.

Until this BECOMES an option (or worse, WHEN it becomes a MANDATE), you will continue to have problems. It sounds to me like your company needs something called a "style guide."

This is basically a document that defines "official" formats for things important to the company. Do you want to attract positive attention (at a certain risk)? Bring up this topic as a corporate or at least divisonal or departmental suggestion. The risk is that someone will tell you... "Great Idea. It's your idea... you write it for us and we'll approve it."

What does a style guide buy you? Easier exchange of data, for one thing. (Wasn't that WHY you asked for help in the first place?) A greater uniformity to what people see, which means better continuity of support - since your documentation (program or manual procedures) becomes predictable in form and content. And it gets you grumbles at first from the folks who AREN'T complying with the style guide, but kudos from the bosses who eventually see better quality of work - because now they have a formatting standard against which to compare work. They have a way to MEASURE when that work is done properly according to standards.

Like any advice, it is only worth what you paid for it... but if it helps, go for it.

 

[froggiebeckie]

Preaching to the Choir

Like Grandma used to say, "You're preaching to the choir."
I couldn't agree more, and I believe we are actually working in that direction (style guide) for upcoming events, processes and changes.
Of course, change comes slowly so meanwhile, we have to deal with the reality, which is previously entered data that's being used for a variety of reports and reviews.
Since I can't control the original data, I figure the next best thing for me is to control what I've pulled in to my db.

Thanks so much for sharing with me.

BeckieO

 

[froggiebeckie]

Took the easy way out...

I just went ahead and made a second update query to replace the multiple spaces with a single space.
There's probably a better way to handle this, but this is quick and gets the job done. Meanwhile, I'll keep working with the code from Moniker, 'cause the more I figure out, the more apt I am to be able to help someone else.

To Recap--
I ended up with 2 update queries that will run each week when new data has been pulled into the table, using the suggestion from Michael J. Ross.

"put your Product field in the query and in the update to part put
Replace([Product],"_"," ")"

Thanks again to one and all.

BeckieO

 

[The_Doc_Man]

But when I try to use 'AND" to add more Replace functions in the UPDATE TO field, I get a return of 0 or -1, instead of the product name, when I run the query.

Probably the AND looks like a Boolean operator for which the result is a truth value (0 or -1). "AND" in an UPDATE TO cell of the query grid is going to be an operator. "AND" in a CRITERIA cell will be treated as a logical conjunction of two conditions that must apply at the same time in order to be part of the range of the query.