Finding a Specific Character in a Field

[GriffyNJ]

I have a database i'm creating which makes a process at my job simpler that it currently is. Long story short, I need to right a function that allows me to look in a Field named accession_no. This field should be all numbers, but sometimes it has back slashes. How can I write a query that basically finds all the back slashes in this field and replaces them with a 0, or removes it all together?

 

[Mr. B]

Try the Access "Replace" function. You can replace the "/" character with either a zero or an zero length string.

Replace(StringBeingSearched, StringToSearchFor, StringToReplaceStringFound)

 

[GriffyNJ]

Ok so I tried that in an update query and it's not returning any values whatsoever. What should be my criteria. Mind you I just want to find a back slash with the value of the field. example: the accession_no has a value of 383838\9. I want to find the back slash in that field and remove it

 

[Mr. B]

Where you use the Replace() function is totally dependant on what you are doing or even how you are doing it. I'm not trying to be short with an answer, but I do not know just how you are getting to the value that you need to replace the back slash in.

If you are using a query to provide the data to a form then you can use the Replace function in your query like:

MyNumber: Replace(YourString, "\", "")

It is feasable that you could also use this in an update query.

 

[boblarson]

Ok so I tried that in an update query and it's not returning any values whatsoever. What should be my criteria. Mind you I just want to find a back slash with the value of the field. example: the accession_no has a value of 383838\9. I want to find the back slash in that field and remove it

In the update query, the update part would be

Replace([YourFieldName], "\", "")

 

[GriffyNJ]

thank you guys for your help. I tried what you suggested, and what happens is it replaces the entire string. I'm trying to simply replace the one character in the string I don't want. so for example sake, if my field ACCESSION_NO has a value 12345\6, I want to replace the the backslash so it ends up reading 123456.

 

[boblarson]

Then I think you may need:

Left([FieldNameHere],InStr(1,[FieldNameHere],"\")-1) & Right([FieldNameHere],Len([FieldNameHere])-InStrRev([FieldNameHere],"\"))

 

[Mr. B]

I tested the theory here before posting my original post. The Replace function will do exactly what you want. I am still not sure just how you are trying to use it. Can you post your query?

 

[boblarson]

I tested the theory here before posting my original post. The Replace function will do exactly what you want. I am still not sure just how you are trying to use it. Can you post your query?

Good point, I just tested as well and it does work fine. It would seem it wasn't used correctly. (I just had assumed it didn't when coming up with the other code, due to my inability to think too straight today)

 

[GriffyNJ]

Indeed you are both right! I spoke to soon, plus I left out the brackets with the field names. perfect. Thank you a great deal guys I appreciate you tremendously. I might be back, as this project is a doozie and I need to be aware of all the functions.

Thanks again guys, I'll pay the kindness forward

 

[Mr. B]

Hey, Bob, I have the same problem. Too much on my mind.

Good to see that the OP got is working.

GrifffNJ,

Come on back anytime. We will try to help.