Default Value
By Richard Rost

5 years ago

How to Set a Field's Default Value in Access

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this video, I will show you how to set a default value for new customers so you don't have to type in data that's the same for most of them. If most of your new clients are locals, for example, you don't have to keep typing in city, state, and ZIP code. I'll also show you how to set a default value for the date to equal today's date.

Joanna from Key West, Florida (a Gold Member) asks: About 99% of my customers are locals. Is there any way to make it so I don't have to type in the City, State, and ZIP Code every time I get a new customer?

Members

Members will learn how to use a form footer to provide that default value. This way if you want to enter a bunch of new records from, say, Florida, and then a bunch of new records from New York, you can set the default value in the footer, enter those records, then set the next one, and so on.

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!

Links

ZIP Code Lookup: https://599cd.com/ZIPCode
Access Beginner 3: https://599cd.com/ACB3

Learn More

FREE Access Beginner Level 1
FREE Access Quick Start in 30 Minutes
Access Level 2 for just $1

Free Templates

TechHelp Free Templates
Blank Template
Contact Management
Order Entry & Invoicing
More Access Templates

Resources

Diamond Sponsors - Information on our Sponsors
Mailing List - Get emails when new videos released
Consulting - Need help with your database
Tip Jar - Your tips are graciously accepted
Merch Store - Get your swag here!

Questions?

Please feel free to post your questions or comments below or post them in the Forums.

Subscribe to Default Value
Get notifications when this page is updated

Intro

In this video, I will show you how to use the Default Value property in Microsoft Access to save time when entering data, such as automatically filling in city, state, and zip code information for new customer records. We will look at setting default values at both the table and form level, discuss using date functions like Date() and Now() for fields such as "customer since," and talk about when it's best to set defaults at the table versus in a specific form.

Transcript

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's video, I'm going to show you how you can stop entering repetitive data all the time by using a default value. If most of your customers are locals, for example, you can stop entering the city, state, and zip every time you get a new customer. That's what you use a default value for. You can still change them if you want to, but Access will populate new records with those values.

Today's question comes from Joanna in Key West, Florida, one of my Gold members. Joanna says, about 99% of my customers are locals. Is there any way to make it so I don't have to type in the city, state, and zip code every time I get a new customer?

Yes, Joanna, for this we can use something called a default value. Let me show you how to set it up.

Joanna, here's a copy of my TechHelp free template. You can download a copy of this from my website if you'd like to. In here, I've got a customer list, and you can double-click on one of these guys and it will open up the basic customer form.

Now, I see what you're saying. If you go to add a new customer, you're in Key West, so you've got one zip code, 33040. What you want, if 99% of your customers are local, is to have this city, state, and zip filled in with the Key West city, state, and zip code. Country I would leave blank. If I'm in the United States and most of my customers are from the US, I just leave my home country blank. That's for customers from other countries.

So let's go back to the table. Here's the customer table right here. Right-click, design view, and find the field you want to change. Here's city. Come down here where it says default value right there. Now type in what you want for a default - Key West - and then hit tab. It should put it inside quotes for you.

Go to state, do the same thing - FL. Zip code is right there. Now, you're lucky you've only got one zip code for most of your locals. I'm here in Fort Myers, we've got a bunch of zip codes. But you could put in 33040. I know I grew up in a small town, Hamburg, New York, and we only had one zip code there.

Save your changes - Control-S. Now if you go to datasheet view, you can see right there, there are your default values. You can change these. These are just defaults; these will come in on new records.

Now, it should come in anywhere you have those fields in a form, too. See, I go to a blank new record here in the table, and there it is - Key West, Florida, 33040. In fact, I do not think the customer list has this. Oh, yeah, here we go. State is right there.

I recommend you don't add new records to the customer list form. You can; I have it set so you can. But you can't enter in the city with a zip code there. So that's it - nice, short, and sweet.

You might also want to set up a default value for something like "customer since." You might want today's date to fill in there for a new customer. So again, go back to the table, design view, find the customer since field, and come down here for default value.

