Two Spaces
By Richard Rost
3 years ago
Fix Records with Two Spaces After a Period in Access
In this Microsoft Access tutorial I'm going to teach you how to fix records where users typed in two spaces after a period. We'll use an Update Query and the Replace function.
Ethan from Romeoville, Illinois (a Platinum Member) asks: I keep my product database in Microsoft Access and with the help of your lessons I print out my catalog directly from the database. Unfortunately a lot of my data entry people are old school and have typed two spaces after a period in a lot of the different fields. Is there any way that I can go through and edit that automatically without having to manually find and replace everything? And I know it's going to happen in the future again because I've yelled at these guys a million times so an automated solution would be nice.
Members
Members will learn a completely automated one button click solution where we will count the number of records that need to be fixed and inside a loop, fix them, count again, rinse and repeat.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Fix 2 spaces after period, How do you change spacing after a period, two spaces at end of sentence
Subscribe to Two Spaces
Get notifications when this page is updated
Intro In this video, we will talk about how to fix records in your Microsoft Access database where users have typed two spaces after a period, which is a common issue with older data entry habits. We will walk through identifying affected records using a wildcard search, creating an update query, and using the Replace function to convert double spaces after periods into a single space. This tutorial focuses on resolving this formatting problem in your tables quickly and efficiently using standard query tools in Microsoft Access.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost and yes, I have a cold today, so I apologize that my voice is a little hoarse.
Today we are going to talk about how to fix records in your database where users typed two spaces after a period. Like this, right there. Like two spaces. Do not do that, two spaces.
Today's question comes from Ethan in Romeoville, Illinois. If I am pronouncing that correctly, one of my Platinum members. Ethan says, I keep my product database in Access and with the help of your lessons, I print out my catalog directly from the database. Good job. Unfortunately, a lot of my data entry people are old school and have typed two spaces after a period in a lot of the different fields. It looks like I did right here too. Yep, there are two spaces, look at that.
I use voice recognition for a lot of this, so that was not me, that was my automation. Is there any way I can go through and edit that automatically without having to manually find and replace everything? I know it is going to happen in the future again because I have yelled at these guys a million times, so an automated solution would be nice.
Yeah, you could just go through with a find and replace and fix them, whatever. But if this happens a lot, you kind of want to just be able to run a query or click a button or something before you go to print the catalog out.
For those of you who do not know, we have got monospace fonts and proportional fonts. In the old days with typewriters, they had monospace fonts. Every font, every letter took up the same exact amount of space. We were taught in typing class. I went to school in the 70s and 80s. So we were taught in typing class that you have to hit the space bar twice after a period. So it is not their fault if they are my age or older. I am 50. I learned this in about eighth grade, I think. I taught myself how to type when I was at home with my little Coco learning how to program, so I never did that. I got yelled at constantly in class to put that extra space there.
Now with proportional fonts, each letter takes up a different amount of space. So a capital W is wider than an I, and they know that. The word processors know that now, including Access, and a report will add a little bit of extra space there.
I found this quote when I was researching. It says, experts say this diminishes readability. But yeah, this is definitely easier on the eyes to read than that. This is like Courier and this is a Home or whatever the default font is.
So let us get to it. What are we dealing with here? We are going to say that this is an expert level class. I mean that expert is more than beginner, but less than developer. We are not going to do any programming today, but you are going to have to know a few things.
First, if you do not know what an update query is, go watch this. We are going to use this to actually change the values in our tables. Then we are going to use the Replace function to change the two spaces into a single space. So go watch this video if you have never used Replace before. Optionally, I want you to go watch my Like video as well. We are going to do a wildcard search so we can see how many records have that problem.
Go watch these three. I really cannot talk today. But watch these three videos. They are all free. They are on my YouTube channel and my website. Then come on back.
Here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want to. In here, we have got customers. Customers can have orders and orders can have notes. We will just use the notes field down here.
Now I added some recently, so I am going to go to my order detail table. If you scroll down to the bottom, I added some creative stuff to some of these orders. Let us add some problems. Let us make some records that have the problem.
I am going to randomly just insert a period space space. Then I am going to put one here, just period space. Yes, I know these are not actual full sentences, but this is just for class. Here we will do two of them. We will go period space space. Maybe down here at the end, after "red bar, shadow," we will do two spaces. You might have some people who accidentally put three. So I am going to do period space space space. Maybe one more down here. Period space.
So we have about five or six records that have the problem.
Now, first, let us run a query. Create a query to just see what we are dealing with, see the problem. Let us bring in the order detail table and bring in, if you want, bring in the ID and the notes field. Now notes is where we are going to put our criteria. I am going to zoom in, Shift+F2. The criteria here, I want to find all records that have a period space space anywhere inside them. So we are going to use the Like keyword. Inside quotes, we are going to put asterisk period space space asterisk, close quotes. That says any number of characters at the beginning or the end of it, and in the middle somewhere, there has to be a period space space.
Hit OK. Run your query and there are the records with the problems. There are all the records that have the problems, all five of them.
Now I am going to turn this into an update query so I can change this. This is one instance where I like to make sure that I have warnings on. I normally teach you to turn warnings off, but I am going to go into File, Options, and then under Client Settings, I want to find this right here: Action Queries. I just turned it on a minute ago when I was testing this, but make sure that Action Queries are turned on just for this example. You programmers know you can turn those on and off with DoCmd.SetWarnings.
So now, any action queries that run are going to warn me and say, oh, we are about to update five rows or whatever. I want to see that here.
Let us go back into Design View. We are going to change this to an Update Query. We are not going to change OrderID, so we can get rid of this out here. We do not need it. Now we are going to update this field to, again, I am going to zoom in, Shift+F2. We are going to update this to: Replace (that is the Replace function), Notes, comma, what do I want to look for? Inside quotes, dot space space, close quotes, comma, what am I replacing it with? Quotes dot space, close quotes, close parenthesis.
This says replace the Notes field, find dot space space, replace it with dot space.
Hit OK.
Now be careful. As soon as I hit OK, look what happened. Do you see it? Do you see the problem? Two points if you see the problem. I am going to zoom in again. You can see what Access did. Access was trying to be helpful and it turned that into a string. I do not want a string because it is going to look for the actual word Notes. I do not want that. I know I tell you that nine times out of 10, if you do not have spaces in your field names, you do not have to deal with the brackets. This is one of those one times out of ten where you do. Sorry. I wish they would change it so that if it sees that as a field name, it would leave it like that. But, put your brackets on your field name.
Now hit OK. Now we are going to run the query.
You are about to update five rows. Are you sure? Once you do this, you cannot reverse it. OK, yeah. Go ahead, update it.
Now, sometimes, as I said before, people might type in dot space space space. You are still going to have dot space spaces where before there were dot space space spaces. So we are going to run it a second time. Do it again. Then run it again. Now we are down to one row. Keep doing it until you are down to zero rows. There you go.
I have gone through and replaced all of those fields that had dot and any number of spaces after it with just a dot space.
Save your query. We will call it OrderDetailFixPeriodsQ. I try to keep all my object names singular. I do not always make it, but try to.
Now, if we close this and go back to our Order Detail table, take a look at all those records down here. You will see that there are no more dot space spaces. You can see where I inserted it right there. The dot dot spaces are gone. There is not one down there. One space. Yep. So it worked. It fixed it.
If this is something that you are doing on a regular basis, you can just keep this query here. You can add multiple fields if you want to, but you have to still run it multiple times and there is still a little bit of manual action. It is better than it was before.
If you want a fully automated solution, in the extended cut for the members, I will teach you how to fix that with just clicking a button. We will count the records with VBA. We will auto fix it with an SQL statement, rinse and repeat. Then we will loop again so that you just have to sit there and watch the computer do stuff. You do not have to check anything or worry about chemicals and molecular structures and moving parts and all those things.
Silver members and up get access to all of my extended cut videos. All of them. There are hundreds of them now.
If you want to check out the product catalog database that Ethan was talking about, here it is. It is free. Go watch this. It is a TechHelp video. I will show you how to enter all the stuff into your database and then print it out in a nice format like that. It looks all pretty and yeah, there you go.
So there is your TechHelp video for today. I hope you learned something. Live long and prosper my friends. I will see you next time. Now I am off to find some Sudafed, a cough drop, and my bed. Good night.Quiz Q1. What was the main problem discussed in the video that Ethan needed help with?
A. Users deleting important records by accident
B. Users typing two spaces after a period in data fields
C. Users pasting duplicate data into the database
D. Users formatting text in bold unnecessarily
Q2. Why did people originally learn to type two spaces after a period?
A. To make files easier to compress
B. Because of proportional fonts in computers
C. Due to the use of monospace fonts on typewriters
D. To help with computer programming readability
Q3. What is the purpose of using a 'Like' criteria with wildcards in the query?
A. To select only blank records
B. To find records where a field starts with a period
C. To find any record containing a period followed by two spaces
D. To exclude records with spaces in them
Q4. What function was used in the update query to replace the two spaces after a period?
A. Substitute
B. Search
C. Replace
D. Concatenate
Q5. What is the key syntax for the Replace function used in this video?
A. Replace(fieldname, oldtext, newtext)
B. Replace(oldtext, newtext, fieldname)
C. Substitute(fieldname, oldtext, newtext)
D. SearchAndReplace(fieldname, oldtext, newtext)
Q6. Why did Richard warn about the way Access auto-formats field names in the Replace function?
A. Access may capitalize field names by mistake
B. Access may wrap field names in double quotes
C. Access may turn field names into literal strings if not bracketed
D. Access may require every field to be in uppercase
Q7. If some records contain three spaces after a period, what must you do to fully clean the data?
A. Run the update query only once
B. Run the update query multiple times until no records are found
C. Change the query to find and replace three spaces in one run
D. Export the data to Excel to fix it there
Q8. What is the advantage of saving the update query (like OrderDetailFixPeriodsQ) for future use?
A. It increases the security of your database
B. It helps you avoid having to create the query again every time
C. It automatically runs every time new data is entered
D. It protects the database from viruses
Q9. Why does Richard suggest keeping action query warnings ON for this process?
A. To ensure the update query runs faster
B. To get a notification of how many records are being changed
C. To make Access save more frequently
D. To automatically backup the database
Q10. What is the limitation of the approach shown in the video for fully automating the clean-up?
A. It requires VBA programming for complete automation
B. It only works with numeric data types
C. It cannot update more than 10 records at a time
D. It creates permanent changes that must be undone manually
Answers: 1-B; 2-C; 3-C; 4-C; 5-A; 6-C; 7-B; 8-B; 9-B; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone tackles an issue that many of us encounter in our Access databases: how to fix records where users have entered two spaces after a period. This problem is pretty common, especially among people who learned to type on typewriters, where double spacing after a period was the norm.
This topic was brought up by one of my Platinum members, Ethan, who keeps his product catalog in an Access database. He noticed that his data entry staff frequently put two spaces after a period in various text fields of his database. While it's possible to use the standard Find and Replace tool and fix these manually, Ethan points out that this isn't practical if it happens often. He's looking for a way to automate this process so he doesn't have to keep correcting it each time before printing his catalog.
For some of you who might not know, in the age of typewriters and monospace fonts, double-spacing after a period was considered correct. Every letter took up the same amount of space, so it made sense. However, with proportional fonts, which most software uses now, word processors automatically add proper spacing after a period, making the extra space unnecessary and even less readable.
So how do we fix double spaces after periods in your Access database? To address this, you'll need to have some understanding of update queries, the Replace function, and using the Like keyword for wildcard searches. If you aren't familiar with these concepts, I recommend watching my free videos on each topic before proceeding.
Let me walk you through the procedure step by step, using the free TechHelp template database available for download on my website. In this database, let's focus on the Order Detail table and specifically the Notes field, where double spaces after periods commonly appear.
First, to see how widespread the problem is, set up a query that searches for any instance of ". " (a period followed by two spaces). Use the Like keyword with wildcards so that Access finds this pattern anywhere within the field. When you run the query, you'll quickly see which records have this issue.
The next step is to convert your query into an update query. This will let us modify the records directly. Before running the update query, ensure that you have your action query warnings turned on in Access's options. This way, Access will prompt you before making any changes, so you can double-check how many records will be updated.
In your update query, use the Replace function to search for ". " and swap it for ". ". One tricky thing to watch out for: when entering your field names in functions, Access may occasionally convert the field reference into a string. To avoid this, make sure to put square brackets around your field names so that Access recognizes them correctly.
After setting this up, run the query. Access will warn you about how many records are about to be updated. One thing to note is that some users might have typed three or even more spaces after a period. The replace action only fixes two spaces at a time, so you may need to run the update query repeatedly until all instances are corrected.
Once you've completed this process, review your table to confirm that all the double spaces have been replaced with single spaces.
If this is an issue that keeps recurring, keep the query handy and run it regularly to tidy up your data. You can also add more fields to the query if needed, but remember you'll need to run the update multiple times if different numbers of spaces are present.
For those looking for an even more streamlined, automated solution, in today's Extended Cut for members, I walk you through how to handle this with a single button click using VBA. The extended lesson covers counting the affected records, automatically fixing them with an SQL statement, and looping the process until all errors are corrected. This way, you can let your computer handle all the work for you.
Silver members and above can access all of my extended cut videos, which cover a wide range of advanced Access techniques.
If you would like to see more about creating product catalog reports in Access, I have a separate free TechHelp video that covers how to set up your product catalog database and print your data in a well-formatted report.
For a complete, step-by-step video tutorial showing you everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Identifying records with double spaces after periods
Using a Select Query to find double spaces
Setting up criteria with Like and wildcards in queries
Converting a Select Query to an Update Query
Turning on Action Query warnings in Access
Using the Replace function in an Update Query
Handling field name brackets in expressions
Running the update query multiple times to remove extra spaces
Verifying updated records in the table
Saving and naming the FixPeriods update query |
