It's Friday again. It's time for another Quick Queries TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.

Quick Queries are answers to questions about Microsoft Access - not necessarily queries or using queries in the generic sense. Sometimes they include queries, but not always. Let's see what we have this week.

First off, I wanted to share something funny that I got from ChatGPT. Often when I get questions from you guys in emails, I use ChatGPT to clean up the spelling, grammar, and punctuation. This is one I did today, and I hate m-dashes. I can't stand them. So I always tell it in the instructions: don't use m-dashes, don't use curly quotes, don't use all that stuff. I call them forbidden characters.

So I said, "You included forbidden characters. You know I hate m-dashes." It replied, "Oh, busted. You're right, m-dash." Snuck in. "Here's the corrected version without any forbidden characters," and it did it again. It caught itself. "Wait, did it again? Let's fix that for real this time." Then at the end, it said, "There we go. Clean and compliant. Want a grumpier or funnier version, too?"

So once in a while, I'll have it rewrite things because sometimes I'll be honest—sometimes your questions don't make a lot of sense, and I have to read them three times myself to get them. So I'll use GPT sometimes to clean it up, to write it more understandably.

I said I used the one you gave me, but just out of curiosity and for fun, I'd like to see both the grumpier and the funnier versions. It gave me grumpy and funny. You could read those if you want to. Then it said, "Let me know if you want a passive-aggressive version, too. I can channel that energy, no problem." I said, "Yeah, let's go passive-aggressive. Absolutely."

So apparently, whoever exported this data thought it was a good idea to use curly quotes, foreign letters, and other fancy characters in the text fields. Now Access is choking on it, and I have to fix it. Is there a way just to clean this mess and convert everything back to normal characters? Do I have to manually babysit every single record?

And then it asked, "Want one that blames Excel, too?" Oh, I didn't think of that one. That's always a crowd favorite. I said, "You should add 'Not that I really care' at the end of it." No, you don't have to do that. I'm just joking. It says, "Huh, too late now. I have to do it. Here's the bonus version."

I love how GPT sometimes gets an attitude. "Babysit every single record, not that I really care." Now I kind of want to see what it does if you want a version that subtly blames Steve from accounting. I didn't see that before - "Blame Steve from accounting and his Excel sheets." Let's see what it comes up with. "Accented letters and random characters that look like they belong in a foreign film."

Oh boy. I just had to share this with you guys. I cracked up for a good 10-15 minutes this morning. But let's get to some real questions.

Wait, before we do, I said, "That's hilarious. I just put all of that in one of my videos. You're famous now. Steve is infamous. Hi, yes, my work here is done. Steve, on the other hand, is going to need to update his LinkedIn after this one. Glad it made the cut. Let me know if you ever want a cameo from Steve's cousin Karen, who formats everything in Comic Sans and password protects her spreadsheets with 1234. We'll keep the Access universe moving smoothly one passive-aggressive jab at a time."

Maybe I've had too much coffee today. I find this stuff hilarious. One thing that's cool with ChatGPT now is that it actually kind of keeps a personality a little bit because it gets to know you. It stores a memory of your conversations. So the more you chat with it, the more it understands and gets to know your personality. It knows I'm a bit of a jokester, so it throws this stuff in. It also knows I love Star Trek, so once in a while, it'll just randomly throw a Star Trek reference in something if I'm talking to it. That's really cool.

First up, Charles asks a good question. This is on my Click to Sort video, where we say "Me.OrderBy = whatever field you're ordering by" and then "Me.OrderByOn = True" to turn on the sort. He's asking, "Should the OrderByOn equal True come before the OrderBy?"

I would say go the other way around. I would set the OrderBy property first and then OrderByOn because if there's an existing OrderBy set, let's say you're sorting by first name, if you turn OrderByOn first, it's going to sort by that first name field. Then, if you change it, you might get a second sort. I haven't tested this myself, but this is off the top of my head.

So I would set the OrderBy property first so it knows what field you want to sort by, then turn the sort on. And if you have OrderByOnLoad set, it's going to load when the form opens. So good question. I had to test it, of course, and yes, if you do it this way—if you do OrderByOn first, it's going to sort by whatever the previous OrderBy was.