Now, today's date is going to always constantly change. You can't put an actual date in there. So you're going to use a date function. There are two functions you can use - Date and Now. Date is today's date at midnight, so there's no time portion. The Now function puts the date and time. If you care about the time of the day that this person became a new customer, which you might, if you have a walk-in business, a deli counter, you might want to know what time of the day most of your customers come in. In that case, you can put =Now in there. That is =Now with open and close parentheses around it. I'll zoom in so you can see it. That's =Now().

Or, if you just want the date, you can go =Date if you don't care about the time portion.

I'll save that and close it. Now, when I go to a blank new customer, there you see it right there, 3/7/21 - that's today's date.

You can also set default values in forms if you don't want to do it at the table level. For example, you come in here - family size. Under the data tab, there's default value. That's handy if you might have multiple forms that are based on the same table. You might have a form for managers and a form for everybody else, like your secretary. Maybe their default value for a credit limit is $1,000, but the managers might want a default value of $10,000. You can change that if you want to. But if it's something that affects everybody evenly, I recommend putting it in at the table; then you don't have to worry about putting it in all your forms, too.

Want to learn more about default values? In the extended cut for members, I'll show you how to change the default value on the fly by creating a field in the form footer that has the default value in it. Then, when you create new records, it will use that default value.

For example, let's say you're entering in a bunch of customers. You've got a bunch from New York, a bunch from Florida, a bunch from California. You enter the bunch from New York, then change the form footer - now it's California. Now, the next bunch of records that you add will all be California.

So we're going to put the default value in the form footer, and that will be the default value's default value. See that?

Same thing with customer since. Let's say we're entering in last Wednesday's questionnaires. These ones are all from 3/2. So you type them all in. Now we have last Friday's, and it's 3/4, whatever. You enter all those in without having to type in that extra data. That's getting the default value from a form field. I'll show you that in the extended cut.

Silver Members and up get access to all of my extended cut videos. As of right now, there are over 100 of them, so there's tons of stuff to watch if you become a Silver Member.

I cover a lot more about the default value property in my Access Beginner Level 3 class. In addition to default values, I also cover input masks, the format properties, and all kinds of other stuff. That's Access Beginner Level 3. I'll put a link down below the video for you.

How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver Member and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more.

All of my members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use.

Platinum Members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and are not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.

Don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more.

If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all.

Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.

YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list.

If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel.

If you like Level 1, Level 2 is just one dollar. It's also free for all members of my YouTube channel at any level.

Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.

Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.

Thanks for watching this video from AccessLearningZone.com.

Quiz

Q1. What is the main benefit of using a default value in Microsoft Access as explained in the video?
A. It enforces strict data entry for all fields
B. It makes forms look more attractive to users
C. It pre-populates fields in new records with frequently used values
D. It deletes old unused records automatically

Q2. In the example provided in the video, what default values does Joanna want to set for her local customers?
A. Phone number and email address
B. City, state, and zip code
C. Customer name and address
D. Country and area code

Q3. Where does Richard recommend setting default values if the value should apply to all forms and users?
A. Only in the report design
B. At the table level in design view
C. In the query builder
D. In the VBA code of each form

Q4. What is the main advantage of setting a default value at the table level rather than just on a form?
A. Makes the database open faster
B. Prevents the value from being changed by users
C. Ensures the default is applied everywhere that table is used
D. Allows users to enter multiple values at once

Q5. Which default value function would include both the date and time in Access?
A. =Date()
B. =Time()
C. =Today()
D. =Now()

Q6. If you want only the current date without the time portion as a default value, which Access function should you use?
A. =Today()
B. =Date()
C. =Now()
D. =CurrentDateTime()

Q7. Why might you set a default value for the "customer since" field to today's date using a function?
A. To track when a customer record was last updated automatically
B. To format the output of reports
C. To automatically record the date a new customer was added
D. To calculate customer age

