Many datasets can be somewhet confused by the time you get them. Maybe you had no control of the export from the database or maybe you asked for the right information and it came back somewhat warped.
SQL has powerful fuctions that can pretty much clean things up however you would like.
We can use multiple SQL commands within an MS Access module to clean up a source by placing them consecutively within a module here is the structure of some of the queries that I use.
UPDATE SELECTED FIELDS BASED ON A MATCHED STRING IN ANOTHER FIELD
SQL that updates Town and PostalTown fields based on a string in an aggregated PostalAdd field.
Please note that _ sign denotes a movement to another line within the VB Module required to make the SQL String run correctly. This may require alteration if you are cutting and pasting from this page.
Dim SQL As String SQL = "UPDATE Table01 SET Table01.Town = 'Barassie', Table01.PostalTown = 'TROON' " & _ "WHERE (((Table01.PostalAdd) Like '*Barassie, TROON*'));" DoCmd.RunSQL SQL
CONVERT A STRING FIELD TO ALL CAPITALS, ALL CAMEL CASE OR ALL LOWER CASE
The following SQL converts the street field of Table01 to all capitals. This could be run like the previous SQL from within an MS Access module
Dim SQL1 As String SQL1 = "UPDATE Table01 SET Table01.Street = StrConv([Table01].[Street],1);" DoCmd.RunSQL SQL1
In the above code change the trailing number parameter to select type of alteration
1 – ALL CAPIALS
2 – all lower case
3 – Camel Case
PARSE OUT LEFT PART OF STRING BY LOCATING UNIQUE CHARACTER OR STRING
This looks to the Yourfieldname field of TableRainbow and searches from the left for a comma and returns everything to the left into a field called LeftParse
Dim SQL2 As String SQL2 = "SELECT Left$([Yourfieldname],InStr(1,[Yourfieldname],",")-1) AS LeftParse FROM TableRainbow;" DoCmd.RunSQL SQL2
PARSE OUT RIGHT PART OF STRING BY LOCATING UNIQUE CHARACTER OR STRING
If you have a string with commas this string will count the length of the string then count the number of characters to your unique string – in this case a comma – and then return all characters from that string to the right of that comma.
The below code looks to the Yourfieldname of TableRainbow counts its length and then find the first comma from the right and returns the information as a select query result in a field named Right Parse. It should be noted that it searches through the target field searching from the left. IF there are multiple commas then it will stop counting when it hits the first comma. You can substitute the right part of the function with a number.
Dim SQL3 As String SQL3 = "SELECT Right$([Yourfieldname],Len([Yourfieldname])-InStr(1,[Yourfieldname],",")-1) AS RightParse FROM TableRainbow;" DoCmd.RunSQL SQL3