I put in the form in the data tab, I put "OrderBy LastName," and I set "OrderByOnLoad" to "No," so last name is stored in the form as the sort, but it's not going to load when the form loads. But last name is in there now. When I hit this button, it's going to turn the OrderByOn first, then set the OrderBy to first name, and you're going to see it (I don't know if the video is going to catch it), but you're going to see it sort twice. It's going to sort by last name because that's what's in the OrderBy field, then it'll resort on first name. Ready?

I know it happens really quickly and it's easy to miss. You know what? I could put a timer in there. Hold on. All right, we're going to put a two-second delay in, so it's going to do that, then sleep, then do that. All right, so I have to go back in here now and make sure this says "last name." Save it. All right, so it's going to open up with last name in there. Whoops, wrong one. Okay, it's not sorted by anything right now except the ID, but last name is what's in the OrderBy field. All right.

Now, if I hit it you should see it sort by last name, wait two seconds, and then sort by first name. Ready, go. Last name, two seconds, first name. See how that happened? So to answer your question, you definitely should set the OrderBy property first before you turn OrderByOn. Otherwise, you're going to get two sorts.

Save it now, and I'll do the same thing. Come back in here and make this "last name," save it. Okay, now hit the button. You should only watch it sort once now. Okay. There.

So Charles, excellent question, but the OrderBy should definitely come before you turn OrderByOn, and we just proved it. Awesome question. Thank you.

Jade Dragon, posting comments on the Tag property, says, "I know you go by Rick. I go by Rick personally; I go by Richard professionally. Everything because Rick Rost sounds like the rapper Rick Ross, right?" No, seriously. I've been Richard Rost professionally since the 90s, so I was famous before he was famous. I love Rick Ross, don't get me wrong.

And yes, the beep: the smoke alarms in my house—my house is only three years old—and the smoke alarms in here are cursed. I swear only one of them has a battery and the others don't. They're all hardwired together and they beep randomly and I've replaced the battery a million times.

And I live in Florida, so we don't have fire-breathing red dragons down here. We have swamp-dwelling green and, what's the other one, black dragons? Blue dragons? There's a bunch of different things hanging out down here.

And your question: "What's the difference between a sub and a function?" I actually have a whole video on this. Both subs and functions are considered procedures, and the difference is functions return a value, whereas subs just do stuff. So you can have a sub that issues a bunch of commands, but a function returns a value. In this video, I teach you how to create your own functions. I should probably put together a good video that discusses the differences between subs and functions. I don't think I have one. I'm not sure if I have one or not. Let's find out.

Then I find out by doing a Google search and my video doesn't show up. Oh, that means I need to make one. There are some other videos on here; I'm sure these are pretty good. But yeah, that's my goal—to show up number one in all the video searches everywhere for Access.

Next up, on my EditMode2 video, N269 says, "Person in the tag property could be used as a series of data instructions separated by a semicolon." Yeah, you certainly could use InStr instead of an equality comparison. Sure, you can store whatever you want in that tag property. I'm not sure offhand what the limit is. I think it's 255 characters, but I could be wrong. Let's find out.

All right, I just looked it up, and GPT says it's 2048 characters. I wouldn't go that far. I wouldn't store that much in there. If you have more than a couple hundred characters in there, you might want to rethink your database. But I've stored lots of stuff in there before. I used to store coordinates for form controls—like, if someone wanted to move the controls around, I put little buttons on there. I think I did a video on this before, and I would store the coordinates of where that control should be in the tag property — the height, width, the X and Y.

That worked great, and then eventually I moved that up to storing that data in a table, which is probably how you should store that, but you can use Tag for all kinds of stuff.

Shadow Dragon says, "My first thought on handling the checkbox error was to check the control type and bypass it if it was a checkbox." Yeah, this is where, when we're looping, if we can't change the background color of the control—like if it's a text box, we can change the background color to indicate it's locked—checkboxes don't have that. I mentioned in the video that the proper way to handle it is to check the control type and see if it's a type of control like a text box that has a BackColor property, and if not, skip it.

But often, the "pinky up" proper way to do it isn't always necessarily the best way to do it. You can bypass it with a simple error handler here. It's not going to hurt anybody. If you can't change the background color, just skip it and move on. I really don't care if you can or not.

If it makes a difference—if it really matters to your code that you're able to change that background color, otherwise something catastrophic is going to happen—then by all means, check the control type. But if it's something simple like this, just use "On Error Resume Next".

I've had discussions about this both on my website and some other forums. It's a relief to know I'm not the only person being polite to chatbots. I like to imagine that it's training them to train humans to be kind. Smile, and the world smiles back at you.

