Quick Queries #32
By Richard Rost
14 months ago
Null, Zero Length String, Next Business Day, BrainWave!
In this Quick Queries tutorial, I will address a variety of user-submitted questions focused on practical Microsoft Access issues. We'll explore the difference between using nulls and empty strings for field updates and their impact on space efficiency and query performance. You'll also learn strategies for managing large database files nearing the two-gigabyte limit, including database splitting and using SQL Server. I'll provide solutions for avoiding accidental code changes across multiple databases by leveraging shared modules. Additionally, we'll cover tips for using VBA to launch multiple queries and advanced techniques for optimizing field searches in databases with millions of records.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, null vs empty string, space efficiency, ACCDB file size, compact and repair, split database, one-to-one relationship, SQL Server upgrade, shared functions module, VBA subforms, indexed fields, query optimization, RegEx in Access, toggle close button, Windows API, overlapping windows, next business day calculation, networkdays function, continuous form new record, class modules, DOS-based system upgrade, developer efficiency.
Intro In this video, we'll talk about several common Microsoft Access topics sent in by viewers, including the differences between null and empty string values in your tables, strategies for managing database size as you approach the two-gigabyte limit, and tips for sharing modules across multiple databases. We'll also cover how to copy full file paths with a simple Windows trick, handle searches across different tables, use regular expressions in Access, and discuss toggling close buttons on forms. Other questions include working with business day calculations and understanding class modules in Access.Transcript This is Quick Queries number 32. What's a Quick Queries video? Well, I get a bunch of questions all week long, and they don't all need their own video, so I mash them all together, and that's what Quick Queries videos are about. So let's take a look at our first question.
First up is a question from Sean, one of my silver members. He says, when blanking out fields before writing new values, does it make sense to update them to null or an empty string? I'm wondering about space efficiency since my ACCDB file is nearing two gigabytes and struggling to complete update operations.
Okay, a lot to unpack here. First of all, there's not much difference between null and empty string as far as space and your database goes for most average-sized databases, depending on the number of records that you have and a couple of other things. Back in the 80s and 90s, when hard drive space was at a premium, it really made a big difference. But now it's not that big of a deal with these giant drives. But if you're getting close to that two-gigabyte limit, it might be.
I believe if memory serves, a null value takes up no space, but an empty string takes up two bytes. So I guess if you've got millions of records, it could make a difference. Now nulls are generally better for query performance. So it's a little bit faster for the database to run if you're doing queries based on nulls. But the main difference to me between a null and an empty string is that a null says, I don't know what this value is. Like if you're entering a customer record, you didn't get their address, you leave the address fields null.
Whereas an empty string says, I know what this value is, it's none, it's nothing. So for example, middle name fields, if it's null, you might just not know what their middle name is. But if you make it an empty string, that means this person has no middle name. My grandfather, perfect example, his name was Benny Spardoodie. He had no middle name. His dog tags from the Navy said, Benny, none, Spardoodie. So that's when I use an empty string to indicate, I know that this field has no value.
Now, if your database is pushing two gigabytes, there are a few things you can do. First, make sure you regularly compact and repair. That might shrink things down. You can always split the database. Even if you've got your database split into front and back ends, you can still split your back end into multiple database files. I have probably 10 different databases that have multiple back end files. You can put customers in one ACCDB file, and you can put orders in another one. That way, your limitation is two gigabytes per table.
Even then, you can split tables up. Let's say you've got a table with tons of fields in it, but not everybody has all of those fields with data. For instance, you've got a first name, last name, address for most everybody, but then you've got a bunch of other stuff like demographic information or whatever that you don't have for everybody. You can split that stuff off into another table as well. You just use a one-to-one relationship between those. There are lots of tricks you can do to stay under that two-gigabyte limit. I have videos on most of this stuff. I'll put links down below.
Of course, the ultimate solution to this is if you're running over a gigabyte or close to it, just upgrade to SQL Server. It has a much larger limit. Great question. If they're empty strings and for no other reason than you just want to make it blank, then sure, change them all to nulls, and you'll save a tiny bit of space. But that's the only time I ever use an empty string: to indicate, I know it has a value, and the value is blank.
Next up, we have a question from Alberto, one of my silver members. He says, I've been using your technique of sharing a functions module across multiple access databases. Recently, I made a change to one of the functions that accidentally broke two or three of my other databases. What's the best way to prevent this kind of issue?
The only way to prevent it 100% of the time is to not do it. For everybody else, this is the video he's referring to. I teach you how to make a module that you can then share between different databases, so you don't have to keep copying the same code. Let's say you have a function. Here, I wrote one called double it. It just takes X and multiplies it by two. Whenever you call the double it function, it does that and returns a value. You can have that module in all of your databases, so you don't have to keep copying and pasting code.
This works great for simple things that you know aren't going to change much. I have one that just takes in the first name, middle name, last name, and then I think prefix and suffix too. Then it returns a properly formatted string taking into account all of those variables. It could be Mr. Richard Rost; it could be Mr. Richard D. Rost Jr. That kind of thing, and it puts the spacing in. That's something that chances are, if I make a modification to it, it's not going to break something inadvertently.
But, if you have something more complicated that does a bunch of steps and you make a minor change to it, you have to anticipate all of the ways that you're using that code in all of your databases. I've done something like this too. I've broken databases because I changed a sub or a function and didn't realize, oh, three other forms were using the sub in a different kind of way. You just have to be careful.
I would say if it's something simple like I just mentioned, put it in a shared module. If it's complicated or at all specific to one database, keep it in that database. That's just my opinion. And of course, the proper solution to most of this is to back up and test and back up and test and always keep backups of your backups. Keep historical backups too, in case you realize, oh, I changed something two weeks ago, you can go back three weeks and get the backup from then. I have a whole separate video on backups. I'll put a link down below for that too.
Next up, a tip from Paul, I'm always trying to get the full path and file name of a file somewhere to use in my code. I usually go into the explorer, get the address of the address bar, then copy and paste the file name and put them together. Paul says, for copying a file path, you can hold down the Windows key and press R that will launch the run box. If you drop a file or folder onto it, it will give you the full path, which you can then copy.
To which I reply, that's a neat little trick. I'll never remember it myself, but I'll share it with you guys. Let's test it. Windows R, there's the run box. I'm familiar with that. Here we go. Here's a file on my desktop. Click drag, drop. Well, I'll be. There it is. There's the whole thing right there. Cool little tip. There's so many of these cool shortcut tips. I'd go crazy. My advice is to find ones that you think are really neat and you're going to actually use on a daily basis and use those because I'll never remember all the millions of tips that I've come across over the years.
A couple of questions on my recent Search Multiple Fields video, Lindsay asks, excellent video. A quick question. Can I search for the same number from two tables or queries at the same time? Sure. If those tables are related, just join them together, and then the result will be in the same table. I got customer ID and order ID in that query that I used in the video. You could search for that. If it's either a customer ID or an order ID, it'll show up. If they're not related, you'll just have to launch two separate queries to search for them. If you got customers and, I don't know, dogs, whatever, and of course, if the customer's not related to the dogs. You know what I'm saying? You just have to launch two separate queries.
You can't launch two queries with the command button wizard the way that it is in the command button toolbox there. It'll only launch one query at a time. You need to use a tiny bit of VBA, but you could run two queries with two different open query commands. You'll see them both on this screen at the same time. Or you can make a form with two subforms. There are a million ways you could do it. Can you do it? Yes, absolutely. I don't know how you want to approach doing it.
Next up, Sage from Korea says he has a question. If you've got 10 million records, which one of those methods is faster? The one where you combine all the fields with the ampersand, if you concatenate, or the one with the like on each field. If your fields are indexed, it will definitely be faster if you put the or condition on each field. If you concatenate them all together, then that's extra overhead that the database has to do on each record. It's going to add all those together. I think I might not have made it perfectly clear in the video, but the concatenate trick is really for small databases. It's just easier to write. If you're working with a small database and you've got a whole bunch of fields, and you want to smash them together, if you're dealing with 10 million records, you definitely want to make sure your fields are indexed properly. You do want to have the or condition on each field because that will run faster. As soon as the database engine can throw out a record, it will. It doesn't have to concatenate everything together first.
A few different people have asked the same question. B8 asks, hello, is there a way to read text and Arabic on access? I watched a video of you reading text in English. First of all, I'm not exactly sure what you mean. Second of all, I don't know. I don't speak or read any other languages except for English. That's one of my deficiencies of skill when it comes to anything, really; I am an English speaker only. If you have questions about Arabic, maybe find an Arabic access group and ask them. Or if any other readers on here speak Arabic, someone post a comment and let them know.
Next up is a question about my toggle close button video, where I show you how to turn off the little close button up here in the upper right corner of the window. So turn it on and off. M.O.6 is asking, can you use the same principles if you're using the tab forms instead of floating windows? The answer is I don't know. I never use the tab document interface. I didn't like it. I didn't use it when it first came out. The very first thing I did was switch back to the overlapping windows. It's one of the first things that I cover in my access beginner one class so that we're all on the same page.
I have a whole separate video on just how to do this because this is not very efficient. Having a big giant form with one thing. This is much more efficient. Having a menu and all the little forms that open up next to it. This is a traditional access database. This, I don't know. You might as well use Excel with different sheet tabs and stuff. It depends on how you use access. Access is versatile. It'll do both. This is how I like it. This is what I teach. I could take five or ten minutes and try it in a tab document interface, but you will learn better if you try it yourself. Pop the code in. See if it works. If it works, great. If it doesn't, well, you just learned something. In fact, I got a whole video on that topic. Try it yourself. If you have a question about something, and if you think you can figure it out, I guarantee you will learn better by figuring it out on your own. If you take five or ten minutes and try it and you can't figure it out, then ask an expert. Post in the forum. But give it a shot. I've learned most of what I know from books and from just, oh, let's see if we can do this. Let's tear this apart. Let's just experiment. Have fun with it.
Next question from BT Basset. In the same video, he wonders if it's possible to use the API calls to hide the buttons on the main access app window. I tried replacing form Hwind with application Hwind access app, but no luck. This is a sample question where he had a question but tried it himself first, which I applaud. The short answer is I don't know. I've never tried it. I asked Chat GPT, as I do sometimes if I'm stuck. I find it more efficient than googling stuff now. I have not tested this myself yet, but it says yes. It's possible to manipulate the access applications window using the Windows API. Here are some different things they gave. Here's some sample code, which you can screen capture or copy or whatever. Or ask Chat GPT yourself.
Notes: It will permanently remove the buttons until access is restarted. If you hide the close button, the user won't be able to close access normally. Although I'm pretty sure they will be able to hit alt-F4 or kill the task with the Windows Task Manager. Test carefully because messing with access is Windows system. You can sometimes cause unintended behavior. I have experienced some weird behavior in access with Windows APIs. Then it asked, would you like me to provide a version that allows toggling these buttons on and off? I'm like, yeah, sure. I don't know. So here's a toggleable one. Very similar. I'm going to move over to the right so you can see all the code. You can pause the video and grab the code if you want. There's the rest of it. Usage: Toggle on and off.
It even says right here, this does not prevent closing access using alt F4 or the task manager. I don't know. Someone try this and let me know if it works. I've never had a need to do this myself because any decent computer user knows how to kill an application.If you've got people using your Access database, if they don't know Alt F4 and they don't know how to kill a task, well, I'd be surprised. But any semi-intelligent computer user is going to figure out how to close the application. So I've never tried to do this myself, but you asked. So Chat GPT says it's possible. Let me know if it works.
Jade Dragon says crazy how convoluted it has to be anytime I want a close button to at least temporarily not be there. I get rid of the control box and have an X close button at the top. Of course, the button image I've been using looks off since it's from Windows XP. Yeah, I do that myself too. And it's convoluted because certain things that you think Access would have built into it, like the ability to turn off and back on again, the close button. That should be a toggleable setting. I don't know why it isn't. Sammy, add that to the list, I guess. It's not in there. So you've got to use a Windows API call, and that's just going over Access's head. It's like saying, hey, hey, Daddy, take care of this for me. But I feel what you're saying, though.
I have a little button myself. I don't even bother putting an X button on it. I just make a little button in the upper right corner that just says close window. That's mine right there. It's literally just as close form. I don't even care. This is in my customer service form. Because a lot of time when my customer service form opens, it's a continuous form. It shows all the customer service emails I have to answer for the day. And when I click on one, I have an on current event that opens up the customer's record next to it, another reason why I like to use overlapping windows. And that will show me all their relevant information too, like their orders and whatever. Because nine times out of ten, their questions are about their orders and the courses that they have, like this person's asking if they can retake classes they purchased in the past. And yes, of course, you can. There's no time limit on them.
But the problem is that if I close one and move to the other while that on current event is running, sometimes I get impatient. I hit the close button, it closes that, and the event that was opening up the customer form crashes, and it causes a VBA error. So I did this to prevent myself from closing this window while the event is running. So I hide that, run the event, and then put it back. And I did this years ago before I came up with this code.
So that's why next up, we got early Ramirez asking a question about the search multiple fields extended cut. He says he Googled doing search in MS Access with regular expressions and your videos and not come up.
Using SQL for searches is great, but if you use regular expressions, that would be more efficient language. And if you did a video about it, it would be like Captain Picard using the Tamarian language in season five, episode two. Now, any question that throws a good Star Trek reference in there, you have my thumbs up. That's one of my favorite episodes. For those of you who don't know, it's Darmok. This is where Captain Picard gets sent down to a planet with a Tamarian. This guy, played by Paul Winfield, was also in the Terminator, great actor. And he has to basically learn the language, and the universal translator doesn't work because it's based on metaphors. It's a really weird, unique language. Darmok at Tanagra. And Darmok and Jalad at Tanagra. It's been a few years since I've seen it. It's a great episode.
One of my favorite other episodes with Picard having to learn a language is from the season one episode called Dixon Hill. That's one of our first holodeck malfunction episodes, and you can tell season one because Rucker doesn't have the beard. Then we get this gem. This is John Luck Pickard, captain of the Enterprise. And Claxon, Reese, Blashblane, Arnick, Karnick. And Treswolla. Yeah, that was great.
All right, but now let's get down to the question. Regular expressions. Regular expressions are a way to validate strings or to enforce specific formats like zip codes or phone numbers using a library called RegX. It's not built into Access. It's not built into VBA. But there is a library reference for it available. It's called the Microsoft VBScript Regular Expressions Library. And then you can write functions that look like this. This is the valid pattern for a RegX email address. Yeah, it's pretty complicated. I personally have never really needed to use a RegX. I know it's been around for a while, but everything that I've needed to do, I've just used basic string manipulation functions for. And as any of you who've been following my channel for a while know, I generally try to avoid external libraries. If it's not something that's built into Access, I try not to use it. Plus, Microsoft has announced plans to deprecate VBScript. So if it's going to be around for a while, who knows, there'll probably be something else that comes around that will take its place. I know there's some scripting options like this in PowerShell and .NET. But if enough of you are interested, I'll do a video on regular expressions.
Like I said, in my 30-year career of using Access, I really haven't needed it. So I haven't bothered. But great question. And thanks for the awesome Star Trek reference.
Next up, a question about my next business day video where I teach you how to find the next business day. So if it's a Monday, it'll give you Tuesday. If it's a Wednesday, it'll give you Thursday. If it's a Friday, it'll give you a Monday. And we do some basic math looking at the weekday function.
Tata Dagnogo wants to know how you would get the next five business days' date from any particular date? Well, you could do it with the same similar math that I show in this video. So if you want five days, five business days in the future, they'll be basically the next week. Think about it. So if it's Monday, you're going to go Tuesday, Wednesday, Thursday, Friday, Monday. So essentially, I would just add seven. And basically, that's the date. Five business days is the next week.
Now the only exclusion to that would be if you want to take into consideration holidays and things like that. And if that's the case, I would use my workdays function. I call it my net workdays function. Excel has the same function where you can give it two dates, and it'll figure out the number of workdays between them. You would basically just be reverse engineering this. You'd say, give me a start date, and I want to add this many workdays to it. And it just runs a loop. And it's going to exclude any holidays. And in the extended cut, I show you how to exclude any custom holidays. So if your business has certain days they're taking off, like a national Star Trek lovers' day, if you're giving that day off, you can add that to your own custom table. And it will include those in the calculations. So check out this video.
Herubbing could ask if you have a continuous form or a data sheet form, is there a way for the new record to be at the top instead of the bottom? Well, first of all, I never use data sheet forms so I don't know about that. But a continuous form, yes, you absolutely can do that. And of course, I've got a video on it. Go check it out. I'll put a link down below.
George wants to know if I'm going to be doing any videos on classes in Microsoft Access. Yes, I'll definitely be recording a lesson or two on classes and building class modules. But it's probably going to be an Access developer lesson as part of my full developer course. I've had it on my to-do list for a while now. I may or may not do a short TechHelp video on it, but it's a pretty in-depth topic. So it's most likely going to be a developer course. And I'll be honest, there are some neat uses for it. But in Access, like I said, in my 30 years of a career with Access, I've never needed a class or a class module. I've built them for fun a couple of times in some sample projects, but I've never had anything that I had to do that required a class. Classes are great in object-oriented languages, C++, that kind of stuff. I used to program in that years ago. But again, in Access, I've never needed one. But I'm going to show some cool examples of things you can do with them. We'll see. I'm probably going to do a developer course on it to go in-depth and then maybe throw a quick TechHelp video together just to give the basic overview.
I've hidden this user's name as I don't want anybody getting in trouble at work, but they said that they might get to put their new skills with Microsoft Access to use at some point in the future. A new manager at work is really keen on moving some of the stuff from pen and paper to computers. Or who knows? Basically pushback from new computer stuff.
I've dealt with a lot of clients like that in the past. You get one person in there, maybe a young eager manager who's all about computerizing stuff and then the boss who's 80 years old and is still using a slide rule is like, no, I like my index cards. I've dealt with lots of companies like that where the people who actually do the work are the ones that want to be more efficient and they want an updated database. I remember one of my first clients when I first started doing this back in the 90s, I'm not going to name names, but it was an old company. They'd been around for 100 years, manufacturing company. They were still using a DOS-based system, probably written in the early 80s. It was running on something super old. Like I had this computer in my school in like eighth grade, I think it was. And I had to drag them screaming and kicking into Windows. And it was like pulling teeth. The people who actually were the ones working every day, putting in the purchase orders and managing the inventory, it took them eight days to do what the new database could do in like two hours.
The funny thing is, though, when everything was set and done, it took me like three months to build the system for them and it worked great. I was very proud of myself. But then when it was all done, one woman comes up to me and she says, you know, this was my whole job pretty much. Was this process that took eight days? You're going to cost me my job. And then I just basically said to her, look, you don't have to let anybody know how long it really takes you to do inventory now. So yeah, there's efficiency and then there's job security. So be careful out there, developers. Don't make anybody mad.
And finally tonight, T. Koving asks, is there a way to link Neuralink and Chat GPT with some Access API to just automatically build a database as I think it up? Sounds like a developer level for sure. Yes, absolutely. I came up with this technology a couple of years ago. I call it brain wave training. I developed it for training, not necessarily for building databases, but I guess you could modify the technology very easily. Here it is. Check it out. I'll put a link down below.
All right, folks. That's going to do it for this week. Thanks for spending some time with me. That's your Quick Queries number 32. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Differences between null and empty string
Impact of null and empty string on performance
Managing database size nearing two gigabytes
Strategies for compacting and splitting a database
Using SQL Server for large Access databases
Sharing a functions module across databases
Preventing issues when updating shared modules
File path copying trick using Windows R
Searching for the same number across tables
Performance impact of using indexed fields
Reading text in different languages in Access
Toggle close button in floating vs tab forms
Using Windows API to hide application buttons
Using regular expressions in Microsoft Access
Finding the next five business days' date
Order of new record entry in continuous forms
Classes and class modules in Microsoft Access
COMMERCIAL:
In today's video, we're going to dive into Quick Queries number 32 where we address a range of intriguing questions from our viewers. We'll discuss space efficiency by comparing null and empty string values in Access databases, especially when nearing that two-gigabyte limit. You'll learn how to handle changes in shared function modules to prevent breaking your databases. We'll also share neat Windows tricks for copying file paths, and explore options for searching across multiple tables and dealing with large datasets efficiently. Curious about utilizing regular expressions in Access? While they aren't built-in, you'll find insights on implementing them with a special library reference. Finally, we'll cover clever solutions like toggling close buttons and tips for managing business days. 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 primary difference between a null value and an empty string in a database?
A. Null indicates unknown or missing value; empty string indicates a known value that is intentionally blank.
B. Both are the same and used interchangeably in databases.
C. Null is used for numeric values; empty string is used for text values.
D. Null takes up more space in the database than an empty string.
Q2. What is one possible reason for experiencing updating issues with an ACCDB file nearing two gigabytes?
A. The file format is outdated and needs conversion to a newer format.
B. The database might be fragmented and requires compacting and repairing to decrease its size.
C. There's too much data in the front-end database.
D. The ACCDB file has too many user permissions set.
Q3. When dealing with large Access databases, what is one method to avoid reaching the two-gigabyte limit?
A. Use only one large table for efficiency.
B. Split your database into front-end and back-end files and possibly into multiple back-end files.
C. Store additional data in hidden forms rather than in databases.
D. Regularly delete old data from your database.
Q4. What is a potential risk of sharing a function module across multiple Access databases?
A. Using a shared function module increases the processing time of queries.
B. All databases are automatically linked, which cannot be undone.
C. Modifying a shared function can inadvertently break the databases using it.
D. It requires converting all files to SQL Server for compatibility.
Q5. Regarding search queries in Access, what affects query performance with large data sets?
A. Using complex VBA code in queries.
B. The physical order of fields in tables.
C. Whether fields are indexed and how search conditions are structured.
D. The number of concurrent users accessing the database.
Q6. What would happen if you try to use VBA code to disable the close button on a tabbed document interface in Access?
A. The code will execute successfully without issues.
B. It might not work, as the feature primarily supports overlapping windows instead.
C. It causes Access to automatically close the program to prevent errors.
D. It makes all close buttons on Windows applications unusable.
Q7. What is the recommended way to deal with unexpected changes in a shared module?
A. Always work without backups to ensure you're focusing on correct changes.
B. Use a common module for all functions, regardless of complexity or specificity.
C. Maintain separate versions for complex, database-specific functions, and regularly back up and test beforehand.
D. Limit your shared modules only to mathematical operations to minimize risks.
Q8. Why are regular expressions not commonly used in MS Access VBA?
A. There are no known libraries to implement regular expressions in Access.
B. Microsoft plans to deprecate VBScript, which supports regular expressions.
C. Access can automatically convert string formats without regular expressions.
D. They are part of the premium features only available in SQL Server.
Answers: 1-A; 2-B; 3-B; 4-C; 5-C; 6-B; 7-C; 8-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, I'm addressing a collection of questions that don't warrant their own individual video, so I've compiled them all into this session. Let's dive into the inquiries together.
Starting with a question from Sean, one of our silver members, who asks about space efficiency when blanking out fields before updating values—whether it's better to use null or an empty string, considering his database is near the two-gigabyte limit. In general, the difference in space between null and an empty string is negligible for most databases unless you're dealing with millions of records. Historically, this mattered more when storage was limited, but it's less of a concern nowadays unless approaching the two-gigabyte Access limit.
A null value technically takes up no space, while an empty string requires two bytes. Although nulls might offer slightly better query performance, the main distinction is conceptual—null indicates an unknown value, whereas an empty string explicitly signifies no value. For instance, a null middle name field suggests you don't know the person's middle name, but an empty string means the person has no middle name, like my grandfather, whose dog tags read "Benny, none, Spardoodie."
Approaching the two-gigabyte database limit requires solutions such as regular compacting and repairing, splitting your database into multiple files, or separating tables with sparse data into one-to-one relationships. Ultimately, upgrading to SQL Server is advisable if you're consistently nearing the capacity.
Next, Alberto, another silver member, mentions a strategy for sharing functions across databases but encountered issues after updating a function that broke other databases. The surest way to avoid this risk is not to share modules at all. Shared modules are best for simple functions unlikely to change, but separate them if complex or impactful changes are needed. Always back up your databases before and after updates, keeping historical backups just in case.
Paul offers a useful tip for copying file paths quickly by dragging a file or folder onto the run box, giving you the full path to copy. While I find it a neat trick, I emphasize remembering shortcuts that suit your daily tasks since there are countless tips out there.
Lindsay asks if it's possible to search for the same number across two tables or queries simultaneously. If they're related, simply join the tables. Otherwise, you'll need to run separate queries or use VBA to open multiple queries concurrently.
Sage from Korea inquires about which method is faster when dealing with 10 million records—concatenating fields or an OR condition on each indexed field. For large databases, the latter is more efficient as it helps discard irrelevant records sooner.
Several people, including B8, ask about reading text in languages other than English in Access. Unfortunately, I speak only English, so I suggest seeking out community forums for guidance.
Regarding my toggle close button video, M.O.6 asks about using the principles with tabbed forms versus floating windows. While I haven't tested it on tabbed interfaces, I always encourage experimentation—it's the best way to learn.
Responding to BT Basset, who explored using API calls to manipulate the main Access app window, I haven't tried it myself, but Chat GPT indicated it's possible. Experimenting with API calls can be troublesome, so proceed with caution, and share your findings if you attempt it.
Jade Dragon comments on Access's lack of built-in toggling for the close button, which requires convoluted solutions. I agree—such functionality should be more accessible.
Eruly Ramirez references my search multiple fields extended cut video, expressing interest in using regular expressions for more efficient searches. Though not native to VBA, you can use the VBScript Regular Expressions Library. However, I'm cautious about external libraries due to potential deprecation by Microsoft.
Tata Dagnogo seeks advice on finding the next five business days. Typically, adding seven days provides the answer, barring holidays, which require using a workdays function that accounts for custom holidays.
Herubbing asks about positioning new records at the top of continuous forms, which is feasible and discussed in another video linked below.
George inquires about future videos on classes in Access. While I plan to cover them, they will be part of a more advanced Access developer course due to the topic's complexity.
I received a question about managers transitioning from paper to computer systems. It echoes my experience with companies hesitant to embrace modernization, highlighting the need for tactful implementation of new technology.
Lastly, T. Koving humorously proposes linking Neuralink and Chat GPT with Access to automatically generate databases—a concept reminiscent of futuristic tech I'd love to explore.
That's it for Quick Queries number 32. Thank you for joining me, and remember, 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 Differences between null and empty string
Impact of null and empty string on performance
Managing database size nearing two gigabytes
Strategies for compacting and splitting a database
Using SQL Server for large Access databases
Sharing a functions module across databases
Preventing issues when updating shared modules
File path copying trick using Windows R
Searching for the same number across tables
Performance impact of using indexed fields
Reading text in different languages in Access
Toggle close button in floating vs tab forms
Using Windows API to hide application buttons
Using regular expressions in Microsoft Access
Finding the next five business days' date
Order of new record entry in continuous forms
Classes and class modules in Microsoft Access |
