|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Seasonal Address By Richard Rost 
5 years ago
Specify a Seasonal Mailing Address for Customer In this video, I will show you how to specify a seasonal address for your mailing lists in Microsoft Access. This is helpful if you send regular mailings, but your customers may head south for the winter. John from Hancock, New Hampshire (a Gold Member) asks: I have a customer who mails a monthly newsletter. Many people go south in the winter, so they want their newsletter mailed to another address at a specific date. I am looking for an elegant way to have Access switch the address at the appropriate time. Elegant is the goal, for I know that is what you do. MembersMembers will learn how to specify multiple seasonal addresses for any combination of dates in a separate related form.
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
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, seasonal mailing address, multiple mailing addresses, winter mailing address, multiple addresses, optional addresses
IntroIn this video, I will show you how to set up a seasonal mailing address for customers in a Microsoft Access database. We'll talk about adding secondary address fields for customers who spend part of the year in a different location, using month and day fields to specify when each address should be active, and combining those values with the DateSerial function. You'll also learn how to use calculated query fields and the IIF function to decide which address to use based on the current date, as well as some tips for handling form design and simple data validation.TranscriptWelcome 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 to specify a seasonal mailing address for customers in a Microsoft Access database. Today's question comes from John in Hancock, New Hampshire, one of my Gold members. John says: "I have a customer who mails a monthly newsletter. Many people go south in the winter so they want their newsletter mailed to another address at a specific date. I'm looking for an elegant way to have Access switch the address at the appropriate time. Elegant is the goal, for I know that is what you do." Thank you very much, John. I appreciate the compliment, and yes, let me show you a good way to specify that seasonal address. Before we get started, you should know how to use the DateSerial function. That's how you can take the components of a date like the month, day, and year and put them together into a valid Access date value. That's important. You should know how to use the Immediate If function, the IIF function. That's how you can basically do an if/then statement in a function in a query or in a form. And of course, you should know calculated query fields. There are three videos I want you to make sure you watch before continuing with this one. If you haven't watched these yet, go watch them. They're free. They're on my website. They're on my YouTube channel. I'll put links that you can click on down below in the description field below. So go click on them and watch them, then come back. Here I am in my TechHelp free template. This is a free database you can grab off my website if you want to. Again, there's a link down below. In my database, I've got a customer table, and in my customer table, I've got some address fields right there: address, city, state, ZIP, and country. What we're going to do is copy those controls, come down to the bottom here, and paste them in down here. Now we're going to change these to address2, city2, state2, ZIP2, and country2. A lot of you who've watched my other videos are going to say, "But Rick, don't you tell us not to put duplicate things of any type in a table? You don't want address1, address2, address3, address4, address5. You don't want on an order, you know, product1, product2, product3, product4. You want to use a separate related table." Yeah, that's the general rule. And if you're ever going to do more than, I say, three of something, you definitely want to put that in a separate related table. However, if it's just one or two things, I'm okay with that. If it's just address1, address2 for the seasonal address, okay. Something like phone numbers: home phone, work phone, cell phone, three max. If you're ever going to want more than that, then yes, that should be in a separate related table. But for this instance, I'll allow address and address2. That's okay. In the extended cut for the members, I am going to show you how to do multiple addresses. So we are going to peel the addresses out, the seasonal addresses, and put them in a separate table as it should be done. But this is fine if all you're ever going to want is two addresses. Again, I'll allow it. Now, we also need to specify the dates that we want to use the primary address versus the secondary address. So what I'm going to do is I'm going to add some date fields down here. This month begin, that's going to be a number. We can't use actual date fields because it's going to change from year to year. If I put in date that's like this year, like let's say I want October 1st to December 1st, I've got to specify a year. So I'm going to specify a month and day value separately. We're going to do address month begin and address day begin. So for January 1st, for example, that'll be 1 and 1. Then we'll have address month end and address day end. What we'll do in the query is put that together using DateSerial to make an actual date for this year. We'll see that in a few minutes. Since most of our customers have address1, and address1 is what most people are just going to have (one address), we're going to make these be the dates that you use the primary address. If not, if the current day falls outside of these dates, switch over to address2. I'm going to set the default value for this to be January 1st to December 31st. So if the current date falls between these dates here, use the first address. If not, look for a second address because we'll assume if these are the default values, they're not going to change it unless they're adding a second address. One thing to also take into consideration: save this and go to datasheet view and make sure all the existing records have these values in there. So, let's just copy and paste this stuff. We've got to always take into consideration existing records when we add fields. If you've got a lot of them, you can use an update query. If you don't know how to use an update query, I have videos on my website and on my YouTube channel. Look for update query. You can use that and just run it once, and it'll update everybody in the table. By default right now, everybody in the database has one address from this date to this date, and it should all be in address1. If you have one, some people don't have an address, that's okay. But now we can go ahead and put in address2s and set these dates. Let's set this up in a form. It makes it a little easier to work with and see than using the table. I generally say try not to do as much work as you can in your tables and queries and instead work with forms, especially for your end users. Your end users should all be working with forms and reports. Let's come into my customer form here, design view. Let's move things around a little bit here. I'm going to get rid of some stuff we don't need for class. Let me just get rid of these. And I'm going to take this address and slide it down just a little bit to kind of differentiate things here. We don't need these buttons right now, either. Let me just clean house a little bit here. I'm just going to make this easier for class so you can see everything. There's a primary address. We'll call this address1. For address2, guess what? We're just going to copy this stuff, paste it down below here. This will be address2, their seasonal address possibly. What's going to happen is you're going to have a date that's included inside of some other dates. Now we've got to work with a calendar here. So, we've got to work from January to December. If they have a summer home, that's from like June to September. That's easy, right? June 1st, maybe September 30th. If it's the other way around, if they winter from October through March, then you're going to want to make the address1 be whatever that middle date is that isn't splitting over the year. That's how the algorithm is going to work. So, whatever date is going to be the one that's between two dates is going to go in address1. Now, let's add those other fields on here, those date fields. Under Form Design, we're going to go to Add Existing Fields. They should be right down here at the bottom. Click, shift-click, and then we're going to drag them all up, drop them right there. I'm going to get rid of these labels here. We've got the month and the day. In the extended cut for the members, I'm going to show you how to turn these into combo boxes so you can just drop this down and pick March. For the days over here, we'll go one to 31 for March. If you pick February, we'll go one to 29. We're going to do a lot of checks and stuff like that in the extended cut. For now, we're just going to use some simple text boxes. Let me left-align these. I am going to put some labels around here. Let's copy that label. This is going to be one. I go cheap sometimes and use one label. We'll slide that over a little bit so we can put the label over to the left. Copy, paste, put you over here, and this is going to be the start or "Begin," whatever you want to call it, and the end date. Looks good. Shrink that up a little bit. Save it, close it, open it up. Looks good. This will be the address that we're using. Oh, I didn't change these. Let's change those real quick. You see, this will be the address that we're using for these dates, which by default is the whole year. If we change this to some subset of the year, then it'll default to address2 if it falls outside of that range. That's what we're going for. Let's come down here real quick and change these. I didn't change the data for these. This should be address2. Remember, they're in this drop-down box here: address2, city2, whatever. I also want to make sure I change the name of the box as well. I don't like the names being "Text30." Change each one of these: city2, state2, same for the ZIP code (or whatever you call it in your country), and country here. I leave country blank if it's a US person. If it's someone else, I'll put Canada or UK or whatever in there. Save it, close it, open it up. Let's say that this is my summer home. I'm going to put "Summer Home" here just so we can see it in the query that it's working right. Let's say I'm only here from 6/1 to 9/30. I hit tab; this is at the end, so it went to the next record. Go back. I'm not doing any checks here. I'll do some validation and checks to make sure like the start date is less than the end date and things like that in the extended cut. For now, the rest of you have to make sure that your people type in stuff that's valid. Yes, you can use VBA and verify all this information and all that stuff; that will be covered in the extended cut. The rest of the year, let's say I'm at my winter home, which is, I don't know, 123 Somewhere, Florida, and let's put this up in Buffalo, New York, so we can see they're different. Now I want to make a query that says, take a look at this date: 6/1 of the current year to 9/30 of the current year. We're going to use DateSerial to put that together. If the current date, today's date, falls inside of this range, then use this address. If not, use this address. Wherever they are in the middle of the year, in the summer months, you want to put up here. The winter address, especially if it flips, like December through January, you don't want that to be address2. If they only go to the winter address after New Year's (they stay up for the holidays to visit with family, and then after New Year, that's when we start seeing all the influx of the snowbirds; they come down from January through March or April), then you would put that up here as their winter address. If it's January 1st through April 1st, then you want that date up here, and the rest of the year is down here. This is the inclusive range. This is the everywhere else address. Let's go to queries. Create, Query Design. Let's add in our customer table. That's the only table that we're going to need right now. I'm going to bring down the star, and then we're going to need to figure out a couple of dates here. First, we're going to figure out the address begin date for this year. I'll zoom in so you can see this. This is our calculated query field: address begin date this year. What's it going to be? DateSerial (that's why I wanted you to learn DateSerial first), the year of today's date, comma, and then the address month begin and the address day begin. That works. We're making a new date called address begin date this year, and it's going to be a combination of the current year plus the address month begin and the address day begin. Let's see what that looks like. There it is. Here's my address begin date this year: 6/1/2021. It's currently December 27, 2021. The rest of these guys, all begin on January 1. Now we've got to figure out the end date for this range. Let me save this while I'm thinking about it. Save. We'll call this the customer address Q. Back to design view. Very similar. In fact, I'm going to copy this guy. Come over here, paste it in. Address end date this year is going to be the address month end and the address day end. Save it. Run it and take a peek. There's your address end date this year. Looking good. The only one we changed is that first record. Now I want to know: does today's date fall inside that range? Let's make another field called "IsBetween." This is where our IIF function comes in. If the address begin date this year is less than or equal to today's date, and today's date is less than or equal to address end date this year, if that's true, put a 1 in here, otherwise put a 0 in here. Whether you use the equal signs is up to you, depending on whether you want to be inclusive or exclusive. So if the address begin date is less than or equal to today's date and today's date is less than or equal to the end date, in other words, if today's date is between those two dates. You could use the BETWEEN keyword, but I like doing it this way. Then IsBetween is 1. Otherwise, IsBetween is 0. You could use true and false here if you like to. I'm an old school C programmer, so I like 1 and 0. Let's take a peek. Now look at that. Since today's date (it's currently December) is not between those two dates, then we're getting a 0. I like to use the greater than, equal to, less than, equal to, because if you didn't have the less than or equal to, then 12/31, this would still show you not between, because it's not including the endpoints. We've done a bunch so far. Here we've used our DateSerial function, our IsBetween, our IIF function. Now I'm going to determine what the address, city, state, ZIP, country are based on whether or not IsBetween is true or false. Ready? Address: now, if IsBetween, then use the address field, otherwise use the address2 field. I'm going to copy that because we can use it for all the rest of it: city, city2; state, state2; ZIP, ZIP2; and finally country, country2. Save it. That's why I wanted to make IsBetween, because I didn't want to have this big complicated thing inside of each one of those. That's why I made IsBetween a separate field. Run it and slide to the right. There we go. Not between, so we're going to use the winter home, which was the second address. Let's check and make sure it's saved. Yes, winter home down here, not between those dates. Let's change this and make sure. Let's say this is now. It should be between those dates. Run the query, and I should be at my summer home now. There it is. Let's make sure someone else works too. Let's put this back to 9. You see that you've got to make sure. I didn't put any data validation in here, so you have to make sure your people are typing in correct dates. You could add your own data validation. I have tons and tons of different lessons on data validation, or become a member and watch the extended cut. I'll show you how to make these combo boxes so you can pick the stuff. Let's change one more person too. Let's change Dana Troy. Let's say from 4/1 to 8/15 she's there, and elsewhere she's on Beta Set, and that's her address. I don't know what a Beta Set address would look like. Ready? Open it up, and she's on. There's her Beta Set address, and she should be there right now. Not between. So as you can see, with a couple of little fields added to the table, a couple of month/day fields, a little DateSerial, a little IIF, it's fairly easy to do two addresses. Now, what if you want multiple addresses? What if they've got three, four, or five different addresses that you might need to mail to? That adds another level of complexity, and I will cover that in the extended cut for the members. We'll do unlimited addresses so you can put two, three, ten of them in here if you want. Let's really make sure that the days are valid for the month. So if you pick June, it will show one to thirty. If you pick February, it'll show one to twenty-nine. If you pick January, it will show one to thirty-one. We'll do some basic correction to make sure that the start date is less than the end date, for example, so they don't put October through June that way. We'll make an "Add" button because sometimes seeing that bottom row down there can add confusion. But the big thing is we're going to have unlimited dates here, and we'll have to use some different techniques and some DLoops and some other stuff to get this to work. But that's all covered in the extended cut, members only. For more information on how to join, stay tuned. I'm going to tell you in just a second. Here you go. Ready? How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you've finished the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the Expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. QuizQ1. What is the primary goal when handling seasonal addresses in a Microsoft Access customer database, as discussed in the video?A. To allow customers to have multiple unrelated addresses B. To automatically switch the mailing address based on the date C. To create a large number of address fields for each customer D. To store all addresses in a single memo field Q2. What should you already know to follow the concepts in this video? A. How to write VBA classes B. How to use DateSerial, IIF function, and calculated query fields C. How to create pivot tables in Access D. How to import data from Excel Q3. Why does Richard permit having address1 and address2 fields in the customer table in this scenario? A. Because normalization does not matter for addresses B. Because handling two addresses in one table is acceptable if it's only for one or two fields C. Because Access does not support related tables D. Because customers never move Q4. When should you consider creating a separate related table for addresses instead of just address1 and address2 fields? A. Only if you have more than three of an item (like addresses) B. Only if you want to include phone numbers C. For every single customer record D. Never, it is not recommended Q5. Why do we use separate fields for month and day rather than a single date for seasonal addresses? A. Because the year component will change every year B. Because Access does not support date fields C. To allow unlimited addresses D. For sorting purposes only Q6. What is the default range set for address1's start and end dates? A. February 1st to October 1st B. January 1st to December 31st C. June 1st to September 30th D. December 1st to March 1st Q7. What should you do after adding new date fields to an existing customer table to ensure data consistency? A. Ignore previous records B. Use an update query to set default values for existing records C. Delete all old records D. Rely on the system to update automatically Q8. Where should end users ideally interact with the database to manage addresses according to the video? A. Directly with tables B. With forms designed for this purpose C. With the raw SQL window D. Through reports only Q9. How is the calculation for "address begin date this year" achieved in a query? A. By concatenating all address fields B. By using DateSerial to combine current year, month, and day fields C. By using only the month number D. By referencing a fixed date Q10. What does the calculated field "IsBetween" indicate in the query? A. Whether the customer has two addresses B. Whether today's date is within the seasonal address range C. Whether forms are being used D. Whether the ZIP code matches the state Q11. What Access function is used in a calculated query field to perform the conditional "if-then" logic? A. DateAdd B. IIF C. DLookup D. Sum Q12. Why is the "IsBetween" field calculated separately before using it to determine which address to mail to? A. To make the query more readable and manageable B. To use fewer database resources C. To avoid using any functions D. It is required by Access Q13. What logic determines which address to use for mailing the newsletter? A. Always use address2 if it exists B. Use address1 if today falls between the start and end dates, otherwise use address2 C. Always use address1 unless address2 is not blank D. Alternate addresses every month Q14. What does Richard recommend doing about validating dates (like making sure the day is valid for the month) in the basic free version of the lesson? A. Add extensive VBA validation immediately B. Do not worry about it; it is unnecessary C. Rely on users to type correct values D. Use macros for all validation Q15. Which technique is suggested in the extended cut (not covered in the main video) for handling an unlimited number of addresses? A. Add more fields for each extra address B. Use a separate related table for addresses C. Store all addresses in one long text field D. Discourage customers from changing addresses Q16. What is a practical use for default values in the month and day fields for primary addresses? A. To eliminate the need for secondary addresses B. To ensure that existing customers retain their default address throughout the year C. To display a calendar control D. To assign a random address automatically Q17. What membership level allows access to download all the sample databases from TechHelp videos? A. Bronze B. Gold C. Silver D. Platinum Answers: 1-B; 2-B; 3-B; 4-A; 5-A; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-A; 13-B; 14-C; 15-B; 16-B; 17-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. SummaryToday's video from Access Learning Zone focuses on how to manage seasonal mailing addresses for customers using Microsoft Access. This question originally came from a member who needed a way to automatically switch a customer's mailing address based on the time of year, since many people relocate for the season.Before getting started, it's important to be familiar with a few key Access functions: DateSerial, which lets you construct dates from year, month, and day components, and the Immediate If function, IIF, which performs IF-THEN logic in queries and forms. Understanding how to use calculated fields in queries is also helpful. If these topics are new to you, I encourage you to visit my website or YouTube channel and watch the recommended free lessons first. In my demonstration, I use the free TechHelp template database, which you can download from my website. The template includes a customer table with basic address fields: address, city, state, ZIP, and country. To prepare for seasonal addresses, I duplicate these fields as address2, city2, state2, ZIP2, and country2. While the best practice in Access is usually to avoid repeating fields and instead use a related table for things like multiple addresses, I make an exception in this case because most users will only ever need to track two addresses per customer. If you need more than that, a separate related table is preferred. In the Extended Cut for members, I demonstrate how to set up the more advanced version with unlimited addresses, but for two addresses, this approach works well and keeps things simple. Next, I add the ability to specify which dates each address should be used. Rather than storing full dates, I use four numeric fields: address month begin, address day begin, address month end, and address day end. These store the months and days when the primary address is valid. Using this method allows the same setup to work year after year without tying the rules to a specific year. The default values are set to cover January 1 to December 31, ensuring that by default, only one address is in use for all records unless a seasonal address is specified. When adding these new fields to your table, be sure that existing customers are given the correct default values, either manually or by running an update query. Forms are better suited than tables for entering or editing this information, especially for your end users. I adjust the customer form for clarity, adding in the new fields for the second address and for the date criteria. For now, these are basic text boxes, although in the Extended Cut, I cover converting them to combo boxes for easier and more accurate data entry, limiting days to valid values for each month and making the interface more user-friendly. Setting up this system requires you to carefully decide which address is considered the "in-season" address and which is the "out-of-season" or backup. For example, if someone vacations in the summer, their main address is used from June to September, with their secondary address used the rest of the year. If their seasonal period crosses into a new year, such as wintering from October through March, you'd select the date range that doesn't split across December and January to be the primary range. Once the tables and forms are updated, I demonstrate how to create a query that checks which address should currently be used. This involves calculating two new date fields—address begin date and address end date—for the current year using DateSerial. With this setup, you can compare today's date to those values. An IsBetween field, calculated using IIF, determines whether the current date falls within the primary address period. If it does, the system uses the main address fields; if not, it uses the secondary set. It's important to validate that dates are entered correctly, though in this basic version, no validation is performed. If you need stricter data validation, I have several lessons covering this topic as well. In the Extended Cut for members, I include more robust features like validation, combo boxes for selecting months and days, and the ability to manage unlimited addresses using related tables and more advanced techniques. After setting up the query, the system can dynamically select the appropriate address depending on the current date and each customer's seasonal settings. This approach keeps the structure simple for cases where only two addresses are needed but can scale up to more complex requirements with related tables. If you want to see the complete step-by-step process, I have a full video tutorial covering everything demonstrated here on my website at the link below. Live long and prosper, my friends. Topic ListAdding seasonal address fields to a customer tableDetermining when to use a second address vs a related table Adding month and day fields for address usage periods Setting default values for seasonal address dates Updating existing records with new default values Creating a form to manage addresses and seasonal dates Designing form controls for secondary address fields Arranging form fields and labels for clarity Using DateSerial to build addresses' start and end dates Building calculated fields for current season evaluation Constructing an IIF statement to check if a date is in range Using conditional logic to select which address to use Outputting address data dynamically in a query based on date Testing different seasonal date ranges in records for validation Explaining inclusive vs exclusive date logic in queries Duplicating query logic for city, state, ZIP, and country fields Using the BETWEEN logic to toggle between addresses in queries |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access seasonal mailing address, multiple mailing addresses, winter mailing address, multiple addresses, optional addresses select case PermaLink Seasonal Address in Microsoft Access |
