Jenedge73
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.
Replace([yourField], "-", "",,1)
^^ 1 will replace just one occurrence. N will replace the first N occurrences.
mx
^^ 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.
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
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
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
<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
:-(
Jeff
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
Replace([Address Book]![Certificate],"-",""
Replace([Address Book]![Certificate],"-",""
Replace([Address Book]![Certificate],"-",""
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OR ... add a WHERE clause to your query which excludes Null values of [Address Book]![Certificate]
mx
mx
Try:
Replace([Address Book]![Certificate] & "","-","")
I concatenated an empty string to the field to account for NULL strings
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]
SELECT Replace("" & [Certificate],"-","") AS NewField
FROM [Address Book]
ASKER
Thanks
Use the Replace function
Update yourTable Set [yourfield] = Replace([yourField], "-", "")
But remember, this will replace ALL INSTANCES of that character in the field.