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 FyeOwner, Developing Solutions LLCCommented:

Use the Replace function

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

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

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
                                           
mbizupCommented:
UPDATE YourTable
SET YourField = Replace(YourField, "-","")

Will replace all occurences of "-" in your field.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
For reference - see image.

mx
Capture1.gif
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
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
Jeffrey CoachmanMIS LiasonCommented:
Sorry, Ignore my post, ...already been covered by previous experts.
:-(

Jeff
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

Try this:

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

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
Dale FyeOwner, Developing Solutions LLCCommented:
Try:

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

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

SELECT Replace("" & [Certificate],"-","") AS NewField
FROM [Address Book]
Jenedge73Author Commented:
Thanks
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.