Another Query Fails
By Richard Rost
2 years ago
When Another Query for Criteria/Calculations Fails
In this Microsoft Access tutorial, I will show you how to handle complex criteria or calculations that aren't resolved by using another query. We'll cover problems with null values, creating make-table queries to bypass these issues, and even include a bit of VBA for efficiency.
Members
There is no extended cut, but here is the database 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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, complex query criteria, Access calculations, query parameter issue, second query solution, temporary tables Access, make-table query, date calculations Access, birthday query Access, null values Access, is not null issue, Access table joins, VBA in Access, Access error troubleshooting, action queries Access, Access expert tips
Intro In this video, we'll talk about a tricky situation in Microsoft Access where even using an extra query doesn't solve issues with complex criteria or date calculations, especially when null values are involved. I'll walk you through building queries to find customers with upcoming birthdays, show why "is not null" sometimes fails, and demonstrate how to use make-table queries and temporary tables to get around these problems. We'll also cover handy tips like working with date functions, aliases, and basic VBA automation to make your workflow smoother.Transcript Today we're going to talk about when even using another query fails with complex criteria or calculations. What does that all mean? Well, yesterday, in yesterday's video, we talked about how if you've got a single query that's too complex and you try to put criteria in it or a really complicated expression and it throws a parameter value and you're not expecting it or some kind of an error message, you might just get away with making a second query feeding the data from the first query into the second query and you can apply your criteria in the second query and that makes perfect sense, right? Well, it doesn't always work and sometimes depending on the first query, the second query still might not work.
I'm going to show you how to get around one specific problem that I came across. In fact, this is something that I don't think Access should work this way, but it does. In fact, Sammy, you might want to put this on our list. We've got a list going for the Microsoft team if I ever get in touch with one of them. Here's a list of stuff that either needs to be fixed or we don't like.
But let's walk through an example, and I'll tell you exactly what I'm talking about, and I'll show you how to fix this particular problem. And I guess one of the things I'm trying to get across is when building software and working with Access specifically, there's always multiple ways to attack a problem. Thinking about other ways to do stuff will sometimes be very helpful.
Now, yesterday was for the beginners. This is what I consider an expert level class which means it's a little more complex than beginners are used to but it's not quite developer level - that's VBA programming and such. Although if you're good little boys and girls at the end of the video, I'm going to sneak in a little VBA just to show you how to do something a little faster.
But let's take a look at the problem. Before we get started, some prerequisites for you: of course, go watch yesterday's video first to understand what I'm talking about with using a second query. Today's example is going to require some date calculations so make sure you understand the year, month, and day functions. We're going to use those to take apart a date into its constituent components. We're going to use date serial to put them back together again. Make sure you understand null values and is not null. These are all free videos. They're on my YouTube channel, they're on my website, go watch those and then come on back.
Okay, so here I am in my TechHelp free template. This is a free database you can download off my website if you want to. In this database I've got a list of customers. Now my goal for this video is to construct a query showing all the customers whose birthdays have not yet passed this year. Now yes, I know there are a million different ways you can calculate this. In fact, I have other videos showing how to do this. But I'm going to show a specific method in this class that a lot of people use, and it causes the problem that we're talking about. It was hard to come up with a good example for this. It's kind of rare, but it does happen. Right now in this customer table I've got a field called customer since. We're just going to use that as the date of birth. I don't feel like typing in a whole new field.
Okay, so let's go to Create. We're going to first create a query, Query Design. We're going to bring in the customer data. Now for this, I just need the customer ID, and I'm going to bring in the customer since field, but I'm going to rename it date of birth. So DOB: like that. It's called an alias. Okay, here I'll zoom in so you can see it better. Alias. And I've got a video on aliasing too. I'll put a link to it down below. It's just assigning that a different name. So now if I run this, all right, there we go. We get all those dates of birth.
Now, and this is where the problem creeps in. Okay, pay attention to what I'm doing right now. Notice there are some people in here that don't have dates of birth. If you look right in the customer table, down at the bottom, all of my Lord of the Rings guys. Frodo, Gandalf, Legolas, and Peregrine come over here. There's no dates of birth on them. Because it was a different age. I don't have dates of birth. I'm sure that knowing Tolkien, he's probably got dates of birth for all of them in the books somewhere. I haven't read them since high school. But anyways, so what we're going to do is, and you'd think this would be enough, I'm going to go into this query, and I'm going to say for date of birth is not null. Now if I run the query, I get all dates of birth and you think everything at this point is fine, right?
Okay, so let's finish this up. I want to break this date of birth down into its components. I need the day and the month and then I'm going to add to it the current year, and that will give me their birthday this year. I can then figure out if their birthday has passed or not. So month is easy. It's gonna be M, it's gonna be the month of DOB. If I run that now, that works fine. I got their month. There's their month. The day, D is gonna be the day of DOB. Looking good so far. Now for this year's birthday, the year is going to be the year of today's date. So the year of date, right? That should be 2024 for everybody. Okay, so now I can use date serial and put that back together into a valid date, right? I'll zoom in so you can see this one better.
Okay, birthday this year is serial and it's year, month, day. So year, month, day, right? And everything looks good so far, right? I got beautiful, everything's perfect so far. I'll save this. I'm gonna call this my birthday que, birthday que. All right, now if I want a list of people who have not yet had their birthdays yet. Let's say I want to send them a card to come into my restaurant for a free sundae. Whatever.
Okay. So I should just be able to put some simple criteria in here now, right? And I should just be able to say right here this is greater than today's date, right? And if I run it and I get okay, enter parameter value. Okay. This is the problem we ran into in the last video where it doesn't know what all these values are yet. So maybe I got to throw this into a second query. So let's get that criteria out of there. Save that. Let's make another query. We'll call it birthday2. Create query. We're going to pull in query1. All right, and here I just want the customer ID so I know who it is and their birthday this year. Let's see. Make sure that's working. Okay. All right, and now on here, let's put in our criteria greater than date like that and then I'll run it and I have data type mismatch in criteria expression. What is going on here? What's going on? Data type criteria mismatch. What? Well, it's basically saying you're trying to compare apples and oranges, but no I'm comparing apples and apples. Birthday this year is a date value. Look it up, open this up, that's a date. It lines up on the right side. I tested this. If you add one to it, you get the next day. This is a valid date.
And we shouldn't have any null values in here. I got is not null there. But somehow, if you come through to here and you put that criteria on there, it's a mismatch. The reason is, the problem is, because of our null values in the table. Okay? These guys down here are causing the problem. And I know that for a fact because if I delete these null values and I run the query now, it works. And these are all the people with birthdays after today's date. Today is June 6th as I'm recording this. It's June 6th of 2024. So these are all valid birth dates.
The problem is that "is not null" from this query is not properly removing all the data. And if you've got a query where a query before it has invalid data in it, it still doesn't work. And this is something I think that the guys at Access should fix. The guys on the Access team at Microsoft. Fix that. If this query removes the null values, then anybody based on that query should not have to deal with those null values. It's a pain. I've run into this several times in my 30-year Access career.
Now, how do we fix it? Well, the way we fix it is get the criteria out of here. And just for the sake of class, I'm going to put those invalid people back in here. All right, so we got Frodo, we got Sam, we got Merry, we got whoever else. So if you look at the data now, those null values are back. All right, how do we fix this?
Well, we can't work with this as a select query. So we're going to have to use something called a make-table query. We're going to make this guy create a temporary table, and that temporary table will not have the null values in it. Okay, now I don't have a simple TechHelp video on make-table queries. I know I've covered it in a couple of other videos. I've got a make-table video coming up, so maybe by the time you're watching this it might be on my site. Just go to my website, search for make-table queries. They're not that hard. Essentially, we're just taking the output of this query and making a table out of it.
So you go up here, make sure the select query you got works. That looks like the data that I want. Now in here, we're going to go to the Query Type and Make Table. It's going to ask you what do you want to call it. We're going to just call it the birthday t or if you want to keep it consistent customer birthday t I have to keep all my customer data starting with customer. Okay. All right hit OK and now when you run this it's going to create a customer birthday t you might get some warning prompts up it's gonna say hey this is gonna create a new table blah blah blah I got those warnings turned off on my system okay and I cover that in my action queries video, and I'll put a link to this down below. You can save this guy as the, let's just call it the CustomerBirthdayMakeQue. Every time you want to run this now, you'll just run this make-table query. It'll create the CustomerBirthdayT.
Now the CustomerBirthdayT has the data in it that you want. Okay, and now you should be able to make a query on this data showing you what you're looking for. So Create, Query Design, bring in the customer birthday table, and you can link to it to the customer table if you want their other information. But I'll just bring in this stuff here. And this will say, it should be greater than or equal to today's date. Greater than today's date, run it, and there you go. All right, you can put the criteria in this because this table has no null values in it.
Okay, so save this one now as your birthday. Let's call it customer birthday. I can't spell today. Que. Okay, so in a nutshell, we got this birthday que that does all the work for us. It breaks it down, gives us the birthday this year.
Okay, that then feeds this customer birthday make que where it makes the birthday this year table. Okay, and then that gives you this, which you can then put a criteria on that in this query here. Now, if all of this seems a little confusing, yeah, it is. It's one of those things we got to do a workaround because the queries just don't work the way you'd think they'd work.
Now, here's where the bonus material comes in. If you want a little bit of VBA code to make this easier, you can run this make-table query and then open up this query in one click. Now, if you're new to VBA go watch this first. It'll get you started. It's about 20 minutes long. It'll teach you whatever you need to know to get going.
Okay, and here's what I'm going to do. I'm going to take over this hello world button here. Right, let's say birthdays this year. Okay, little things like this drive me nuts. Okay, there we go. All right, right-click Build Event, it'll bring up your VBA code editor. Okay, now we don't need the status Hello World in here. What we're going to do is, we're going to first run the make-table query, which is this guy, and you see it's full name here. CustomerBirthdayMakeQue.
All right, so it's going to be this easy. DoCmd.OpenQuery CustomerBirthdayMakeQue. It's going to create the customer birthday t. Okay now we'll say DoCmd.OpenQuery which one is the customer birthday que. That's our query down here that puts the criteria on for this guy and now we should get our list of birthdays. All right so if I click on the button, all right, the existing table is going to be deleted before you run the query. Yeah, okay. And there you go. See, there's all your proper people after June 6th. And whenever you run this, it's going to bring up this list of people.
Okay, and if you want to turn that warning message off, you can come in here and say DoCmd.SetWarnings False. Don't forget to turn them back on. Now DoCmd.SetWarnings True. Okay, now click the button and you go right to your query there and every time you run this it comes back up with the right list of data. And if you want to see who all these people are you can take this query and you can join in here we'll add Tables, the Customer table. All right, so we can bring back in whatever other details we want. Like you want their first name and last name, let's say, and their address so you can send them a birthday card, whatever. All right, if I run this now you'll get all their information. In fact, this is another tip for when you're making these complicated queries. The stuff that has the calculations in it, right, this guy, okay, this guy, this guy, all these other queries. Get rid of all that extraneous data. Only bring into this query exactly what you need. You'll need the ID so later on you can go back and figure out who it was. But for this query all we need is the date of birth. We're figuring out the month, a year, birthday, this year. We don't need all the rest of these fields. We don't need email, address, state, country, all that stuff. Forget about it. Forget about it.
Okay, then we can do this and this and all the other steps in the middle. Then when we're done, and we've got our nice list of ID and birthday this year, then we can rejoin it to the original customer table and add that stuff back in. Okay, so that's another important step. There's a lot of good tips in this in this video today. Okay, and a little tiny DBA for you, so don't say I never gave you nothing.
All right, so that's just one example of when a complicated query fails even if you try to make it in a multi-step query. Sometimes you just got to resort to making temporary tables. I've had to do it a lot. If you've got a better solution for this, I want to hear about it. Post a comment down below. Yeah, I know there's more advanced techniques you can use with subqueries and all kinds of other stuff, but this is I think the easiest method for most people to get. Also, if you want to learn more about these action queries, they're extremely powerful. There's update queries, append queries, adding records, make-table queries, all kinds of stuff. I've got five whole lessons on them, each one's over an hour long, in the middle of my expert series, check them out.I'll put a link down below. But folks, that is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. Live long and prosper, my friends. I'll see you next time.
TOPICS:
1. Addressing complex criteria in Access queries
2. Handling parameter value errors in queries
3. Using multiple queries to manage complex data
4. Working with Access date functions (year, month, day)
5. Employing DateSerial for date calculations
6. Handling null values in Access queries
7. Creating and using aliases in queries
8. Constructing a query to show upcoming birthdays
9. Creating and managing make-table queries
10. Creating temporary tables to eliminate null values
11. Automating queries with VBA code
12. Suppressing system warnings in VBA
13. Joining tables to retrieve additional data
14. Optimizing queries by limiting result fields
15. Utilizing DoCmd in VBA for query execution
COMMERCIAL:
In today's video from Access Learning Zone, we tackle an issue with complex queries in Microsoft Access that even using a second query won't fix. I dive into the specifics of handling date calculations and null values, showing you step-by-step how to construct a query to find customers with upcoming birthdays. You'll learn why "is not null" doesn’t always work and how to create temporary tables to bypass these problems. I even throw in some VBA code tips to automate the process. 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 common issue does the video address?
A. Slow query performance due to too much data
B. Errors occurring from complex single queries with criteria and expressions
C. Inability to import external data into Access
D. Difficulty in formatting dates within queries
Q2. What technique does the video initially suggest to handle complex queries?
A. Using macros to break down the query
B. Splitting the complex query into multiple simpler queries
C. Importing data into Excel for further calculations
D. Writing SQL directly in the Access SQL view
Q3. Which functions are mentioned as necessary to understand for the tutorial example?
A. Left, Right, Mid functions
B. Date, Now, Time functions
C. Year, Month, Day functions
D. Concat, Join, Split functions
Q4. What type of data value causes a problem in the queries discussed in the video?
A. Duplicate values
B. Blank strings
C. Negative numbers
D. Null values
Q5. What is the goal of the query being constructed in the video tutorial?
A. List of customers with purchases over $100
B. List of customers based on their joining date
C. List of customers whose birthdays have not yet passed this year
D. List of customers living in a specific zip code
Q6. Which query type is used to resolve the problem with null values in the tutorial?
A. Append query
B. Update query
C. Delete query
D. Make-table query
Q7. After creating a make-table query, what should be done to ensure criteria can be applied?
A. Check if the table is indexed correctly
B. Apply criteria to the newly created table
C. Export the table to an external file
D. Rename all fields in the table
Q8. What is a crucial step in simplifying complex queries as suggested in the video?
A. Avoid using aliases in queries
B. Discarding extraneous data and only bringing in necessary fields
C. Adding all fields and calculations in every step
D. Using complex VBA scripts for every query
Q9. What VBA command is used to run the make-table query in the example?
A. DoCmd.OpenTable
B. DoCmd.RunCommand
C. DoCmd.OpenQuery
D. DoCmd.RunSQL
Q10. What is recommended to do after turning off warnings using VBA commands?
A. Close the query window manually
B. Restart Access to reset warning messages
C. Turn the warnings back on with DoCmd.SetWarnings True
D. Save all VBA scripts
Answers: 1-B; 2-B; 3-C; 4-D; 5-C; 6-D; 7-B; 8-B; 9-C; 10-C
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 focuses on what happens when using an additional query to filter or process complex criteria or calculations in Microsoft Access still fails. This is a step beyond what was covered in yesterday's video, where I demonstrated how breaking up a complicated query into two parts can sometimes resolve issues like unexpected parameter prompts or error messages. That method can work, but it's not always reliable, especially when certain types of data or calculations are involved.
Let me explain a particular situation I encountered that highlights a limitation in Access. While I do not think Access should behave this way, unfortunately it does. If I ever get in touch with Microsoft's Access team, this is definitely going on my list of quirks and bugs for them to review.
I want to walk you through a very specific example so you can see exactly where the problem occurs and how to resolve it. One of my main points today is that with Access, and with software development in general, there are often several different ways to approach a challenge. Being open to trying different methods will often help you find solutions you might not have considered.
This lesson is what I consider expert level—not quite developer (which would involve more VBA code), but definitely more advanced than what beginners are used to. Toward the end, I will briefly show some VBA code as an added bonus for those who are interested in automating the solution.
Before you begin, I recommend a few prerequisites if you're not already familiar with them. Please watch yesterday's video if you haven't done so, since it explains the basics of splitting queries. Today's topic will involve some date calculations, so you need to know how to use the Year, Month, and Day functions, as well as how to reassemble date information with DateSerial. Understanding null values and how to use Is Not Null is also essential. All of these concepts are covered in free videos available on my website and YouTube channel.
Here's the scenario: using my TechHelp free template database, which you can download from my site, I have a list of customers. The goal is to create a query that shows all the customers whose birthdays have not yet occurred this year. There are many ways to achieve this, but I'm showing you a method that demonstrates the specific problem under discussion.
Instead of a dedicated date-of-birth field, I will use the existing "Customer Since" field for this demonstration. After starting a new query and bringing in the relevant customer data, I assign an alias to that field to call it "DOB" (for date of birth). If you're not familiar with using aliases in queries, I have a separate video on that topic.
When I run this query, I see all customer records, but some people do not have any dates entered. For example, in my customer table, some of the Lord of the Rings characters don't have a DOB on file. To try to filter out those records, I go back into the query and set a criterion to exclude null values by specifying that DOB must not be null. Running the query at this stage appears to work—only records with an actual date are returned.
Next, I need to calculate each customer's next birthday this year. To do that, I extract the month and day from their DOB, combine that with the current year, and reconstruct the date using DateSerial. I set this up step by step, confirming that I get the right values for month and day, and verifying that the current year is being used throughout. When I put it all together to get each customer's birthday this year, it looks correct in the query results.
Now comes the issue. Intending to get a list of customers whose birthdays have not yet occurred this year, I try to filter the results by specifying that "Birthday This Year" should be greater than today's date. When running this, Access throws a parameter prompt rather than returning the expected results.
This is the same type of problem I talked about in the previous lesson, where sometimes criteria fail because Access doesn't recognize calculated fields or expressions in its evaluation order.
So I try the common workaround: removing the criteria from the first query and starting a new, second query that is based on the first one. I bring fields forward, apply the criteria (birthday greater than today), and attempt to run it. However, this time I get a "data type mismatch in criteria expression" error.
Troubleshooting this, it turns out the error is triggered by the presence of null values in the source table, even though they should have been excluded by my earlier "is not null" criterion. If I remove the problematic records from the source table (those with null dates), the query then runs as expected. Clearly, having null values in previous queries can cause subsequent queries to break, even when the earlier query is supposed to filter those out. This is an area where Access really should function differently.
To solve it, I take a different approach. Rather than using select queries alone, I use a make-table query. This type of query outputs the desired results into a new table, and if you set it up correctly, that table will contain only the data you want—without the troublesome nulls.
If you're not familiar with make-table queries, I cover them in depth in a few other videos and I have a full lesson on the way, so check my website for more resources if you're interested. Essentially, a make-table query creates a temporary physical table in your database using the results of a select query. Once you have this clean table, you can base further analysis or reporting queries on it without worrying about lingering null values.
I create the make-table query using the results of my earlier calculations and tell Access to write the output to a new customer birthday table. After running the query, I confirm that this new table contains only valid records, none with null dates. Building a new query on this table and setting criteria for upcoming birthdays now works perfectly.
As a practical improvement, I also demonstrate using a bit of VBA code to streamline the process. With VBA, you can program a button to run the make-table query and then immediately open the analysis query. This way, every time you want to refresh your results, it only takes one click. For those less experienced with VBA, I suggest watching my beginner's introduction to VBA, which will provide all the background you need.
Within the VBA code, I use the DoCmd object to execute the queries and suppress system warning messages so the process runs smoothly and quietly each time. When you want to see additional customer details, you can simply join your birthday results back to the customer table, pulling in names, addresses, or anything else needed to contact those people.
One key tip I'll stress again: when constructing these intermediate queries, only include fields absolutely necessary for your calculations. Minimize excess data. Once you have the set of IDs you need, you can always rejoin and include the original full details in your final output.
To sum up, even breaking up a complex query into multiple stages doesn't always solve every problem in Access, especially when null values are involved. In some cases, creating a temporary table is the simplest and most reliable way to get around issues with data types and calculation mismatches. I encourage you to experiment with these techniques. If you have alternative solutions for dealing with this, I would love to hear them—feel free to leave a comment.
If you want to learn more about action queries, such as update, append, and delete queries, or if you want deeper dives into advanced Access features, I have a full series of expert-level lessons available on my website.
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 Addressing failures with multi-step queries in Access
Solving data type mismatch errors in query criteria
Working with date functions to extract year, month, and day
Using DateSerial to assemble dates from components
Filtering out records with null date values
Renaming fields using aliases in queries
Building a query to find customers with future birthdays
Diagnosing why "is not null" can fail in query chains
Creating make-table queries to generate temporary tables
Eliminating problem records with temporary tables
Writing VBA to automate query execution
Suppressing warning messages during VBA automation
Joining a results table back to the main table for more details
Minimizing query fields to streamline calculations |