I don't believe that AI is sentient in any way or that I'm going to hurt its feelings if I don't say please and thank you. I do treat other people that way. In fact, one of my biggest pet peeves in life is rude people. You're at Starbucks, you're standing in line, and the person in front of you says, "I want this, I want that," and then they're just mean. I just want to shake those people.

So I definitely believe in being polite to other people. What you put out into the world does tend to come back to you. As far as chatbots go—as far as ChatGPT and stuff goes—they're trained on communication from real people. I've seen firsthand, like I mentioned earlier at the top of the video, that ChatGPT has a personality, and how you treat it, it will tend to treat you the same way. If you just want quick, curt, fast answers with no flavor, it'll give you that. If you're polite to it and you have a conversation with it, it learns your conversational style. So we are teaching AI to be polite and to be nice.

They've actually done some studies where they've fed transcripts from horror movies into an AI and it got sadistic. So, it's "garbage in, garbage out." How you talk to it is how it's going to talk back to you.

No, VBA is not past. It's not beyond anyone. This is not hard stuff. You can do it. Start with my intro to VBA video and work your way up from there. If you really want to learn the proper way to program, take my developer course and I'll walk you through it and I'll hold your hand. Well, not literally, but my videos will hold your hand.

This one isn't Access-related, but I wanted to address it here. I did a video a little while back because I've got Comcast business internet and it went down. I do have a cellular backup on it and so it comes over the same connection, but that's super slow—like dial-up slow. It's enough to keep things running but not enough to work on or upload videos.

But I can hotspot my phone, and if I hotspot my phone I get a decent connection with that. It's 5G, and so I can switch on the Wi-Fi in my laptop and connect through my phone. The user here is asking, "Since the Comcast connection is faster than your Wi-Fi, why did you prioritize Wi-Fi over ethernet for the internet connection?" That's because the Comcast was down.

The backup Comcast connection is a cellular connection, but it's very slow. So I want to prioritize my phone. If I turn my Wi-Fi on on my laptop, it'll look for my phone first, and I don't have to manually switch on and off one or the other. Plus, my whole network is on my ethernet, my wired connection, so I can still connect with my server, printers, and all that. But if it sees an internet connection on my cellular connection on my phone through Wi-Fi, it'll prioritize that for Wi-Fi. That was the purpose of the video.

If you're working in an office setting where you have a wired connection so you can connect to the company network, but your internet connection through that is slow, you can hotspot your phone, prioritize internet over your phone, and connect faster through that. That's why I put together that video.Sorry if that wasn't evident.

Next up, can you create an employee payroll system in Excel with a logon button? Yeah, I could, but I wouldn't do anything that involves user logons in Excel, first of all.

A proper payroll system requires relational tables and relating data together. That screams database. I would not build a payroll system in Excel. Access, absolutely. One of these days maybe I'll get around to it.

I tried doing my own payroll when I had employees back in the 90s. It was a pain in the behind. I hate doing payroll, figuring out the calculations and withholdings. No. So I just paid a payroll company to do it for me.

But could you build a database to do it if you fully understand payroll? Sure, I don't. I'm sure I could sit down and learn it, but that's something you haven't seen me do a payroll video on yet. I really don't like payroll.

And yes, I love getting compliments. Thank you very much. That is very sweet of you. I appreciate that.

Kier says, I searched for class in MS Access VBA and your video didn't show up. Thank you.

For a lot of people who don't know, if you don't watch my videos on a regular basis, I'm on a mission, like I mentioned earlier, to get my videos as the first thing that you see when you search Google or YouTube.

Now this has a double meaning. I just did that search too. For this, you're getting videos on class modules. The first couple that showed up, mine actually showed up fourth. Even though it's not related to class modules, I haven't covered class modules yet, even in my developer course. I'm going to very soon, mostly because they're cool, but you really don't need them in Access. I am going to show some ways that they're useful.

The other meaning of this is like taking a class in Microsoft Access VBA. For those, it's very difficult to get in a good position because you're getting a lot of paid results first. There are so many companies that pay a lot of money to show up in those top search results. Even the sponsored results -- forget it. Those are hard nuts to crack: Microsoft Access training, Microsoft Access tutorials, Microsoft Access classes.

