Rx_
Nothing In Moderation
- Local time
- Yesterday, 17:00
- Joined
- Oct 22, 2009
- Messages
- 2,803
After importing data from yet another spread sheet into an Access table, it is necessary to clean up the data.
In this case on a large number, the data sometimes had a couple of spaces, sometimes had a couple of dashes, then there was the random space and dash combo.
Step 1. Create a new Query, add the table with the imported data.
Step 2. Add the field with the data (that has dash, space combinations).
in this case, the field name is API
Step 3. in Query Tools Design - change the query to an Update query
Step 4. in the Update Query's Update To:
Replace(Replace([API]," ",""),"-","")
Note: [API] is the same name as the field.
Step 5. Run (!) the update query
There were four records out of my 89,200 that had blank for the API number. The Query will display a dialogue to warn you that the 4 blanks could not be updated.
Step 6. In Query Tools Design, change the Update Query back to a Select Query. This is to prevent running it a second time.
Step 7. Run the select Query.
Step 8. Check out the results.
In this case on a large number, the data sometimes had a couple of spaces, sometimes had a couple of dashes, then there was the random space and dash combo.
Step 1. Create a new Query, add the table with the imported data.
Step 2. Add the field with the data (that has dash, space combinations).
in this case, the field name is API
Step 3. in Query Tools Design - change the query to an Update query
Step 4. in the Update Query's Update To:
Replace(Replace([API]," ",""),"-","")
Note: [API] is the same name as the field.
Step 5. Run (!) the update query
There were four records out of my 89,200 that had blank for the API number. The Query will display a dialogue to warn you that the 4 blanks could not be updated.
Step 6. In Query Tools Design, change the Update Query back to a Select Query. This is to prevent running it a second time.
Step 7. Run the select Query.
Step 8. Check out the results.
