Dumb question about find/replace

[Perplexed]

Here is my dilemma: I successfully used find/replace to get rid of extraneous numbers in one of my columns (alphanumeric). The results look like I123450. However all of the resulting numbers still have a 0 as the last digit and I need to get rid of them.



Does anyone know of an easy way to do this? You'd think that wildcards would work but I can't figure it out. Your help is appreciated.

[trb5016]

As a preface, you may want to do a quick copy/paste backup of your data before during any find/replace operations!


Press Alt+F11 (this opens the visual basic editor)
Press Ctrl+G (This opens the Immediate Window)

In the Immediate Window type:

CurrentDB.Execute "UPDATE [<YourYableName>] SET [<YourColumnName>]=Left([<YourColumnName>],Len([<YourColumnName>])-1)"

(don't include the <>)

Then press enter

This removes the right most character from every record in whatever column you enter.

You could also make an update query with the SQL statement enclosed in the quotes

[TheShabz]

see if this link helps out.
http://www.techonthenet.com/access/f...ng/replace.php

you would replace(fieldname, "0", "") i believe, this is assuming the zeros are only at the end. if there is a fixed number of numbers, lets say 6 all the time, you could replace(fieldname, "0", "", 6, 1). if there isn't a fixed number, I'm not 100% sure.

I guess the bigger question that should be asked is why do you want to do that?

[TheShabz]

trb, thats assuming every record ends with a zero. What you'd want to do for that is encompass the whole thing in an iif statement checking to see if
Left([<YourColumnName>],Len([<YourColumnName>])-1) = "0" if so, to go ahead and if not, to leave it alone.

[trb5016]

trb, thats assuming every record ends with a zero. What you'd want to do for that is encompass the whole thing in an iif statement checking to see if
Left([<YourColumnName>],Len([<YourColumnName>])-1) = "0" if so, to go ahead and if not, to leave it alone.

Ah you're right, I thought every record had a 0 but it doesn't, so I'll add a where clause to make it more complicated haha.

CurrentDB.Execute "UPDATE [<YourYableName>] SET [<YourColumnName>]=Left([<YourColumnName>],Len([<YourColumnName>])-1) WHERE Right([<YourColumnName>],1)='0'"

[TheShabz]

oh and perplexed, use the sql that Trb provided, its much cleaner and more professional.

[Perplexed]

Thanks guys. I'll try this tomorrow since the day has gotten away from me and it's time to leave. Only the converted numbers have the 0 as the last digit. All of those do start with the letter 'I' though. Never messed with visual basic before so this should be fun.

[Perplexed]

After giving this more thought I realize that I'm probably headed for disaster since my column has valid entries that end with '0'. However none of them start with the letter 'I' as all of the ones I want to change do. This is far more complex than I anticipated. The ability to record and replay keystrokes would do this job nicely but I don't think that's possible on my work computer. I seem to recall that you could do that in early versions of Windows or maybe I'm going way back to my Wang days. I suppose that today's cyber criminals eliminated that function. I'm also surprised that Access doesn't have a macro wizard. Oh well, there's always the old fashioned way (lol), now where's that intern.

[TheShabz]

CurrentDB.Execute "UPDATE [<YourYableName>] SET [<YourColumnName>]=Left([<YourColumnName>],Len([<YourColumnName>])-1) WHERE Right([<YourColumnName>],1)='0' AND Left([<YourColumnName>],1)='I'"

That will delete the final character of all records that end with 0 and start with I

[Perplexed]

Thanks TheShabz. By the time I saw your reply I had already manually removed the zero from the records. Only took about 15 minutes to do 802 entries, quick albeit monotonous. I'm keeping a copy of your post though, I'm sure I'll need it sometime in future. Appreciate the help.

[TheShabz]

and by "I had already manually removed" you mean "I had my intern manually remove" right?

oh and, mark the thread solved please

[Perplexed]

Hey I helped, couldn't let him show me up. This was my 1st post so was unaware about marking it solved. Now to figure out how to do that. Don't tell me, I'm pretty sure that I can find it.