Quick Queries #78
By Richard Rost
39 days ago
Top Values Query Setting Limiting Query Results
In this video, we tackle several Microsoft Access topics, including why queries sometimes stop showing all your records due to the TOP VALUES setting and how to fix it, working with SharePoint as a backend and its common challenges, displaying multiple query results in a single report or dashboard, managing Access security with trusted locations, techniques for reusable controls, VBA debugging tips, and resolving common issues like form parameter prompts, slow combo boxes, and handling ByRef vs ByVal for form references in code.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp QQ Quick Queries, AccessLearningZone.com, query result limit, query top values, SharePoint backend, VBA line numbers, AddLineNumbers, VBA error handling, DLookup, dashboard queries, input mask, format to uppercase, OneDrive trusted location, unblock zip file, relink tables, VBA Nothing Null, WinSCP, EditPlus, microfinance database, parameter from form, appointment form filter, reusable combo box, ByRef ByVal
Intro In this video, we tackle several Microsoft Access topics, including why queries sometimes stop showing all your records due to the TOP VALUES setting and how to fix it, working with SharePoint as a backend and its common challenges, displaying multiple query results in a single report or dashboard, managing Access security with trusted locations, techniques for reusable controls, VBA debugging tips, and resolving common issues like form parameter prompts, slow combo boxes, and handling ByRef vs ByVal for form references in code.Transcript Why does Microsoft Access sometimes stop outputting records to your query when you know there's more data in there? Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today, we're going to talk about that setting that silently limits your Microsoft Access Query results. I'll show you where that limit comes from, why it happens, and how to fix it so you can see all of your data again.
We'll also hit on a bunch of other topics including using SharePoint as a backend, displaying multiple query stats on one dashboard, trusted location security headaches, reusable controls in Access, and some VBA debugging tips.
Alright, let's get to it.
Leading off today, we've got a question from a longtime student, Micah. He wants to know if there's a way to throw line numbers in the VBA editor. A few folks shared some workarounds and tools, but the core question was whether Access itself has a built-in way to turn line numbers on, and if not, what your options are.
And Sammy's got it right here. He cheated. He used Chat GPT, so don't feel bad. If it's something I know, which doesn't happen a lot, but it does happen sometimes.
But the short answer is no. There's no native setting anywhere in Access, in the Access VBA editor, to display persistent line numbers like you see in modern IDEs. VBA just never got that feature.
As Raymond pointed out, you can see the line number and column number right up here in the editor if that helps. If you want full line numbering, there are a few workarounds.
As Chat GPT said here, there are some third-party add-ins like MZ Tools. I've never used this myself, so check it out and use it at your own risk. You can always type in line numbers manually if you want to.
The big thing here is it's good for debugging. That's why people want to use it - because there's an ERL function. I barely ever use it. But what you can do is go in here and let me just bring this down here.
There we go. You can come out here and put in like 10 status hello world, 20 status hi there, like so on like this. We can get it all looking nice here. Usually, you increment by 10s so you can stick something in there if you want to.
We're going back to the old BASIC days. My old Tandy Coco BASIC. 30. You could say something like, let's dim X as a long. And down here, you can say X equals 10 divided by zero, which is going to error out. But if you have a big, giant subroutine, you might want to know where that error is.
So up top here, you can say On Error GoTo MyError. We've done this in the basic error handling before. Here you do a clean MyExit, so if it does get here, you exit sub. But if an error throws, you come down here where we're going to do MyError.
And you can say MessageBox "Error in line" and then ERL, that's a built-in function, and then you could do a description. Save it, debug compile.
And now if you come back out here and hit the button, and of course my database moves, right? See, error in line 30 - division by zero. Now it tells you what line number you want. It's on and it makes it a whole lot easier to track down your errors.
The problem is putting all these line numbers in. Like I said, there are some third-party tools. Or if you're a Gold Member, I did write some code right here. It's in the Code Vault.
I'll be honest, I did use Chat GPT for some of it, but it will take one of your modules and just stick line numbers in it. For example, I just threw it into this database. Let's view, turn on the immediate window, and then I'll come right down here and I'll type in, that's the function name, AddLineNumbers ActiveModule. Boom. There you go.
This is AddLineNumbers for you. If you want them, it makes debugging easier. I think they're a pain to maintain myself. You can get that same level of error handling, but you just have to do different - I put little tags in the code. I'll set it like a variable, like errorTag = 1, errorTag = 2. That way, if an error is thrown, it just tells me where the last errorTag was, whatever the value is set to.
There are all kinds of ways to do this. I cover this all in my debugging classes. So good question. That brings me back. I haven't done anything with line numbers in years, like decades, literally.
Next up, we got a question from Brian who said his office is moving away from shared drives and forcing everything into SharePoint. He tried storing and splitting his Access database there, but ran into issues like tables getting stuck in author only mode.
He wanted to know if there's a proper way to run a split Access database from SharePoint. A lot of people chimed in with some different answers.
I have an Access SharePoint seminar where I cover all the different things about connecting your Access database to SharePoint, lots of different stuff in it.
But yeah, this is one of those because IT said so situations. It pops up a lot. Technically, you can use SharePoint as a backend by linking your tables to SharePoint lists. Donald mentioned in the thread that he's made it work for a couple of years, so it's not impossible. I've done it in a seminar.
But let's be honest. SharePoint was built for document storage and collaboration first, databases second. So you're going to hit limitations, permissions, weirdness like that author only issue. You're going to hit performance bottlenecks, record locking, quirks, all that fun stuff. A lot of the headaches that you're going to run into are actually SharePoint configuration problems and not Access, but you still have to deal with them.
My general advice is what Adam and Sammy both pointed out. If your organization already lives in SharePoint and you have no choice, then fine, you make the best of it. You're going to do what I teach in the seminar.
But if you've got any influence at all, then SQL Server is a much better backend for Access: better performance, better multi-user handling, fewer limitations, and better security. It's the reason most serious Access deployments end up in SQL Server. I just started a free SQL Server course. I'll put a link down below. Check it out.
Next question comes from Leo. He says he's got multiple queries that each return a single number for management stats. He wants to display all of them together on one report or dashboard. He also ran into a follow-up issue where each query kept asking for the date range separately. He wants to know if there's a better way to deal with that.
I just have to share some comedy gold from Alex. This is just Alex being funny and most people get his sense of humor, but he gave an answer, "use DLookup," which is a good solution. And Leo says "expand please." So Alex expanded it. He's not being a jerk. That's funny. It's his sense of humor.
But we asked for some samples of what he's got. Here are his different queries. A lot of them have like one different value and some of them have multiple values.
For these guys, I'd recommend using DLookup where you can just DLookup this and display it in another form or report. For these, where you've got multiple items, put this in a subform or a subreport. I have videos on all this stuff on my website. There's also a video Donald recommended here called Dashboards where I show you a whole bunch of other tricks, like including charts and stuff. Here are some DLookups and there are DSums. This is most of what you want.
As far as that last gotcha that you got down here - every time he opens a dashboard, all the queries ask me for the period of time. The key to that is instead of having the queries have parameters directly in them that ask for a pop-up - Enter Parameter Value - what you want to do is put some form fields together. On your main menu or whatever your report form is, whatever form launches this stuff, put those as text boxes on that form. They can be unbound text boxes. The queries will get their values from that form.
This video shows you how to do that. Here is a little form with a state field in it. This query then gets that parameter from here. Then you can use that in your form.
The next one really isn't a question, it's more of a tip that Peter shared. He discovered you can force capitalization in a field by putting the greater than symbol in the format property, which automatically converts text to uppercase. He's using it for UK postcodes.
It sparked a broader discussion about input masks and data formatting and all kinds of tips that people added. I love tips like this because they're simple, but they're super useful, especially for beginners.
If you put the greater than symbol in the format property, Access will automatically force everything to uppercase as users type. It's a nice quick way to keep things consistent without writing any code.
At the developer level, I usually don't rely too heavily on input masks because they can get pretty restrictive. Peter ran into that with UK postcode since the patterns aren't always consistent. In cases like that, I prefer handling that kind of formatting in code. I use the UCase function in an after update event, and that gives you more flexibility without fighting an input mask.
Kevin also mentioned using LCase for standardizing things like file extensions, which is another great tip. Donald added some great ideas here too.
The key takeaway here is input masks are great for guiding users and for beginners. Once you start dealing with edge cases, though, doing it in code gives you a lot more control. Within an after update event, you can throw those special edge cases, those special considerations in there. If it's this, then do this. Otherwise, do the generic thing.
Next one comes from Julie, who was trying to open one of my templates, but kept getting blocked by Access security because the file wasn't in a trusted location. She tried setting up trusted folders and moving files around, but kept running into issues with OneDrive, relinking, and security warnings.
This one did get resolved after we walked through it step by step. I'm not going to go through the whole thread. I'll put a link to this thread down below, but we did end up coming up with some step-by-step security things to check. Here are all the instructions. I'll put a link to this down below if you have problems like that.
Trusted locations do trip up a lot of people, especially now that Windows loves quietly shoving everything into OneDrive whether you asked for it or not. Julie's situation is really common.
You download a template, try to open it, and Access throws up a security warning because it thinks the file came from the internet or isn't in a trusted folder. It did come from the internet, but you put it in a trusted folder. It should be trusted. Sometimes you have to get rid of that mark of the web. Once you do that, Access should be like, oh, okay. But it doesn't always.
For those of you who are unfamiliar with these, here is my video on trusted locations. Here is my video on that mark of the web, this thing here that shows up.
The big takeaways here:
First, always unblock the zip file and the database files in Windows properties before opening them. Right-click on the zip file and you'll see some properties in there.
Second, put your database in a true local folder like on your C drive, not your desktop or your documents, because those are often redirected to OneDrive behind the scenes.
Once you add that local folder to a trusted location in Access, most of these security warnings disappear. Like Kevin pointed out in the thread, once you get past security, you might still have to relink tables if it's a split database, but that's a whole separate step. Julie, glad we got you up and running finally.
Next up, one of my Silver Members, Jason, posted this bit of code. I did a video a little while back about dealing with Nothing and how to check to see if an object is set to Nothing or not. He added a little bit that checks to see if it's also possibly zero, because it can be Null, it can be zero, it can be Nothing.
I've got a little extra code here I wrote that has value that checks for Nulls and empty strings. He sent me an email, a 23-line version of all kinds of extra stuff. I'm going to be doing a longer video on this. But I just wanted to share this with you if you're also tired of Nothing.
All right, let's head over to YouTube.
First up, I got Jim. Jim said he didn't get the seminar. I'm not sure what seminar he's talking about. I've got an ASP seminar, I've got an SQL Server online seminar, I've got a bunch of different seminars. But he's talking about Winhost and struggling with file transfers and FTP, possibly with FileZilla, and was wondering what tools or setup I recommend for managing files online.
Winhost does technically have a file management system built in, but it's web based. Honestly, it's not my favorite. It works but it's pretty clunky if you're moving around a lot of files.
What I personally use is EditPlus. It's a great free editor that has FTP built right in. So you can connect to your hosting account, move files, edit them, all that stuff. It works really smoothly, regardless of who your ISP is, since you're just using FTP directly.
As for FileZilla, I know a lot of people use it, but I haven't personally worked with it in many, many years, so I really cannot offer any hands-on advice there.
If you're concerned about just file transfers, I like to use WinSCP. Again, it's a free award-winning file transfer manager. This is what I use to upload my videos and stuff to my web server. So EditPlus for editing my website and WinSCP for transferring files. That's what I use. Hope that helps.
Next up, Unity AgroLiteTV asked if I could create content on microfinance database management, basically building systems for tracking microloans and small financial accounts.
That's actually a pretty niche topic. I've never personally built anything specifically for microfinance, but from a database standpoint, it's really just customers, accounts, loans, payments, interest, all the usual relational stuff that I normally cover in my full course.
So while I probably won't do anything specific to microfinance, everything you'd need to build a system like that is already in my full Access course. Tables, relationships, payment tracking, reporting, it's all the same core design principles. You just put the Legos together however you want to.
Of course, I say this on all my videos, if enough people want to see it, sure, I'll consider making a video on it. If you want to see me do something about microfinance, post a comment down below.
Next up, Milan is saying his Access query will only show 99 rows even though he knows there are a few hundred records that should be coming back. He's not using a WHERE clause, Group By, or anything that should filter results.
This one is almost always the TOP VALUES setting in a query. In the Query Design view, open the Property Sheet and look for TOP VALUES - right up here. This guy says Returns All. If you've somehow got a 99 stuck in there, then you're only going to see 99 values.
If you switch to SQL view, you'll see it's right there - SELECT TOP 99. If it's set to 99, Access will happily stop at 99 and call it a day.
Just set it back to All or just delete this. You can just come into here in SQL view and delete that. Or if you're in regular design view, just come up here and pick All.
If you're viewing the result from a form or query and that form or report is based on this query, the query takes priority. So it's going to limit it by the TOP VALUES. I have a video on it, go watch this - I explain it in more detail.
Next up, Nationsin said that one of my between dates examples isn't working in the newer version of Access, getting that classic error saying the expression is typed incorrectly or too complex to evaluate.
Yeah, that error message has been around forever and this between dates technique literally has not changed in decades. It's nothing to do with the new version of Microsoft Access. If you're getting that message, it's almost always a syntax issue, which means you typed something in wrong in the criteria, the field name, the form reference, or the date format.
The method itself still works exactly the same whether you're using Access 95 or Access 2024, whatever the newest 365 version is. I cover static dates, parameter prompts, and form-driven criteria in that video. Go back through it step by step and compare what you typed to what I show, and do exactly what I do in the video.
Most of the time people have problems because they're trying something in their database with different fields and different form names. That's why you can't figure out why it's not working. So do it with the database that I'm doing it with. If you're still stuck, post something in my forums on my website and maybe we can help you track it down.
Next up, G Griffith or G Griffith - I don't know. He says he runs Access 24/7/2 - oh, and by the way, this is about my Kill Microsoft Access the MSACCESS.EXE article that I wrote. I posted this in my Captain's Log, so you might not have seen it. He says that he sees random weirdness too that gets fixed by restarting, but he was wondering if memory leaks, especially from Windows API calls, might be part of the problem.
Yeah, that's exactly what I was talking about in the article. If you've got an Access database running 24/7/7, doing background jobs, sending emails, processing customer service requests, that kind of thing - memory leaks can absolutely build up over time, especially in older evolving databases like mine. I started it 20 some years ago. Yeah, it's got lots of Windows API calls and years of bolt-ons in it. It's like Scotty with the Christmas tree engines thing.
So this was basically a band-aid fix because it would take way too long for me to go through and troubleshoot all of this code that I've been writing for the past 20 years. We're talking about a database that I normally would have had to reboot at least once a week if not more often, like every five or six days. As far as on vacation, I'd have to remote in and reboot it, otherwise it would start locking up.
One of the reasons I built my Access Watchdog program is that when it does lock up it reboots it. But even that didn't solve the problem all the time. My fix is basically a controlled band-aid. I have Access gracefully close itself, and every couple days the batch file waits until Access is shut down and then it runs a process that kills MSACCESS.exe and restarts the database.
Since putting that in place, I've gone almost a full month without having to reboot the machine. The 15th, it'll be a full month - January 15th I started this experiment. So I'm happy, it's a huge improvement.
Check the article out for more information on how it works. I'll put a link down below.
While I'm going through all these questions on YouTube, I want to make a program note here. I just released the first three lessons of my SQL Server for Microsoft Access course and got a ton of comments and questions, which is awesome. I'm going to save these. I'm not going to do these in Quick Queries. I'm going to actually do a Q&A lesson at the end of Level 1. I'm going to address all those questions there because I know that not everyone who's using Microsoft Access cares about SQL Server, so we're going to keep the Quick Queries mostly Microsoft Access, but I'll get to all your questions very soon.
That's one of the nice things about doing the course while people are actually watching the lessons as they come out because I get questions as I'm in the middle of developing it. I can address those by the end of the lessons, that's kind of cool.
It's not like most of my Microsoft Access course is done - I've got thousands of hours of video - so that's why I started doing Quick Queries, so I can address those.
Next up, Andreas says Access still has that wonderfully old-school database feel. Compared to modern web stacks like PHP or Node, it's way simpler to build and understand. He used it years ago and is thinking about getting back into it.
Yes, pick it up again! That's a great way to put it. Access definitely has that old-school feel, but honestly that's part of the charm. You can build real working databases without needing to spin up a full web stack, app server, APIs, all that overhead. It's still one of the fastest ways to go from idea to working system, especially for internal tools. If you ever outgrow it, you just scale the backend up to SQL Server and keep your Access front end. That's the beauty of it.
So if you're thinking of jumping back in, it's still very much alive and kicking. We're waiting for you to come back and sit at the cool kids table at lunch. We're all here.
Next up, we got Tommy who says, "I don't know if you still review comments here as it's an old video." One of the nice things about YouTube comments is that when I get them, I sort them by the date they were received. I get to the most recent ones first. That's good because if you post a comment now, chances are I'll get to it soon. The bad news is there are ones that are years old that are still in there because I answer as much time as I have when I do Quick Queries. The old ones, if I miss you, they keep getting pushed back.
Tommy says when I open up the appointment form, filtered to one customer, the customer dropdown still lets me pick a different customer, and it looks like it changes who the appointment belongs to. How do I stop that and just scroll through the customer's appointments instead?
That's actually normal. That's how that form was built. The combo box on the appointment form is the customer ID for the current appointment record, so changing it literally reassigns that appointment to a different customer.
If you want it to behave like a filter or a navigator, you need a separate unbound search or filter control, or open the appointment form from the customer form already filtered and then scroll through the appointments that way.
That's kind of what I do in this video. I show you two different ways. We do contacts for a customer. Instead of appointments, it's contacts. Same thing. Every time you talk to the customer, that's called a contact. I contacted him today about this. He called me yesterday about that.
I show you two different ways to do that. You can do it with a subform like it shows here. All of his contacts show up under the customer record, or you can make a button that opens up a separate form with the same stuff in it. Watch this video; that should answer your question.
This one, Shadow Dragon, posted a comment on one of my shorts where I talk about avoiding exposing your SQL Server to the internet. I just wanted to share it because it's hilarious: "I look forward to the day I can hire a cybersecurity AI named Worf that will defend my Minecraft server." I had to share that. That's hilarious. Thank you.
Next up, Namesh is asking if there's a way to make a reusable custom control in Access, like a combo box that can drop into lots of forms over and over again. Access really doesn't do custom controls the way Visual Basic or web frameworks do. A combo box is just a control on a form.
The usual way is to build the standard combo box once, then copy and paste it to other forms like I do. I build a little template form and I reuse it. If you have like a customer combo box or combo boxes that you reuse over and over again, if you have field names and all that are the same, you can copy it from form to form and then just whatever VBA goes with it, just copy that as well.
You can do things like class modules and stuff, but that's a lot more advanced, but the controls themselves can be copied from form to form. That's about the best you can do.
He's asking if there's a way to speed up combo boxes by using parallel or concurrent processing in Access, like running multiple background processes at once.
Short answer: no. Access isn't built for parallel processing like that. It's single-threaded for most operations, so forms, combo boxes, queries, they're all running in sequence. Trying to spin off parallel processes usually causes more problems than it solves, especially with form events and object references.
If combo boxes are slow, the better fix is to optimize the row source query, index your tables, or cache the lookup data locally. I've talked about this in lots of other videos. Trying to run things concurrently doesn't work like that.
Hey, I got your attention. Hit that like button right now. Go on, hit it. Do it. Make sure you subscribe and click that little bell icon so you get notified when I release new videos and stuff.
We've got time for one more.
Finally tonight, Artat pointed out something in my code about passing a form into a sub. He said if you pass it ByRef and then set it to Nothing at the end, you could accidentally wipe out the calling variable too and suggested passing it ByVal instead.
Yeah, that's a great catch and a good bit of VBA nuance. ByRef is the default. So if you pass an object and set it to Nothing inside the sub, you can affect the original reference. In my specific example, I was passing the form object itself, so it really didn't cause an issue. But his point is absolutely valid in general. If you want to protect the calling variable, pass it ByVal. Nice little under-the-hood tip there. If you want to learn more about that, go watch this video.
So today we saw how that TOP VALUE setting can quietly cap your query results, why SharePoint can be a tricky backend for Access, and a better way to handle parameter prompts using form controls instead of popups.
Post a comment down below and let me know how you like today's video and how you plan to use some of this in your database.
While I have your attention, don't forget Access Day 2026 is officially on the calendar! Save the date, you can register now. It's happening Friday, March 27 in Redmond, Washington. I'm not presenting. I'm just going as an attendee myself, but I'm looking forward to it.
Make sure you stop by my website and check out what's new. At least the first three lessons of my SQL Server for Microsoft Access Users course - that's completely free. Just did an article on vibe coding. If you want to hear my opinions on that, you'll find that in my Captain's Log with all kinds of other cool articles.
While you're on my website, don't forget to subscribe to my mailing list so you get an email every time I release something new, which is pretty much every day. Stop by my merch store, get a hat, get a t-shirt, get a hoodie, get a little teddy bear. I've got to see if they have little penguins. I've got to see if they have little penguin stuff.
Don't forget my book, it's on Amazon. Go get it.
That's going to do it, folks. That's your Quick Queries for today. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you next week.Quiz Q1. What is the cause of a Microsoft Access query only displaying a limited number of records despite more data existing?
A. The TOP VALUES setting is limiting the output
B. Improper table joins are hiding data
C. Query design view only loads 100 records by default
D. Access always applies a hidden filter to queries
Q2. How can you remove the record limit imposed by TOP VALUES in an Access query?
A. Delete the TOP clause in SQL view or set TOP VALUES to All in Design view
B. Change the datasheet view settings
C. Remove all WHERE clauses
D. Restart Access to refresh settings
Q3. What is one of the primary issues with using SharePoint as a backend for Access?
A. SharePoint tables cannot hold more than 50 records
B. You will often encounter performance and permissions issues
C. Microsoft Access will automatically convert tables to local tables
D. Forms cannot connect to SharePoint data at all
Q4. What backend does the instructor recommend as a better alternative to SharePoint for serious Access deployments?
A. MySQL
B. PostgreSQL
C. SQL Server
D. Google Sheets
Q5. What is a recommended approach for displaying multiple query stats on one dashboard in Access?
A. Use DLookup for single values and subforms or subreports for multiple items
B. Export query results to Excel and re-import them
C. Use only one query per dashboard
D. Use parameter forms in every subreport
Q6. To avoid repeated parameter prompts for each query on a report, what method should you use?
A. Gather the parameters from unbound text boxes on a form
B. Leave all queries unfiltered
C. Hardcode the date into every query
D. Use only static criteria inside the report
Q7. What simple formatting trick can force text to uppercase in an Access field?
A. Input masks with symbol #
B. Data type property set to UPPER
C. Greater than symbol in the Format property
D. Required field option enabled
Q8. For greater flexibility with inconsistent data patterns, what's a better alternative to input masks for formatting?
A. Formatting via code in the After Update event
B. Only use Data Macros
C. Add formatting in the table design
D. Use queries to permanently update values
Q9. What is a common cause of Access template files being blocked from running, even after placing them in a trusted folder?
A. The file is too large for Access security
B. The file still has the "Mark of the Web"
C. The file is not split into frontend and backend
D. Access cannot open files from the internet
Q10. Where should you store your Access database to minimize security warning issues related to OneDrive?
A. In your Downloads folder
B. In a true local folder such as C:\\AccessDatabases
C. On your Desktop
D. In your Documents folder synced with OneDrive
Q11. In VBA error handling, what does putting line numbers in code help with?
A. Determining which version of Access was used
B. Quickly locating where an error occurred using the ERL function
C. Changing variable scope
D. Automating all error handling
Q12. What is the downside of manually adding line numbers to VBA modules?
A. Code runs slower
B. Difficult to maintain and update as code changes
C. Only works with SQL Server
D. Makes error handling impossible
Q13. What is the primary reason that reusable custom controls are not common in Access forms?
A. Access does not support user-defined controls like other frameworks
B. Controls cannot be copied between forms
C. You must use subdatasheets for all custom controls
D. Only labels are reusable
Q14. How can you create reusable components like combo boxes in Access?
A. Build a template form and copy controls (and related VBA) as needed
B. Install add-ins for custom components
C. Use macros everywhere
D. Store controls in table fields for reuse
Q15. How does passing an object ByRef vs. ByVal in VBA affect the calling code?
A. ByRef means changes in the sub can affect the original object variable
B. ByVal prevents use of the object in the sub
C. Both methods make copies of the object
D. ByRef is only used for numbers
Q16. If a combo box in Access is loading slowly, what is the recommended course of action?
A. Optimize the row source query and index your tables
B. Switch to using HTML forms
C. Use multiple combo boxes to split data
D. Rely on parallel processing in Access
Q17. What is the common cause of the "expression is typed incorrectly or too complex to evaluate" error with between date criteria in Access?
A. The technique no longer works in new Access versions
B. Syntax errors in field, form reference, or date format in the parameter
C. Lack of enough RAM
D. Using too many tables in a query
Q18. Why is Access still considered valuable for rapid application development?
A. It requires advanced knowledge of web stacks
B. It lets you quickly move from idea to working database without overhead
C. You must always use SQL Server as a backend
D. Only experienced C# developers can use it
Answers: 1-A; 2-A; 3-B; 4-C; 5-A; 6-A; 7-C; 8-A; 9-B; 10-B; 11-B; 12-B; 13-A; 14-A; 15-A; 16-A; 17-B; 18-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I address several common concerns and questions that Microsoft Access users encounter, starting with an issue that can be quite frustrating: why does Access sometimes limit the number of records a query returns even when you know there are more records in your data?
There is a particular setting in Access queries, known as TOP VALUES. If this is set to a number like 99, your query will only return that many records, regardless of how much data is in your table. This limit is set either in the query properties or directly in the SQL statement. If you suddenly notice that your queries are not displaying all your expected results and you have not applied any filtering criteria, check the TOP VALUES property in your query's property sheet or look for a "SELECT TOP" statement in SQL View. Setting this back to All ensures you see the complete set of results again.
In this Quick Queries video, I also tackle a wide variety of related topics:
A student asked about adding line numbers in the Access VBA editor. Unfortunately, there is no built-in feature in Access VBA to display persistent line numbers as seen in more modern programming environments. However, there are third-party add-ins like MZ Tools, and you can add line numbers manually if you want to make error tracking easier, since VBA's ERL function reports the line number where an error occurred. To help with debugging, you might decide to number your code lines in increments of 10, reminiscent of old BASIC coding practices. Advanced users can automate this using custom Access code, such as the AddLineNumbers function I have available in the Code Vault for Gold Members.
Another question involved running a split Access database with SharePoint as the backend. SharePoint support is possible by linking tables to SharePoint lists, and some users make this work for years. Still, SharePoint prioritizes document storage and collaboration, not database performance, and you can run into permissions issues and performance bottlenecks. For serious multi-user Access databases, SQL Server is generally a much better backend, with improved performance and reliability. I have a full seminar and a beginner's course on SQL Server for Access users if you'd like to explore this route.
Displaying multiple statistics from separate queries on a single dashboard or report is a frequent need. If you have multiple queries giving you single-value results for management stats, these can be displayed using DLookup in a main report or form. Where queries return multiple rows, use subforms or subreports. A common pitfall here is that each query prompts you separately for parameters like date ranges. The best approach is to store parameter values in unbound fields (such as text boxes) on a form, and reference those fields from your queries, which eliminates repetitive pop-up prompts.
We also discussed a useful tip shared by a viewer: you can force text to uppercase in a field by placing the greater than symbol in the format property. This is particularly handy for keeping things like UK postcodes consistent. Input masks serve a purpose for entry guidance but often become restrictive, so for more complex needs, programmatic formatting using VBA's UCase or LCase functions gives much more control.
The topic of trusted locations in Access came up, particularly how security measures in Windows and OneDrive interaction can block Access databases from opening properly. Unblocking files in Windows properties and placing your database in a true local folder (like C:) that you add to Access's list of trusted locations typically resolves these security warnings.
A member shared additional code for more robust null and "Nothing" checking in VBA, expanding on previous tips I offered for identifying and handling these values.
In the discussion about managing website files, my recommendation is to use EditPlus for editing and WinSCP for uploading and transferring files via FTP. These tools are reliable and straightforward for website file management, while built-in file managers are often clunky.
Someone requested a tutorial on building microfinance tracking databases. While I have not created a microfinance-specific system, all the necessary technical components (customers, accounts, loans, payments, reporting) are found within my core Access courses, and these principles can be adapted to many types of applications.
Back to Access queries, if you're seeing exactly 99 records with no obvious filter, it's nearly always the TOP VALUES setting. Clear it to restore full results.
One viewer mentioned problems with between date criteria generating an error about complex expressions in newer Access versions. The solution is to double-check your syntax and form references. This technique has not changed between older and new versions; syntax mistakes are almost always the cause.
Regarding long-running Access databases and random weird errors, memory leaks (especially caused by repetitive Windows API calls) can accumulate over time. If you keep Access running 24/7, it helps to have a procedure that closes and restarts the application periodically to keep things stable.
Several comments came in on my new SQL Server for Access Users course. Those are being collected for a dedicated Q&A lesson, since not everyone using Access is interested in SQL Server.
Access still holds a unique place as a database tool that allows rapid development and deployment of working applications without the overhead of modern web platforms. If you have used it in the past, it is still a solid choice for many internal development projects.
On the subject of reusing custom controls like combo boxes in Access, while reusable "custom controls" in the sense of .NET or web development are not really supported, you can copy and paste well-configured controls (and their code) between forms. For more advanced reuse, you might explore class modules, but typical solutions stick with replicating form elements.
Access is single-threaded, so attempts to speed up controls like combo boxes through parallel processing are not supported. For slow controls, look to query optimization and better indexing instead.
There was also a tip about passing objects ByRef versus ByVal in VBA. If you pass a form ByRef and set it to Nothing inside a subroutine, you could inadvertently nullify the original variable in the calling scope. To avoid this, pass it ByVal when you do not want that behavior.
Throughout today's video, we discussed how query settings like TOP VALUES can affect your results, best practices for using Access with SharePoint, the benefits of form-driven parameters, techniques for consistent data formatting, security around trusted folders, code tricks for debugging, recommendations for file management tools, application design tips, and important VBA nuances.
If you would like to follow a step-by-step video tutorial that goes into all of these topics in detail, you can find the complete video on my website at the link below.
Live long and prosper, my friends.Topic List Displaying line numbers in the VBA editor
Workarounds for line numbering in Access VBA
Using the ERL function for error location in VBA
Manual and automated methods for adding line numbers
Error handling with On Error GoTo and ERL
Using SharePoint as a backend for Access databases
SharePoint limitations and configuration issues with Access
Choosing SQL Server versus SharePoint as an Access backend
Displaying multiple query statistics on a dashboard
Combining single-value queries in reports using DLookup
Using subforms and subreports for multi-value queries
Solving query parameter prompts with form-based parameters
Formatting data to uppercase using the format property
Input masks versus code-based data formatting
Forcing consistency in case using UCase and LCase in VBA
Dealing with Access security warnings and trusted locations
Unblocking files and removing Mark of the Web
Trusted folder setup to eliminate Access security warnings
Managing file transfers for web hosting (Winhost, EditPlus, WinSCP)
Fixing Access query output limited by TOP VALUES setting
Locating and changing TOP VALUES in Query Design and SQL view
Diagnosing "expression is typed incorrectly" errors in queries
Date parameter prompts and troubleshooting query syntax
Handling random Access issues and memory leaks in long-running databases
Automating MSACCESS.EXE restarts to solve stability problems
Copying and reusing combo boxes and controls across multiple forms
Optimizing combo box speed with better row source queries and indexing
Understanding Access single-threaded processing limitations
Preventing unintentional variable changes with ByRef and ByVal in VBA
Passing form objects safely in VBA subroutines
Opening forms filtered to a single record versus combo box filtering
Using subforms and buttons to display related records for a customerArticle Sometimes when you run a query in Microsoft Access, you may notice that it only returns a limited number of records - say 99 - even though you know there are hundreds of matching records in your table. This can be confusing, but there is often a simple explanation for this behavior. One of the most common causes is the TOP VALUES setting in your query design.
In Access, when you design a query, you may not realize that the design window includes a property called TOP VALUES. This property allows you to specify that the query should only return a certain number of records. For example, if TOP VALUES is set to 99, the query will stop after returning 99 records, even if more records match the criteria. This setting is easy to overlook, especially if you didn't set it intentionally.
To check if this is the issue, open your query in Design View. Look for the TOP VALUES box in the property sheet at the top of the window. If it says anything other than All, such as 99 or 100, that is your culprit. Set it back to All, and your query will once again return the full set of matching records.
You can also check for this in SQL view. If you see a clause in your SQL statement like SELECT TOP 99, that is what is limiting your results. You can simply remove the TOP 99 portion to restore the full results.
Another common issue that can confuse you when working with queries is parameter prompts. Sometimes when you open a dashboard that pulls data from several queries, each of those queries will separately prompt you for parameters, such as dates. This happens if your queries include parameter prompts directly in their criteria, like typing [Enter Start Date] and [Enter End Date]. Instead of prompting each time, you can set up a cleaner, more user-friendly method by using form controls. Place unbound textboxes on a form, like your dashboard or menu screen, and reference those fields in your queries. For example, in your query's criteria, you can enter something like Forms![MainMenu]![StartDate] instead of using a pop-up prompt. This way, the user enters the values once on the form, and all queries referencing those controls will pick up the same criteria.
Some people also struggle with security warnings when opening Access files, especially if you downloaded a database or template from the internet. Access may block the file, displaying security notices or refusing to run code. To prevent these warnings, always unblock the file before opening it. Right-click on the downloaded file, go to Properties, and if you see an Unblock checkbox, check it and hit Apply. Then, place the file in a true local folder on your C: drive - avoiding folders like Desktop or Documents, which are often synced with OneDrive and can complicate things. Finally, add that local folder as a trusted location in Access. This ensures Access will trust files stored there, and you won't see the same warnings.
When it comes to using SharePoint as a backend for Access, many organizations are moving away from local shared drives and using SharePoint for file storage and collaboration. While it is technically possible to link Access to SharePoint lists as your tables, you'll often run into limitations, permissions headaches, and performance problems because SharePoint was designed more for documents than as a true database backend. If you can, use SQL Server instead - it is a much better fit for Access and avoids many of the quirks and bottlenecks you're likely to face with SharePoint.
If you're trying to display several summary statistics on a single dashboard - letting management see multiple live data points at a glance - a useful tool is the DLookup function. DLookup allows you to pull a single value from a table or query and display it on a form or report. For example, you could show the count of open tickets, the highest sales amount, today's order total, or any other single value, right on your dashboard. For more complex summaries, like lists of pending orders or upcoming appointments, use subforms or subreports to display those related records.
For neat formatting of user inputs - such as forcing capital letters in postcodes or codes - add a greater than symbol (>) in the Format property of the textbox or field. This simple trick automatically capitalizes all text entered by the user. For more control, especially if formats can vary, use the UCase function in the After Update event of the field to convert the value to uppercase with VBA code.
Access security can also be tripped up by files that retain an internet mark or are stored in locations synced with cloud services like OneDrive. If you keep experiencing issues opening Access files due to security warnings, review your trusted locations and always use local folders set as trusted. Remove any marks of the web from the file properties before opening. If your database is split (with separate front-end and back-end files), remember you may need to relink your tables after moving the files.
If you're working with VBA code in Access, you may wonder about line numbers in the editor to help with debugging. The built-in VBA editor does not natively display line numbers like modern IDEs, but you can add them manually at the start of each line - for example, 10, 20, 30 - in increments of 10, just as in old BASIC code. There are third-party tools like MZ Tools that can automatically add or remove line numbers, though use them at your own risk.
Adding line numbers is especially helpful when using error handling with the On Error GoTo pattern. By assigning numbers to each line, you can display the line number with the ERL function in your error handler, making it easier to pinpoint where an error occurred. Here is an example:
Sub ExampleRoutine()
On Error GoTo MyError
10 MsgBox "Hello World"
20 MsgBox "Hi There"
30 Dim x As Long
40 x = 10 / 0
MyExit:
Exit Sub
MyError:
MsgBox "Error in line " & Erl & " - " & Err.Description
Resume MyExit
End Sub
When the code runs and hits division by zero at line 40, the error handler will tell you "Error in line 40 - Division by zero," helping you quickly locate and fix the problem.
You can automate the addition of these numbers with VBA code as well. For example, a routine to add line numbers to a module might look like this:
Sub AddLineNumbers(ModuleName As String)
' Pseudo-code to add line numbers to each line of code in the specified module
' (Implementation details are more involved and typically use the CodeModule object)
End Sub
That's a more advanced topic, but the general principle is that line numbers make debugging much easier if you use them consistently.
If you're trying to build reusable controls in Access - like a combo box for selecting customers that you want to use on multiple forms - Access doesn't support true custom controls like other development environments. The best approach is to build the combo box once on a form and then copy and paste it into other forms as needed. If you always use the same field names and data sources, it will stay compatible. You can also copy any associated VBA code with the control. While you can build advanced solutions using class modules, most developers simply use the copy-and-paste approach for reusable UI elements in Access.
On the topic of performance: if your combo boxes are slow to populate, parallel or concurrent query execution is not supported in Access. All operations are single-threaded, so improving performance means optimizing your queries and indexes, or caching lookup data in local tables.
Finally, some advanced VBA tips: be careful when passing variables between procedures. By default, variables are passed ByRef, which means if you change the variable inside the called procedure, those changes persist in the calling procedure. If you want to avoid this, pass objects or variables ByVal instead, which will prevent unintended side effects, such as setting an object to Nothing and affecting the original reference.
Microsoft Access is still a powerful, fast way to build real working database solutions, especially for internal tools and data tracking applications. Many modern organizations still rely on it, and with a little know-how, you can overcome most of the quirks and limitations you'll encounter. Remember to check your query settings and form controls if you notice unexpected behavior, and take advantage of Access's flexibility in connecting to other backend systems like SQL Server for bigger projects.
You can keep up with updates, new tools, and techniques by visiting my website and joining the mailing list. If you have specific questions or want to request content on niche topics like microfinance database solutions or troubleshooting complex VBA scenarios, feel free to reach out or leave a comment. Access might have its quirks, but with a few good practices, it's still one of the best platforms for quickly turning ideas into robust database applications. |
