Link to home
Start Free TrialLog in
Avatar of Jenedge73
Jenedge73Flag for Afghanistan

asked on

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.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image


Use the Replace function

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

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

Replace([yourField], "-", "",,1)
                                           ^^   1 will replace just one occurrence.  N will replace the first N occurrences.

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

Will replace all occurences of "-" in your field.
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
Although I am aware of it, I can recall only replacing N instances.  But hey, it could happen ... and probably on EE ...

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

Jeff
Avatar of Jenedge73

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OR ... add a WHERE clause to your query which excludes Null values of [Address Book]![Certificate]

mx
Try:

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

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

SELECT Replace("" & [Certificate],"-","") AS NewField
FROM [Address Book]
Thanks