Job History 2
By Richard Rost
15 months ago
Recent Job History for Candidates in Access Part 2 In this Microsoft Access tutorial, we will learn how to display a candidate's most recent and previous job history directly on the main customer form using the DMax and DLookup functions. You'll discover how to handle situations where there is no current job and manage null values effectively. This is part 2. MembersThere is no extended cut, but here is the file download: 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, job history, DMax function, DLookup function, Microsoft Access Forms, recent employment, IIf statement, NZ function, concatenate criteria, start date filtering, end date filtering, current job display, previous job display, customer form customization, nested functions, update form data
Subscribe to Job History 2
Get notifications when this page is updated
Intro In this video, we continue building the Microsoft Access job history system by showing how to display a customer's current and previous job directly on the main customer form. I will show you how to use DMax to identify the most recent job based on start and end dates, display job information with DLookup, handle cases with no current or previous jobs using the IIf function, and set up your form controls for clear presentation. This is part 2.Transcript Today is part two of my job history series where we're going to take a complete job history for someone and display their current job and their previous job right on the main customer form. And of course, this is part two. So if you haven't watched part one yet, go watch part one, then come back.
Part one was all the setup. We got everything set up, and that's why I do the TechHelp free template video stuff too. So I have to recreate the wheel every single time. We got our customers, and now we've got a full job history for the customer. What we want to do is display their current job and then their previous job on this form. Remember, their current job is going to be the most recent start date with no end date. It could be none. They could have no current job. If they're not working, it'll say none. They're not currently working. That could be important.
So what's the algorithm? We're looking in the job table. End date has to be null, so we use is null for that. We want the job in here with the largest start date, the most recent. With dates, the larger numbers are in the future. We're going to use DMax to find the largest start date where the end date is null for this customer. I even like to write this out in Notepad sometimes before I even get into my code.
We're going to use DMax. DMax takes three bits of information: the field we're looking for, which is start date, from the job table where the customer ID has to match the customer ID on the current customer form. But it's also on the job form, so we can utilize the one on the job form too. So, customer ID equals, and we're going to use concatenation for that customer ID. But we also want to make sure that the end date is null. So inside of my quotes again, I'm going to go "and is null end date" just like that. Then we'll close up our quotes.
With that concatenation, this gets a lot of people, so I want to review it, especially for expert users. This whole thing right here, this concatenation, is going to get replaced with one. It grabs that customer ID, technically the one off of this form. It's going to say "customer ID equals one and is null end date," and that's going to go into there. So that is our final DMax statement. We can take this and put it in a text box on the customer form.
Let's close this. When I stand corrected, I was thinking about this form, it is going to actually grab this one because this field is going to go on the customer form. But customer ID is on both of these forms, but this box is going here. So we'll take one of these guys like credit limit, copy, paste. Whoops. I don't want you. I want you, copy, paste. There we go. I had this text in my clipboard and still didn't copy right, so it dropped it down as a label.
Now we're going to need two boxes. The current job's start date, and then we can use that start date to get the description and find out what job it actually is. But first, let's start with this date. So we're going to say current job date. That's going to be this guy. Let's open up his properties. I'm going to name this guy current job date. For the control source, it's going to be equal to that DMax statement we just wrote. I'm going to copy that and paste it right there, and get rid of that format for currency.
Save that. And we'll close it. Open it. There we go. We got 200401. That should be his current job. Let's look in here. Yep. It's got an end date for this customer, and it's got the right date. Let's take a look at the next customer. It's blank. Why is that? Let's take a look. It didn't come back with one, so it's actually got a null in there. If you want to change it to some other value, you can use that NZ function we talked about earlier, and you can wrap it in, but I'm just the null value is fine for now.
Now that we know what the current job date was, we know the date that this person had the job, and we know it's this one because this one here has no end value. Now we can use that date and look up the description. You could look up the ID too; you could look up anything from this record that you want. One thing to note, though, if the person somehow does have two jobs that they currently are at that they started on the same day, this is only going to give you one of them. This assumes that they don't have the exact same job on the exact same day.
Now we got the date, and we know the person, now we can actually look up what the job name is. So I'm going to copy and paste this guy. This will be just current job now. Let's open this up. I'm going to change the name to current job. Let's delete the control source. We're going to put something brand new fresh in here. Shift F2. What are we going to do? Well, we know the customer ID, and we know what their current job's start date is, so that should be enough to look up the name of that job or the description.
This is going to be equals DLookup the description from the job table where the customer ID equals the same customer, and the start date equals now. Remember for dates, we have to put them inside of these things to put valid dates. So that and current job date. That's the thing we looked up a second ago, and we're going to look up the description from the job table where the customer is the current customer. The start date equals that max start date that we looked up a second ago.
Hit OK, save it, close it, open it, and look at that. It looked up fleet admiral. Let's make sure that's correct. Yep. There's fleet admiral. Let's go to the next one. Error. Why am I getting an error? Take a second. Well, because this is null, we have to say, if this happens to be null, they don't have a current job, let's put the word none in there. So to do that, we're just going to wrap our function in an IIf statement.
Right in here, we're going to say, if is null, current job date, then put the word none in here. Otherwise, do the DLookup. And of course, close up that double parenthesis. This is a nested function. Here's the DLookup right there. But the IIf is going to go first, it's going to say if the current job date is null, put the word none in this box. Otherwise, put that whole DLookup thing in, that'll take care of your null problem. Save it, close it, open it, and look at that. None.
Now we can do the same thing with the previous job. What's the rule for that? It's going to be the max of end date. Assuming they've got other jobs in here, find the one that's got the max end date. In other words, the job they quit most recently, which job did they quit, that'll be their previous job. So again, it's going to be a two-step process. I'm going into design view, coming down here. I'm just going to make these from scratch because I want to write these for you to get into the habit of doing this, not just copying and pasting.
So this will be the previous. Whoops, what happened? The previous. Sometimes I look down, and I realize I was not typing. All right, let's come in here. This will be a previous job date. Let's delete this and start from scratch to get the practice.
Again, we're dealing with DMax equals DMax. What field are we maxing? End date. From the job table where the customer ID equals customer ID, that's it. That's all we need for this one. Just give me the max end date for the customer. Nulls won't show up in here. Or if he hasn't, another way to think of it is if he hasn't quit a job or been fired, or if he doesn't have an end date, that means he doesn't have a previous job, he only has a current job or no jobs. Hit OK.
Then the same thing, copy, paste. This will be previous job. Now we'll get that description. Previous job delete. There we go. This will be equals, I find it easier to write the inside function first, do the DLookup first, then wrap it in the IIf. So DLookup description from the job table for the customer ID equals customer ID, and end date equals previous job date and that thing. Now we got to deal with our null. We'll wrap this whole thing inside of an IIf is null, previous job date, none, comma, this whole thing, closed parenthesis.
Notice what I almost accidentally did there. I almost put that inside a quote. That was just me. My finger's getting ahead of me. But people do that. I see this mistake a lot. This is a field on the form. It's not a text. It's not a string of letters. This would be the actual text previous job date, whereas this is the field previous job date. Don't mistake those. Hit OK, save it, close it, open it. There we go. Shuttle pilot. That's his previous job. Yep. Next, no current job, previous jobs, a starship captain. Yep. That's got the biggest end date. Let's do the next person. Looks good. Academy instructor, engineer was previous. Looks good. Next, job B and no previous job. Yeah. Both of these are active, and this is the one that he canceled the most recently.
If you go to someone who has no job history, it'll just say none and none, which is what we want. I just cleaned this up a little bit. I said, current job, I moved these over here. Current job, flatten this upright, like so. Take that big. Then underneath that, I did sense. So he's had this job since that date. Then the previous job under that. Previous job. Then this would be until. Getting these lined up exactly right is a thing of mine. I usually, in my full classes, I don't make you sit here. We're having fun today. Then I'll select all of this stuff right click size to grid. There we go. Maybe put the jobs button down here next to these guys. Save it.
Close it. Open it. There you go. So it's looking pretty good. Now, one more issue we have to address. What if you update this information? What if it turns out that he's no longer a fleet admiral? He just talked to someone on the phone today, and he quit that job. He was a fleet admiral as of 2025, 11. Close this and this doesn't update. If you close this and then we open it, now it updates. So how do you get it to do that without you having to close and reopen the form? We'll talk about that in tomorrow's class.
Tune in tomorrow's same bat time, same bat channel. Or if you're a member, you can watch it right now. It's one of the benefits of being a member. You can watch videos as soon as I finish them. But that is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part three.
TOPICS:
Displaying current and previous job on form
Determining current job using DMax
Using DMax with IsNull for end date
Concatenation for DMax condition
Copying and modifying form elements
Using DLookup to find job description
Handling null values with NZ function
Using IIf function for conditional display
Finding previous job using max end date
Writing DMax and DLookup for previous job
Aligning and formatting form controls
COMMERCIAL:
In today's video, we're continuing with part two of our job history series. We're learning about displaying a customer's current and previous job directly on the main form. We'll show you how to use the DMax function to find the most recent start date without an end date, indicating the current job, and how to handle situations where there's no current or previous job using the NZ and IIf functions. You'll learn to set up fields to show job descriptions based on these dates using DLookup and ensure your data is displaying correctly. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.Quiz Q1. What is the objective of the job history project discussed in the video?
A. To display a customer's full job history on a separate page
B. To display the customer's current and previous jobs on the main customer form
C. To record job applications for future reference
D. To integrate social media profiles with job histories
Q2. How is a "current job" identified in the job table?
A. By the smallest start date with a valid end date
B. By the largest start date with no end date
C. By the largest end date, regardless of start date
D. By the smallest start date with an ongoing contract
Q3. Which function is used to find the most recent start date for a customer's current job?
A. DLookup
B. DCount
C. DSum
D. DMax
Q4. Before placing the DMax result into the text box on the customer form, what must happen if there is no result?
A. Display an error message
B. Replace the null with a hyphen
C. Use the NZ function to give it a default value
D. Display the null value as it's acceptable for this scenario
Q5. What happens if a customer's job information includes two jobs with the same start date and end date?
A. Both jobs are displayed in the form
B. Only one job will be shown because the function assumes unique start dates
C. An error message will appear
D. The jobs will be averaged into a single entry
Q6. To display the "previous job" for a customer, which field do you get the maximum value from?
A. Job description
B. Start date
C. End date
D. Customer ID
Q7. How do you handle null values when looking up job descriptions for a customer whose current job or previous job is not available?
A. Use the If function to replace null with a default text
B. Leave it blank and display no information
C. Display an error message to the user
D. Use the IIf function to replace null with the word "none"
Q8. What feature change would you introduce if a job record is updated (e.g., a job end date is added)?
A. Manually delete the existing job data from the form
B. Wait for the end of the day for an automatic update
C. Close and then reopen the form to refresh the data
D. Automatically trigger a form refresh without closing
Answers: 1-B; 2-B; 3-D; 4-D; 5-B; 6-C; 7-D; 8-D.
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 continues our job history series as we work on displaying a person's current and previous job directly on the main customer form. For those who haven't seen part one yet, I recommend checking that out first, since it covers all the initial setup.
In part one, we established our framework for tracking the full job history of our customers. Our task now is to display their current job and their previous one on the form. The current job is defined as the most recent start date that does not have an end date. If the person isn't currently employed, it will display as 'none'.
To find the current job, we will utilize an algorithm that looks at the job table. Specifically, we search for entries where the end date is null, using DMax to identify the job with the latest start date for the customer in question. Sometimes it's helpful to jot down this kind of logic in a simple text editor such as Notepad before coding.
DMax requires three pieces of information: the field of interest (the start date), the table name (job table), and a condition that matches the customer ID. We'll align this process directly with the customer form's data. The result—when fully coded—will be integrated into a text box on the customer form for easy viewing.
We should create two text boxes on the form, one for the current job's start date, and from this, we can derive the job description. We'll start by focusing on the date by labeling it as "current job date," and storing the DMax expression as its control source. We may encounter null values which can be handled using the NZ function if needed.
With the current date identified, verifying a position assignment becomes straightforward. By using this date, we can next utilize the DLookup function to pinpoint details like job ID or description. However, if two jobs were initiated on the same day, only one job will be displayed, presuming unique job starts.
After establishing the date, we move to retrieve and show the job name, renaming our text field to "current job." Revisiting our functions, DLookup will retrieve the job name from the relevant table using known customer information and the current start date. It's important in date expressions to ensure proper syntax by encapsulating date values appropriately.
Some error checking is necessary if current job data is absent, indicated by an empty value. An IIf statement can be wrapped around our function to substitute a null output with the word 'none'. After implementing and saving these changes, the customer's job history will be accurately represented on the form.
We will similarly approach previous job data, using DMax to find the maximum end date, indicating the most recent job that ended. We'll create input boxes from scratch for practice, but following a similar construct as before will help us identify and display the previous job as well.
Adjustments to form design enhance clarity—proper labeling and resizing help organize current and previous roles with pertinent dates. It's crucial to reassess design after any database update to trigger live form refreshes. There's a forthcoming lesson on form updating methods to make real-time data reflect changes effectively.
For full video tutorials, including comprehensive step-by-step instructions on everything discussed here, visit my website through the link below. Live long and prosper, my friends.Topic List Displaying current and previous job on form
Determining current job using DMax
Using DMax with IsNull for end date
Concatenation for DMax condition
Copying and modifying form elements
Using DLookup to find job description
Handling null values with NZ function
Using IIf function for conditional display
Finding previous job using max end date
Writing DMax and DLookup for previous job
Aligning and formatting form controls |