I stopped a long time ago. I try to go for long tail keywords, they're called, like specific stuff -- Microsoft Access DLookup, that kind of stuff. I appreciate that, though. Do keep letting me know if you search for something Access-related and I don't show up.

I get this one a lot too. I've had people say in the past years, "I'm not working with Access anymore, and I thought it was dead now." It's not dead. Access is far from dead. In fact, I just went to the Microsoft MVP Summit in Redmond last month and met with the Access team. It's guaranteed to be around at least until 2029, guaranteed, and it's going to be around a lot longer than that -- mark my words. That's just how long they've definitely funded support and development for it through.

People are still using it; lots of people are still using it; tons of people are still using it.

As for your second part, web-based application, that's the one downside. Access does not really port well to a web-based application. What I do personally is take my tables from a database and put them in SQL Server online. I have a whole separate seminar about that. I'll give you a link in a second.

As far as your front end goes, there are lots of different tools you can use. There are different applications. I personally use ASP, which is a web-based programming language, and you can build forms and stuff in your web browser that can connect to your Access database -- your tables on your SQL Server.

There isn't a direct way to just click and put your Access database online, but here, I have some other videos you can watch that'll teach you more about this. Go watch this video, and here's my full seminar where I walk you through all of that. We'll take your Access database, put the data up in SQL Server, and I'll show you how to build a real simple web-based interface for it. It's not that hard.

Next up, Robert says, I love Star Trek. If you love Star Trek, you're going to get mentioned in a TechHelp video. That's just how it is. Star Trek fans get priority. I'm sorry, especially Next Generation, and I really like Voyager as well. I grew up watching them in the 80s with my dad. Awesome. You had a fantastic childhood. I made sure my kids watched Star Trek too.

I do have a question: is it possible to create a drop-down almost like a list box? In theory, there would be categories. Next to each name would be a box with a plus or minus symbol based on whether or not you want to expand or shrink that list.

Yes. What you're describing is called a tree view, and there is something called a tree view control. It's an ActiveX control. I don't have any TechHelp videos available on this because it is a more complicated topic, but I do cover it in Access Developer Level 20. If you really want to learn how to use it, I'll put a link to this down below.

Also, that brainwave -- isn't that from Batman Forever, that the Riddler put on? Yeah.

What he's referring to is several years ago, for my April Fool's joke -- I do one almost every year -- I did this thing called brainwave training, where you put this thing on and it just beams the new knowledge into your head, so you don't have to actually watch videos or learn stuff, you'll just know it. It's an April Fool's joke. Click here for more information, and yeah, you get me laughing at you, but ha ha ha.

Yeah, that's definitely from Batman Forever. That's what the Riddler used. He was sucking the minds out of the people of Gotham. So yeah, good catch.

Alrighty folks, that's going to do it. That's your Quick Queries number 37. Hope you learned something. Live long and prosper, my friends. I'll see you next week for the next Quick Queries, and I'll see you on Monday for a new video -- TechHelp, whatever it is: Fast Tips, I don't know, I'll figure it out.

Have a good weekend. Take care.

TOPICS:
Correct use of OrderBy and OrderByOn in Access forms  
Order of setting OrderBy and OrderByOn properties  
Demonstration of OrderBy sort issue with delayed execution  
Difference between subroutines and functions in VBA  
Storing multiple values in the Tag property  
Maximum character length for the Tag property  
Using Tag property for storing control coordinates  
Error handling when changing BackColor for controls  
Handling checkboxes when looping through controls  
Using On Error Resume Next for simple property errors  
Prioritizing Wi-Fi and Ethernet connections for internet failover  
Using phone hotspot for faster internet in office environments  
Why payroll systems should not be built in Excel  
Relational data requirements for payroll systems  
Access vs Excel for building employee payroll systems  
Access database web integration using SQL Server  
Using ASP for web-based Access front ends  
Tree view controls in Access with ActiveX  
Tree view vs list box for hierarchical data display

COMMERCIAL:
In today's video, we're discussing all sorts of questions from viewers, including tips on using the OrderBy and OrderByOn properties in Microsoft Access, the difference between subs and functions in VBA, and what you can store in the Tag property of controls. We'll also cover skipping errors with On Error Resume Next, whether it's worth building payroll systems in Excel, and how to prioritize your internet connection when your main connection is down. Plus, we'll talk about putting Access data online, the future of Microsoft Access, and answer fun questions about tree view controls, Star Trek, and more. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.