How to Fix MS Access Remove Characters from String

Most of the time, you have a query in which some fields contain special characters. So, you want MS Access remove characters from string. However, you do not know the procedure. This article will guide you to remove the special characters from a field.

Microsoft Access is a relational database management software developed by Microsoft. It stores the information and retrieves it when required. The DBMS saves the data at a cloud location. So, you can access the files from anywhere.

Moreover, you can use several functions to edit the information as per your requirement. If you want to remove the special characters from a field, you can use these functions to eliminate the unnecessary characters.

Methods for MS Access Remove Characters from String

Apart from storing data and updating information, Microsoft Access allows you to perform various operations in the database. If you want to remove special characters from the string, you can use the Replace() function. To learn how to remove characters in Access query using this function, let us consider an example.

Suppose we have a table named UserDetails that has various fields. One of the Field names is PhoneNumber. This field contains the contact number of different users. Now, In the Phone number field, some users enter different characters other than numbers like (, ), -, ., etc.

PhoneNumber

(123)4567-891

123-654-7891

123.654.9871

So we have to remove the special characters from the string using the replace function and then update the table. The Replace function is an MS Access string function. Now follow the steps below for MS Access remove characters from string.

1. Use the Replace Function

• Click on the Create option in the top menu bar and select the Query Design icon.
• Now, select the desired table (UserDetails) from the Add Table section in the right pane.
• Choose the PhoneNumber field from the list of columns in the desired table.
• As you click on the Run button, you will see the PhoneNumber field will be selected.
• Remove the special characters one by one by using the Replace function.

P2: Replace([Phone Number],’(‘,’’)

• It will remove the ‘(‘ sign (opening parentheses) from the phone number field. Similarly, use the below functions.

P3: Replace([P2],’)’,’’) P4: Replace([P3],’-’,’’) P5: Replace([P4],’.’,’’)

• After using all the above functions, the newly created P5 field will only contain the Phone numbers without any special character.

P5

1234567891

1236547891

1236549871

• Now, save the query as CorrectPhoneNumber by pressing Ctrl+S.

2. Update the MS Access Query

After you create the new query that contains the corrected phone numbers, use the Update function to update the values in the PhoneNumber with corrected values. Follow the steps below to update the table to resolve this Microsoft Access database error.

• Click on the Create option to create a new query and then select the Query Design button.
• In the new wizard that appeared, choose the CorrectPhoneNumber table from the right pane.
• Now, click on the Update button to update the fields in the table.
• Select PhoneNumber field in the Field section and [P5] in the Update to section.
• Finally, click on the Run button. It will update the incorrect field with the correct data.
• Save the table with a particular name and use this updated query instead of the UserDetails.

You can use the above functions for MS Access remove characters from string. They are effective but using these functions has a risk of file corruption. It may damage your database files. So, to deal with such circumstances, you need a professional tool to repair the MS Access data files.

Professional Solution

MS Access Repair Tool is the ideal solution to repair corrupted database files. The tool is designed to resolve multiple database file corruption and restores the file to the original state. It helps you restore the files if they get corrupted because you used any function. This database recovery tool supports MDB and ACCDB file formats.

Conclusion

MS Access is a relational Database Management System to store and retrieve the user’s data. It also offers various functions to perform different operations in the database. One such function is the REPLACE function through which MS Access remove characters from string. Using these functions increases the risk of file corruption. Hence, Access Database Repair Tool helps you to repair the damaged Access data files. Thanks for reading. I hope you found it helpful.

Related Post