|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
IIF Function for Future Dates By Richard Rost 
6 years ago
IIF Function to Return Future Dates Based on Criteria Nancy from Clarksville TN asks: I'm having trouble using the DateAdd function with the IIF function. I've got a field named [Exposure Type] which could be "Positive" or "Contact". If the exposure is Positive, I need a date 11 days after the exposure date. If Contact, I need 15 days. How can I do this? MembersThere is no Extended Cut for this video. LinksIIF Function: https://599cd.com/iif
IntroIn this video, we will talk about how to use the IIF function in Microsoft Access to calculate future dates based on different criteria from your database fields. You will see how to set up tables and queries to return a follow-up date by checking whether an exposure type is "Positive" or "Contact", using both calculated fields and queries. I will also discuss best practices for data structure, including using related tables and avoiding storing calculated values in your tables.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, we are going to use the If function, the Immediate If function, to return future dates based on some criteria.Today's question comes from Nancy from Clarksville, Tennessee. She is not a member, but you see I still do answer questions from non-members from time to time, especially when it comes from doctors and nurses and people helping to fight the pandemic. Nancy says: I am having trouble using the DateAdd function with the If function. I have a field named Exposure Type, which could be Positive or Contact. If the exposure is Positive, I need the date 11 days after the exposure date. If it is Contact, I need 15 days. How can I do this? Well Nancy, let me set up a little database here and show you how to do this. All right, here is my simple database template. Let me set up a table just like yours. I am going to put in here my ID, which is my AutoNumber. Every table should have an ID. I will just put in here FirstName, Short Text. I will put in Exposure Date. Now in my classes, I recommend you do not use spaces in your field names. So I would never make a field called Exposure Date. I would make it like that. But just to keep with what you have going on in your database, I will leave it like that for you. All right, that will be a Date/Time field. Now here is the thing. There are many different ways you can set this up. You want to put in here your follow, let us call it the Follow Up Date. You did not give me a name for whatever field you wanted. This is what we call it. I will call it the Follow Up Date. Now you can store this in the table using what is called a calculated field. I personally do not like using calculated fields. I prefer doing this in a query, which I will show you in a minute. But I will show you how to do it in a table just in case this is how you have your database set up. Now the nice thing about the way Access treats dates is that you do not have to use the DateAdd field if you are working with whole days. A value of one in Access equals one day. So if you want to value 11 days in the future, you just say plus 11. So if I wanted this to be the exposure date plus a week, I can say right here equals Exposure Date. And see, this is one of the reasons I do not like using spaces in my field names because if I did not have spaces in my field names, I would not have to use those square brackets. Plus 7. All right, just put that in there. Hit OK. All right, you have got your exposure date plus 7. All right, save this. Now you call your table Exposures. Exposures. I prefer naming any of my tables with T's and I like keeping them all singular. But again, that is just me. We will call it Exposures. All right, no Primary Key defined. Say yes, that makes our AutoNumber our Primary Key. And now when I open up my Exposures table, if I put in here Joe was exposed on 1-1, it gets the date automatically. All right, so that is one way you can do it. Now you have two criteria. You want to use an If function. This I just wanted to show you, plus 7 gives you one week in the future. All right, let us go back in here, Design View. Now you have got an Exposure Type field as well, all right, Exposure Type. So let us add that in here, Exposure Type. And you have got this, I am assuming, stored as a text value because you have got Positive or Contact. Again, I would not store it that way. I would use a related table and have either a 1 or a 2 in this field, but that is a whole different ballgame. I am going to assume you do not know much about database normalization and related tables, so we will just work with the data that you have got. It is not the best way to build it, but we will teach you what you need to do to get your database working. So your Exposure Type in here, you said, can be two values. You have Positive and you have Contact. We will put someone else in here in this value down here. So we will put Sue in here on 2-1. And you want the exposure data to change based on this over here. Let us go back into Design View. So your Follow Up Date, we will move this down to the bottom now. I like to keep all the calculated stuff at the end and my queries. I do not usually do calculated fields in tables. Millions of reasons why. I will not get into it in this video. So down here, we are going to say if the Exposure Type, and you can just pick from the list here if you want to, if the Exposure Type equals Positive, what do you want your Exposure Date or your Follow Up Date to be? That is going to be 11 days, so just like that, the false part. If it is not Positive, we can do Exposure Date plus 15. If you only have two values like this, Positive and Contact, you only have to deal with whether it is Positive or not. Now if you had five or six different values in here, you would have to use either nested If functions, which become a whole lot more complicated or I would recommend using a second table. But this should work for what you want it to do. Let us close this, save changes, go back into the Exposure table. You can see already now this is working. Exposure date of 1-1, Positive means 11 days in the future, that is on the 12th. 2-1 and Contact gives me 15 days in the future. It is on the 16th. So this should get you working right here for what you want to do, and you can stop watching the video now if this makes you happy. However, let me show you how I would set this up properly. I am going to close this table. The first thing I am going to do is set up the table, create a table design. I have got my ID. That will be my AutoNumber. Again, the patient's info, I will just put First Name in here. We will put in here Exposure Date. That will be a Date/Time. Again, no spaces. I am going to put Exposure Type in here, but I am going to make that a number. Now with only these two values, I am just going to use 1 and 2. If you have multiple different types, you can set up another table and relate them together. But since we only have the two, 1 and 2 is just fine. I do not like putting text in there because when you have got text in your table, what if someone does not spell Positive right? Now it is not going to work. This is what is called normalizing your data. You should not have repetitive copies of data in here - the word Positive, for example. This should be a value, 1 or 2. If you are sure you are only ever possibly going to have two values, use a Yes/No value. In fact, for this example, that is probably even better. Let me put this back to Positive here. Instead of that, let us do Positive. That will be a Yes/No value. See, if they are Positive, if they are not, True, or we will say Yes and No. Yes equals Positive, and then we will say No equals Contact only, or something like that. If you think there is a possibility you might have other fields in here, then do not use Yes/No. Use a Long Integer, value 1, 2, 3, and you set up another table with what these values mean. One is Positive, two is Contact only, three is Negative, whatever. That is an exposure-ish table, so I assume this is all people that have been exposed. Now, I do not store the Follow Up Date in this table. I am going to use a query to do it. Why? You do not want to store calculated values in your tables ninety-nine percent of the time. There are some exceptions. This is not one of them. So, we are going to save this. I will call this my ExposureT. I like to annotate tables with T. Why? Watch my free Access 101 class when you have some time. I know you are busy. I know doctors and nurses are very, very busy people. But I have got a three-hour long, free Access beginner tutorial, and I talk about all the reasons why I do things the way I do. I am going to use the numeric key, yes. So now, in my ExposureT table, this is what it looks like. I have got Joe, Sue, Bill. Exposure date 1-1, 2-1, let us put 3-1 for Bill. Actually, no one I think was exposed in here in January, but anyways. Positive there and Positive there. Save that. Close it up. Now, how do I calculate my Follow Up Date? We are going to make a query. Create Query Design. I am going to bring over my ExposureT table, just like this. Down here, I am going to bring in the star that brings in all the fields down here. I am going to make a calculated query field called FollowUpDate. And that is going to be the same thing we had before. If, now here we have a single value called IsPositive. I am just going to say If IsPositive, the default is equals true, so just If IsPositive, ExposureDate plus 11, ExposureDate plus 15. And that is that easy. I will zoom in a little bit for you so you can see it. FollowUpDate: If IsPositive equals true, then ExposureDate plus 11, ExposureDate plus 15. When you save your query, I will call it ExposureQ. Run that and you can see there are your values, same as before. If you think there is a possibility of having multiple different Exposure Types, let us go back to my ExposureT, Design View. Let us say you do not have IsPositive. Delete that. Let us say you have ExposureTypeID. That will be a number. Let us say we have got three values, 1, 2, and 3. Let us say we have got Positive, Negative, and Contact only. I am going to save that. Now I am going to make another table. This is basically getting into simple relationships here, another table. This will be my - usually I name my IDs, but for quick videos, I do not bother. This will be ExposureTypeID. That will be an AutoNumber. Then we will just say Description. Now in this table, you could put a value in here to determine what the follow-up is. So FollowUpDays, the number of days to follow up. Save this. This will be my ExposureTypeT. Primary key, there you go. So let us say in here, this is where you would put Positive, and you can make it all caps if you want, it does not matter. Follow up in 11 days. Let us say Contact only, that is a 15-day follow up. And let us say Negative, you still want to check with these people in 20 days. Save that. So you have got 1, 2, and 3. Now you come back to your Exposure table. I would, if you are using multiple tables, now I would rename this to ExposureID. In case you get into relationships and you get to see which ID is this, it is better to have them named like that. Now your ExposureTypeID is going to be a number from 1 to 3. So you come in here. You say, Joe was Positive, Sue was Contact only, and Bill was Negative. Save that. Now my ExposureQ is going to be completely different. So delete that. Create, Query Design. Now you want to bring in both tables, so ExposureT and ExposureTypeT, just like that. Now Access will make a little relationship line between them. This ExposureTypeID in this table is the same as the ExposureTypeID over here. Now watch how simple this is. Bring in the ExposureID, FirstName, their ExposureDate, the Description and the FollowUpDays from over here. Now let us save this as my ExposureQ. Run it, you see all the relevant data. Joe, ExposureDate, Positive description, FollowUpDays is here. Now it is just simply a matter of adding this value and this value. So right here, you say FollowUpDate: that means this is a calculated field. FollowUpDate is the ExposureDate plus FollowUpDays, the number of days there. And it is handy because, like I said, Access treats whole numbers as a value of one date. That is a very, very powerful feature that you can utilize. You want to add weeks? You can add 7. If you want to add months, then you have to use DateAdd. I have got whole lessons on DateAdd. I am not going to bother going over it here. I will put some extra links to some stuff like the If function, calculated query fields, DateAdd, relationships. I have got free videos on all this stuff on my YouTube channel and on my website. If you want to learn more about how to do this properly, like I said, I know you are busy. You probably just want to take your table and get it working. But if you want to build it properly, if you are planning on using this database long term, it might be beneficial to take a step back and rebuild it properly. You will have fewer problems in the long run, trust me. So I hope that shows you what you need to go forth and help fight the virus. If you have any other questions, post them in the comments below or contact me on my website. I will put some contact information at the end of the video here or check the description below. How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions and other perks. After you click the Join button, you will see a list of all the different perks that are available: Silver, Gold, Platinum and Diamond. But do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making them and they will always be free. If you enjoyed this video, please give me a like and share. Click on the Subscribe button to subscribe to my channel and be notified of any new releases. Check for additional resources down below the video. Click the Show More button and you will see a list of other links to other videos, downloads, resources, lessons and lots more. If you have not yet tried my free Access Level 1 course, it is three hours long. You can find it on my website or on my YouTube channel. And if you like Level 1, Level 2 is just one dollar. And that is free for my members. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can post your question there. Be sure to stop by my Access forum on my website. Look for me on Facebook, Twitter, and of course YouTube. Once again, my name is Richard Rost with AccessLearningZone.com. Thanks for learning with me and I will see you next time. QuizQ1. What was Nancy's issue that prompted the video tutorial?A. She was unable to use the DateAdd function with the If function to calculate future dates based on different criteria. B. She could not connect Access to her database. C. She wanted to create a report with a summary of data. D. She needed help creating input forms. Q2. What are the two values that "Exposure Type" can have in Nancy's database? A. Positive and Negative B. Positive and Neutral C. Positive and Contact D. Contact and Negative Q3. What is the primary purpose of the Immediate If (IIf) or If function in this scenario? A. To filter out unwanted records B. To calculate a follow-up date based on exposure type C. To validate user input D. To sum up total exposures Q4. What does Access treat a value of 1 as when used with dates? A. One hour B. One month C. One year D. One day Q5. Why does Richard discourage the use of spaces in field names in Access tables? A. It causes tables to load slowly B. It requires the use of square brackets and can complicate queries C. It forces fields to be numbers only D. It makes tables incompatible with forms Q6. What field type does Richard prefer for storing the "Exposure Type" when there are only two possible values? A. Text B. AutoNumber C. Yes/No (Boolean) D. Currency Q7. Where does Richard recommend Follow Up Dates should generally be calculated? A. In forms B. In queries as calculated fields C. In VBA modules D. In table calculated fields Q8. What is the primary reason Richard gives for not liking to store calculated values in tables? A. It slows down database performance B. It's not visually appealing C. It can cause data inconsistency and redundancy D. It requires too much disk space Q9. What is the advantage of using IDs (numeric keys) for exposure types instead of storing text values directly? A. It makes sorting easier B. It eliminates spelling errors and supports data normalization C. It looks better in reports D. It reduces the need for relationships Q10. If you want the calculation to work for more than two exposure types, what does Richard suggest? A. Use multiple queries B. Use nested If functions or a related table with type definitions C. Stick with text fields D. Use DateAdd exclusively Q11. What extra information does Richard suggest adding to the related Exposure Type table for future-proofing? A. Names of doctors B. Patient addresses C. The number of follow-up days for each type D. The type of medication required Q12. When should the DateAdd function be used in Access, according to Richard? A. Only when adding months or non-integer intervals B. Never, because it is obsolete C. Anytime you want to add years D. Only in macros Q13. Why does Richard recommend naming tables with a "T" at the end? A. For Access compatibility B. For easier identification and to distinguish tables from other objects C. To meet default Access requirements D. Because it sorts tables to the end Q14. What is the primary feature of Access regarding date calculations highlighted by Richard? A. Access does not support date arithmetic B. Access automatically converts time zones C. Adding a whole number to a date adds that many days D. Access ignores leap years Q15. What does Richard suggest for beginners wanting to learn more about building proper Access databases? A. Watch his free Access Level 1 class or other free tutorials on his website and YouTube channel B. Ignore normalization concepts C. Use spreadsheets instead of Access D. Immediately upgrade to Platinum membership Answers: 1-A; 2-C; 3-B; 4-D; 5-B; 6-C; 7-B; 8-C; 9-B; 10-B; 11-C; 12-A; 13-B; 14-C; 15-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. SummaryToday's video from Access Learning Zone covers how to use the Immediate If function in Microsoft Access to generate future dates based on certain criteria. I'll walk you through the process step by step, showing you how to set this up using both simple methods and more advanced techniques.The question addressed today comes from someone who needs to calculate follow-up dates for exposures. Specifically, if someone was exposed in a "Positive" situation, the follow-up should be 11 days later. For a "Contact" exposure, it should be 15 days later. The challenge was getting the DateAdd function to work with the If function to automate these calculations. To demonstrate, I set up a table similar to what you might already have. I started by creating fields for an ID, the person's first name, and the exposure date. For teaching purposes, I usually avoid spaces in field names, but I kept them in this example to match the question. The exposure date is a Date/Time field, and then I created a field for the follow-up date. You can insert the follow-up date directly into the table as a calculated field. While I personally do not recommend storing calculated fields in tables, it is still possible. The nice thing about how Access manages date values is that whole numbers represent days, so to add 11 days you can simply add 11 to the date. For example, to get a week after the exposure date, you can just add 7. After saving the table, I entered some sample data and showed how the calculation works. When using an If function for two possible exposure types (Positive and Contact), you compare the Exposure Type field—if it is Positive, add 11 days to the exposure date; otherwise, add 15. Once this logic is set up, entering Positive or Contact automatically gives you the correct follow-up date. However, my preferred method is to avoid storing calculated results in a table and instead use queries. This approach is more flexible and avoids the pitfalls of storing logic-based results, which can lead to errors if your data changes. To do this the right way, I set up a better-structured table that uses data normalization principles. Rather than storing the full description of the exposure type, I use numbers (like 1 for Positive and 2 for Contact) or even a Yes/No field if you are certain there will only ever be two options. For more values, it's best to use an integer field and a related table to keep things tidy and accurate. In this improved method, you keep the exposure data in one table and the exposure types, along with their follow-up day values, in another table. The Exposure Type table would include the type description and how many days to add for follow-up. This way, you do not have to worry about typos or inconsistent data since everything is managed by keys and relationships. To generate the follow-up date, you then create a query that pulls together the exposure information, the type description, and the number of follow-up days. The calculation in the query simply adds the exposure date and the follow-up days. Access makes this easy because adding a whole number to a date value automatically moves the date forward by that many days. This setup not only answers today's question but also provides a much more reliable and scalable solution if you ever need to expand your system with more exposure types in the future. If you need to add weeks, you can add multiples of 7; if you need to add months, that's when you would use DateAdd, which I cover in other lessons. If you're interested in learning more about setting up your database correctly, including data normalization, relationships, and calculated fields, I have various free videos on my website and YouTube channel. I highly recommend watching my free Access Level 1 course, especially if you're looking to build a solid foundation for your databases. You can always post more questions on my website if you have them, or join my Access Forum and connect with others. Membership options are available if you want access to more advanced videos and resources, but these TechHelp lessons will continue to be available for free as well. For a complete video tutorial with step-by-step instructions covering everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListCreating a calculated Follow Up Date based on Exposure TypeUsing the Immediate If (IIf) function in Access Adding a calculated field in an Access table Handling field names with and without spaces Demonstrating table design for exposure tracking Assigning different day intervals based on criteria Building an Access query for calculated dates Using Yes/No versus Number fields for data normalization Storing lookup values in a related table Establishing relationships between tables in Access Using a related table for exposure type descriptions Performing calculations in Access queries using joined tables Calculating fields using values from a lookup table Saving and running queries to view calculated results Best practices for not storing calculated values in tables |
||||
|
| |||
| Keywords: TechHelp Access Using the Microsoft Access IIF Function to Return Future Dates Based on Criteria - DateAdd, Relationships PermaLink IIF Function for Future Dates in Microsoft Access |
