Is there a simple way of cleaning this up so I am only left with the email address? In other words is it possible to create an update query to remove everything between # signs (including the # signs)?
---- I found some vba code on the forum which extracts strings but was wondering if there is an easier way just using an update query since I'm still not too efficient with vba
Is there a simple way of cleaning this up so I am only left with the email address? In other words is it possible to create an update query to remove everything between # signs (including the # signs)?
---- I found some vba code on the forum which extracts strings but was wondering if there is an easier way just using an update query since I'm still not too efficient with vba
Create a New Query, select Design Query.
From the Tables dialogue box, select the table with your email_address field in it. (I have called it Table1 for this example)
Right click the title bar, and select Query Type -> Update Query.
Again, right click the title bar of the query, this time select SQL view.
Enter the following code:
UPDATE Table1 SET Table1.email_address = Left$(Table1.email_address,InStr(1,Table1.email_address,"#",0)-1);
You may also enter a WHERE clause if you need to:
UPDATE Table1 SET Table1.email_address = Left$(Table1.email_address,InStr(1,Table1.email_address,"#",0)-1)
WHERE Table1.ID = 1;
To explain.
Left$ required the parameters (string, length)
So what you are passing is the email_address field, plus the number of characters to keep to the left. ie Left$("Mr.K", 2) = "Mr"
InStr returns a character position within a string using the parameters (start character position, string, search_string, match_type):
ie InStr(1, "Mr.K", ".", 0) returns a character position of 3 for the "."
For further info, match_type of 0 performs a text comparison.
If you search for the first # position in your email_address field, it will return its character position. You do not want to include this as part of your Left$ command, so we trim it off by taking the position and subtracting one.
Exit the query and save it.
You should notice that the query icon has a 'pencil and screwdriver' icon associated with it.
Double click this and it will attempt to update your records.
Read the warnings before accepting as it will update all records that match your SQL statement criteria.
Thank you for the solution and the explanation. I was able to clean up some other stuff with this function too. (function Replace saved me some time too)
