Removing "-" character using Access Query

Is there any way to remove a single character from a field using a query?   The character set randomly within the string.
Jenedge73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:

Use the Replace function

Update yourTable Set [yourfield] = Replace([yourField], "-", "")

But remember, this will replace ALL INSTANCES of that character in the field.

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Replace([yourField], "-", "",,1)
                                           ^^   1 will replace just one occurrence.  N will replace the first N occurrences.

mx
                                           
0
mbizupCommented:
UPDATE YourTable
SET YourField = Replace(YourField, "-","")

Will replace all occurences of "-" in your field.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
Joe,

Learn something new every day.  I'm sure I knew this at some point in the past, but I don't think I have ever used the Count or Compare arguments that are available with the Replace function.  Thanks for the reminder.

Dale
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
For reference - see image.

mx
Capture1.gif
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Although I am aware of it, I can recall only replacing N instances.  But hey, it could happen ... and probably on EE ...

mx
0
Jeffrey CoachmanMIS LiasonCommented:
Note that if your ultimate goal is to actually "Remove" the "-", from the field:
<Removing "-" character using Access Query>
<way to remove a single character>

...you can run an update query similar to this:

UPDATE YourTable SET YourTable.YourField= Replace([YourField],"-","")


JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
Sorry, Ignore my post, ...already been covered by previous experts.
:-(

Jeff
0
Jenedge73Author Commented:
I don’t want to update the field, I only want to remove "-"in the query.   The field I want to remove the character from is a text field.   This is what I have tried so far along with the error message:
Replace([Address Book]![Certificate],"-","")  Data Mismatch in criteria expression
Replace([Address Book]![Certificate],"-","",,1) the expression you entered contains invalid syntax
Replace([Address Book]![Certificate],"-","",,N) the expression you entered contains invalid syntax
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

Try this:

IIF (Not IsNull([Address Book]![Certificate]), Replace([Address Book]![Certificate],"-","") , Null)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OR ... add a WHERE clause to your query which excludes Null values of [Address Book]![Certificate]

mx
0
Dale FyeCommented:
Try:

Replace([Address Book]![Certificate] & "","-","")

I concatenated an empty string to the field to account for NULL strings
0
mbizupCommented:
Is Certificate a field in the Addressbook table:

SELECT Replace("" & [Certificate],"-","") AS NewField
FROM [Address Book]
0
Jenedge73Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.