Q8. How can you have different default values in different forms for the same table field?
A. Set the default value in the form's field property
B. Change the default value at the report level
C. Use VBA code to set the table default value only
D. This is not possible in Access

Q9. What method is shown in the extended cut for changing default values "on the fly" for batches of new records?
A. Using queries to group records
B. Placing a field in the form footer to set the default value dynamically
C. Creating a new table each time
D. Editing the design of the table for each batch

Q10. Which membership level gives you access to extended cut videos and sample databases as mentioned in the video?
A. Bronze Member
B. Guest Member
C. Silver Member and up
D. Standard Free User

Q11. What does Richard recommend if you want to receive email notifications of new videos?
A. Enable notifications in your browser settings
B. Join the mailing list
C. Follow on Instagram
D. Subscribe to the RSS feed

Answers: 1-C; 2-B; 3-B; 4-C; 5-D; 6-B; 7-C; 8-A; 9-B; 10-C; 11-B

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 video from Access Learning Zone covers how to use default values in Microsoft Access to save time and reduce tedious data entry. I will demonstrate how setting up default values can help you avoid typing the same information over and over again, especially if most of your customers live in the same area. For example, if the majority of your customers are local, you can have Access automatically fill in the city, state, and zip code fields with the typical values. Of course, you can still override these defaults when necessary.

This idea came from a question about how to make entering new customers faster when almost everyone is from the same place. The solution is to set default values for fields like city, state, and zip code. I will show you exactly how to configure these defaults.

Using my free TechHelp template as an example, I walk through accessing the customer table, switching to design view, and setting the default values for the city, state, and zip fields. For instance, if you want "Key West" to be the default city, you simply enter that as the default value. The same goes for state and zip. These settings ensure that whenever you create a new record, these fields will be prefilled, saving you effort on every new entry. You can always modify these values for customers who are not local.

I also discuss using default values for fields like "customer since." If you want today's date to auto-populate when entering a new customer, you can use built-in date functions. The Date function enters the current date, while the Now function includes both the date and time. So, if it is important for you to know exactly when a customer was added, you can use the Now function. Setting these defaults is as simple as updating the field's properties in design view.

While I recommend setting defaults at the table level so they work across all your forms, you can also set default values on individual forms. This can be helpful if you have different forms for different roles, such as managers and clerks, who might need different default values for things like credit limits.

In the Extended Cut for members, I explain how to make default values more flexible by using a form footer field to control the default value. This is very useful if you are adding groups of customers from different areas—say a batch from New York, then another from California. By changing the value in the footer, you can update the default for each new batch of entries without needing to go back to the table design each time. The same approach can be used for fields like "customer since" if you are entering records from a specific date.

Silver Members and higher get access to all Extended Cut videos, and there is a large collection available already. In my Access Beginner Level 3 course, I discuss default values in even more depth, along with topics like input masks and formatting options.

If you want to become a member and get access to all the bonus content, sample databases, and my code vault, it is easy to join. There are several membership levels, each with their own benefits, including access to full courses on Access, Excel, Word, and more for Platinum Members.

Free TechHelp videos will always be available, so you can continue learning even if you are not a member. If you found this video helpful, I would appreciate it if you gave it a thumbs up and left a comment. Be sure to subscribe to my channel for updates on new videos, and check out the links below for additional resources, related videos, and free lessons. Since YouTube no longer sends email notifications for new videos, I recommend joining my mailing list if you want to stay informed about updates.

If you are new to Access, I have a free Access Level 1 course that covers all the basic database concepts. You can find this course on my website or YouTube channel. Level 2 is inexpensive or free for all channel members.

If you have a question you would like answered in a future video, visit the TechHelp page on my website to submit your inquiry.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Using default values in Access tables to reduce repetitive data entry
Setting default values for city, state, and zip fields
Configuring default values for fields in table design view
Assigning today's date as a default value using Date and Now functions
Choosing between Date() and Now() for date and time defaults
Setting default values at the form level
Comparing default value settings in tables vs. forms
Recommendations for best locations to assign default values