Microsoft Access v Microsoft Excel: Which is Better? When to Use Each.

 

Access versus Excel. It's a question I get asked all the time: which is better, what do I use, and when.

Today's question comes from Mark from Akron, Ohio, one of my Gold members, but I get asked something similar to this all the time. Mark says, "I just started a small business and I can't decide whether I want to use Excel or Access to manage my customers, contacts, orders, inventory, and so on. I don't want to use off-the-shelf accounting software like QuickBooks because I do have some unique record-keeping needs for my business. I've been using Excel for years, but I hear Access is a lot more powerful. What do you recommend?"

Well, first Mark, let me start off by saying that I love Access and Excel. Over the past couple of years, I've spent most of my time teaching Access lessons simply because that's more of my niche market. There's a lot less competition for good Access training materials. 

With Excel, there's a lot of good people that are putting out books and tutorials like MrExcel. He's got a lot of great stuff. So there's a lot more competition there. Access, not so much. So that's why I've been focusing my business. So I love them both, and I'm going to give you an unbiased accounting of which one is better to use and when.

Access and Excel really complement each other well. Let's talk about the primary role of each application. Access is great for data storage and management. When you've got a large volume of data that you need to store and you need to be able to manage it, make additions, do update queries, deletes, and stuff like that on a large scale, and you want to automate stuff, that's Access's primary function.

Access is also very good at creating a robust user interface. You want to create menus and buttons for non-computer users to be able to go in and easily do what they have to do. That's what you want Access for. Also, if you're dealing with multiple simultaneous users, if you've got 5, 10, or 20 people in your office that need to work from the same customer table, again, that's a job for Access.

Excel is much better with smaller amounts of data, and it's really great at data analysis and visualization. In other words, if you want to take this year's sales and do analysis on them, what-if scenarios, pivot tables, you want to see trends and things like that in your data, that's what Excel is good at. 

Excel is much better with things like advanced charting. It's got better conditional formatting. We'll talk about these things as we go along today in the video.

Now, each application also has its primary disadvantages. Access, for example, has a steeper learning curve. Like my buddy Alex says, it has a higher barrier to entry. It does take a little more training to get up to speed for building Access databases. It's not quite as easy to use as Excel. Excel is a lot more intuitive. You can just sit down and start typing information in. With Access, you have to set up a structure first.

Also with Access, there's a longer development time. If you're going to build a database to run your business, it's going to take longer than just sitting down and plugging information into a spreadsheet. But that disadvantage is actually a benefit in the long run because you'll be a lot more productive with a properly built Access database.

Excel's problems? Essentially, a lack of structure. Anyone can just type in anything, anywhere they want on the sheet. There's no rules. Yes, Excel does have some basic data validation, I know that, but by and large, you can pretty much get away with whatever you want. That's okay for you, but if you've got 15 people working off an Excel sheet, it becomes a problem.

Another primary problem for Excel is that it's not relational. Each sheet stands on its own. So if you've got customers and orders and products, they're not related together at all. You have separate information in separate sheets, and it can be hard to try and track information down. That's what Access does really well.

So let's talk about how both are alike. Both can store large amounts of data. The maximum size of a single file is 2 gigabytes, and that's a lot of information. That's, I believe, like a million rows in Excel, and I forget how many columns. Access, a single table can only be 2 gigabytes which again is pretty big, but in Access, you can chain them together. You can have multiple tables stored in multiple files, so your size is pretty much irrelevant.

Both can search, sort, and filter data, although searching, sorting, and filtering very, very large spreadsheets can get cumbersome sometimes. Both do have data analysis tools, although Excel's are much more robust. Both can create basic charts. There is some simple charting in Access. It's getting better. Excel is by far a better option for charting, though.

Both have sophisticated functions for Max, Min, and lookups and all that kind of stuff. Both can perform advanced calculations. Both can be secured with a password. If all you care about for security is putting a simple password on your file, you can do that in both Access and Excel. Both support macros and the full Visual Basic for Applications programming language. And both can access data from outside sources, although I will argue that Access does it better.

So that's what they both have in common. Now let's take a look at where each is better than the other. Now these are in no particular order, and the one in bold is the one that I suggest is a benefit.

Access does have a longer learning curve, whereas Excel is relatively easy to learn. It's a lot more intuitive to sit down and just start typing data into Excel, although that can sometimes be a downside because there's less structure. Access does have a longer setup development time, whereas with Excel, you can just start working right away. You can just start sitting down and typing in your customers, your products, and so on.

With Access, it's easier to maintain data integrity with validation rules, formatting, things like input masks, and so on, indexing with no duplicates. Excel does have some basic validation rules, but these are harder to enforce.

Collaboration. Access is better for multiple simultaneous users. With Excel, it's not quite so easy to work in teams. Yes, you can share a file on the network, but you really shouldn't edit it at the same time. Excel Online does have some multiple simultaneous user capabilities. So does Google Sheets, in fact. You can see each other editing a spreadsheet at the same time. But again, Access does this much, much better.

Excel is much better at analyzing small amounts of data, whereas Access does lack rich data analysis tools. With Access, you can build a high-quality, long-term application. I've been running my business off the same database, granted with some updates, since 2002, and it's now 2020, so that's 18 years. I'm using basically the same core database.

With Excel, your sheets can get unmanageable as your data needs grow. I've got 40-some thousand customers in my database. An Excel sheet with 40,000 rows is tough to work with. With Access, you can create a custom interface for your users with menus for different groups. You can have an admin menu, you can have a menu for your accounting people, your secretary can have his or her own screen, and so on. So you can custom tailor your Access application for whoever is using it. 

You can lock them out of certain areas and give them rights to certain other things, and so on. With Excel, it is possible to create some kind of a menu interface, but it's nowhere near as easy. And it's harder to manage workflow if you're using just a bunch of different Excel sheets.

Data entry and lookup can be made simple. Your developer can set things up, and then your users can just use them. Whereas with Excel, everyone who's using it pretty much has to know how to use Excel.

With Access, simple charting is available, but it's nowhere near as good as with Excel. Excel has much more robust charting options available. In fact, I will often take data out of Access, export what I need, and then import it into Excel, and I'll run my charting in Excel.

With Access, you can do some simple like sales charts. It's got bar charts, line charts, and some simple stuff, but Excel has things like trend lines and stuff that Access just doesn't have. Plus, the formatting options are a whole lot better in Excel charts. I tried to do a pie chart in Access the other day using the new metro charts, and you can't even change the colors of the pie slices yet. They're working on it. They're adding more stuff to it, but right now, Excel is way better with charting.

With Access, your data must be structured. Each field has to have rules. This is a date. This is a yes/no value. This is text, and so on. And I think that's a benefit if you're storing information in a database. With Excel, you can literally put data anywhere you want on the sheet. Now, that might be a benefit to you as well depending on what kind of information you're storing. So this one can go either way.

If you just want to use Excel for a collection of loose notes, and I've done that before. Like I used to do my personal checkbook in an Excel sheet because I could make notes and put stuff wherever I wanted, and that's fine. But eventually, it got big enough where I moved it over to Access.

Alright, I've touched on this one before. Access is better at managing large amounts of data, searching, sorting, and filtering, whereas large sheets can be very cumbersome to work with in Excel. If you've got an Excel sheet that's more than a couple hundred rows, I think that's starting to get unmanageable.

We've talked about the longer setup and development time, whereas with Excel, you can start working immediately. Data in Access is relational. Customers are related to orders, orders are related to products, and so on. In Excel, you don't have that; everything is stored in a flat file spreadsheet. So if you want a list of what products your customers in New York have purchased in the last year, it's so much easier to generate that in Access than it is with Excel data.

In Access, records can have unique auto number IDs. Access automatically maintains numbering your customers and numbering your products, whereas Excel doesn't do that. Access has indexing, so it speeds up searches and sorts. Excel doesn't have indexing. So if you want to take a list of 10,000 customers and sort them by any particular field, last name, first name, whatever, that's a lot faster in Access.

Access has referential integrity, and that means that you can have situations in Excel where you put in an order that doesn't have a customer. You can't do that in Access. You can set up referential integrity so that if an order doesn't have a customer associated with it, you can't enter it in the system.

This is what I'm guilty of a lot. In Access, calculations are bound to fields in queries and are easier to enforce. So when you put calculations in a query in Access, that record is the one that is always calculated on. In Excel, you can have a situation where you put in a formula, and you got the wrong reference in there. So it's referring to a different row. And I do that all the time. I copy and paste stuff, and I forgot the auto-fill, and I'm having my calculations pointing to different records, different rows. That can't happen in Access.

In Access, you can set up parameter queries where the user can type in. Let's say you're looking for a list of customers from a particular state, they can type in the state when you run the query like New York or Florida, and boom, there's your list. You can't easily do that in Excel.

A big benefit of Access is action queries. There's update queries, delete queries, append queries that can change data in huge batches when you import new orders or something like that. With Excel, you can do something similar. You can do mass updates and stuff, but it requires either a macro or some scripting or some VB programming.

I've kind of already talked about this. In Access, complex queries can be saved for novice users to run. So you can set up a query, again, that shows all the products between two dates from customers from a particular state for a particular sales rep. You can put all that in one query and save it, and then Joe the secretary needs to run that report to give it to someone, he just clicks one button and boom, there's the report.

Excel doesn't have something like that available. You have to know how to use Excel and its filtering options to get the data that you want. And it's even harder if it's in multiple spreadsheets.

Now I know a lot of these pros have been on the Access side so far. I'm kind of picking out different features that Access has, but Excel does really have some areas in which it shines. For example, what-if analysis doesn't exist in Access. Data modeling, so much better in Excel. Pivot tables and pivot charts, they used to have pivot tables in Access 2010, but they removed them in 2013. 

Because again, pivot tables, it's something better to just take the data that you want to pivot, take it out of Access, just export it and then import it into Excel quickly, and you can run your pivot table then.

You can set up user-level security in Access. Now they don't have user-level security built into Access like they used to have back in like Access 2000, 2003. They removed that. But like I show in my security seminar, it is still possible to set up your own user-level security and lock down your database. It's not built into Access, but there's a way to do it if you really want to take care to make sure that your database is secured.

With Excel, there's simple protection, there's sheet-level protection, and you can put a password on the entire workbook, but security isn't as easy to implement or as robust to implement in Excel as it is in Access. 

Now, once you have that security set up, you can audit your user activity. You can see who's doing what, who's deleting records, adding records, and so on, whereas with Excel, that's just not possible.

Access is much better for printing out reports. Any kind of report you can make, you can print it. Mailing labels, different kinds of correspondence, all kinds of stuff. You can print out anything. I can do pretty much anything in Access that you can do in Word, for the most part. With Excel, you can format your sheets to look good when you print them, but it's nowhere near as powerful as Access. Excel is much better with conditional formatting. You've got icons, data bars, color scales.That's where you go from one color to another one, you know, like hot red to light green. Okay. Access has real simple conditional formatting with basic colors. It's useful, but it's not as good as Excel. So if you're looking for data visualization, Excel is where you want to be.

I've actually got this next one backwards. I didn't bold it correctly. Let me fix it real quick. There we go. It's a benefit for Access that only the developer in your office needs a paid copy of Access. If you've got 20 people in your office, only one person, the person who's making the database, who's making design changes, needs a paid copy of Office. Everybody else can run the database using the free runtime edition that you can download from Microsoft's website.

Whereas with Excel, everybody has to have a paid copy of Microsoft Office to use Excel, the desktop Excel. However, to flip that over, you can give everyone a free copy of Excel online if they log on to office.com and set up a Microsoft account. They can use Excel online, which has 80% of the features that people are going to need from Excel right in the free version. Whereas Access has no such free version.

But, you know, Access with a Microsoft 365 subscription is like $12 to $13 a month. It's not expensive, and you only have to pay that for one person in your office or whoever is going to be developing. 

Okay, so in summary, to run down these real quick, when to use each. Use Access when you want a more robust user interface, you don't mind taking some more time to learn how to build it, and you've got a little more time for application development. You don't mind spending time doing that. 

You've got to store large volumes of data, thousands and thousands and thousands of records. Definitely use Access. You need relationships between your tables. All right, definitely use Access for that. You have employees who aren't computer people who don't really even know the basics of Excel, and you don't want to bother training them. You can build them a bulletproof application in Access they can use to do their work.

If you need multiple simultaneous users, then Access is pretty much your only choice. If you want to be able to import and export or modify large amounts of data automatically with action queries, again, Access. And if you want a variety of different printable reports and labels, Access is your man. 

Now use Excel if you don't want to take the time to learn Access. All right, it's got a real small learning curve. You can just sit down and start entering your data. If you've got smaller sets of data to store, feel free to use Excel. I don't take everything that I have and put it into a database right away. If I've got something that I know is going to only be 20, 30 rows of data, sure, I'll throw it in a spreadsheet. 

There's no sense taking the time to set up a table and do all that stuff and put it in Access if it's just a small set of data. I keep my list of domain names that I own, for example, in a spreadsheet. I've got like 20 different domains, and I want to make sure that I've got the domain name and the password and when it renews and all that stuff. And that's just in a spreadsheet. I don't need to build a database for that. 

If you need the adaptability to put information anywhere on the sheet, use Excel. If you want to be able to put notes in the corner and this and that over there, Excel is just fine for that. Performing any kind of statistical analysis, what-if scenarios data modeling, use Excel. 

I will often export information from Access into Excel to do that or for data visualization. Better charting, pivot tables, better conditional formatting. 

Okay, so after going through all of that, what's the final verdict on which is better. Which one should I use? Drum roll please. Cue the drum roll. Oh, I don't have a drum roll. Okay. Forget that. All right. So which should I use? Use both. 

Of course, they complement each other. They work well together. Excel is quicker and easier to set up initially, but a well-built Access database will definitely save you time and money in the long run. It'll increase your productivity to have a good Access database. Managing large, complex spreadsheets is cumbersome, especially if you need relational data.

Right? If you've only got a couple hundred customers, feel free to keep them in your Excel spreadsheet. As soon as you get up to a couple thousand, you might want to really consider moving them over to Access, especially if you're dealing with situations where each customer might have multiple addresses, multiple phone numbers. Now we're talking relational databases. 

I will often start out entering my data in Excel. This gives me a way to get up and running quickly. Then when my data grows to over a few hundred rows, I didn't mean to rhyme that, it's time to start moving over to Access. After you've built your Access database, continue to export your data to Excel for rich charting, pivot tables, what-if analysis, and so on. Each application has its place and they complement each other nicely. 

So that is my verdict on which is better and when to use each, Excel versus Access. They each have their own benefits, they have their own drawbacks, and they make a great team for your office data management.

Want to learn more about Access and Excel and how to get them to work together? Well, in the extended cut, I'm going to show you a few tricks. 

First, I'm going to show you how to import or link to Excel spreadsheets from Access. That's handy if you've got someone using an Excel sheet somewhere on your network, maybe doing data entry for you. I'll show you how to link to that sheet so you can see what information is in there, and you can even pull it into your database. Then with an append query, you can run a little append query and have it automatically brought into your customer table with one click, well, double click. 

Then I'm going to show you how to go the other way. So if you've got someone on your network that is just using Excel and they want to have access to, let's say, your customer list or your itinerary or whatever that you've got stored in Access, you can have it so that they can see that information inside of Excel. And there's a trick to have it so it automatically refreshes when you open up that spreadsheet file, and I'll show you how to do that in the Extended Cut for Members.

For a complete video tutorial on this topic, please visit https://599cd.com/AccessvExcel?key=Blogger

How to Convert Text to Title Case for Movies, Books, and Songs in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone covers the topic of converting text to title case, where the appropriate words in titles like movies, books, and songs are capitalized. Landon from Mount Prospect, Illinois, asked how to change all the text in his database so that the first letter of each word is capitalized, as seen in titles. The text in his Microsoft Access database consists of inconsistent cases, and he seeks an efficient method to standardize them without manual edits.

To address Landon's query, I have previously discussed changing the case of letters and words. In my Change Case video, we explored converting text to uppercase, lowercase, and proper case using UCase, LCase, and a custom proper case function. Proper case converts the first letter of each word to a capital letter while making the remaining letters lowercase. However, for titles, not every word needs to be capitalized—articles and short conjunctions like "the," "of," "a," and others typically remain in lowercase unless they appear at the beginning of the title.

We're going to modify the proper case function to accommodate these special rules. This tutorial assumes a developer-level understanding, including familiarity with Dlookup, NZ function, for-next loops, and arrays. You should also review videos on the split function since we'll be using it to manage arrays.

First, create a "movie" table in Access to store the list of titles, ensuring none of them use field names like "name" or "date," which are reserved words. Populate this table with movie titles, either in lowercase or varying cases. Tools like ChatGPT can generate random titles for practice.

Once you have your list, create a query to convert these titles to proper case using the StringConvert function with a parameter of 3 for VB proper case. Note that while VB proper case works in VBA, it needs numerical representation in queries.

To enhance this function, we'll create our own VBA function called title case. This function will convert the entire string to proper case first and then determine which words should revert to lowercase based on a predefined list. Instead of hardcoding this list into VBA, we'll store it in a separate "small word" table in Access. This approach allows users to easily update the list as needed.

Next, split the input string into an array of words using the split function, so each word can be processed individually. Loop through the array, checking each word against our list of small words. If a match is found and the word is not the first in the title, it is converted back to lowercase.

By the end of this part of the tutorial, you'll have the groundwork for your title case function, which can handle converting text to title case, considering specific capitalization rules. Tomorrow, we'll continue refining this function, focusing on rebuilding and returning the string. 

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.

For a complete video tutorial on this topic, please visit https://599cd.com/TitleCase?key=Blogger

How to Perform Natural Sorting in Microsoft Access for Mixed Alphanumeric Lists

 

Today's TechHelp tutorial from Access Learning Zone is about natural sorting. I'm Richard Rost, and I'll guide you through this concept. Natural sorting arranges data in a way that feels intuitive, especially when dealing with text fields containing both numbers and letters.

Clara from Bowie, Maryland, a platinum member, posed today's question. She receives unit number lists combining numbers and letters, such as 1a, 1d, 2f, 571, and 01d. When sorted in her database, these entries are arranged alphanumerically, causing mismatches like 100 appearing before 2. Clara wants to sort them numerically first and then by letters.

Computers sort data alphanumerically by default, focusing on each column in sequence. This means numbers appear before letters. Therefore, an entry like 105 might be mistakenly grouped with entries starting with 1. To achieve a numeric sort while ignoring the letters, we can use the Val function, which converts a text string into its numeric value.

For our demonstration, we'll create a table in a free template from my website. Starting with an ID field, we'll include a field labeled 'my field' as short text, accommodating both numbers and letters. We'll populate this table with sample data: 1, 2, 3, 1a, 2c, 100a, 205f, and 1001g.

When we sort this data, it's ordered alphanumerically. To implement natural sorting, we'll create a query with a calculated field. This field will utilize the Val function to convert text strings into numeric values by extracting the leading numeric characters.

In this calculated field, we'll define a new field 'x' that applies the Val function to 'my_field.' Running the query, you'll see the numeric parts extracted correctly. The Val function starts from the left of the string and stops at the first non-numeric character or period, ensuring it extracts the correct numeric values.

However, Val has some rules. It parses until encountering a non-numeric character or period. Symbols like dollar signs and commas are considered non-numeric. So a string like $14 will return zero since no numeric characters are found before the symbol.

With our new 'x' column in place, sorting becomes straightforward. We'll sort by this calculated column 'x' in ascending order. Additionally, by including 'my_field' in the sort, we ensure that entries with identical numeric values are correctly arranged by their letters.

Finally, save the query. This approach achieves the desired natural sorting, displaying entries in a numerically logical order with letters correctly sequenced thereafter.

This method ensures your data is sorted in a more intuitive, user-friendly manner. For a complete video tutorial with detailed, step-by-step instructions, visit my website at the link below.

Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/NaturalSorting?key=Blogger

How to Restart Your Microsoft Access Database at Regular Intervals (Hourly, Daily, etc.)

 

Today's tutorial from Access Learning Zone involves how to restart Microsoft Access efficiently. If you have an Access database that runs continuously on a timer—perhaps like a server on your network—occasionally, it needs to be restarted to avoid issues. Today's question comes from Brianna in Rockville Center, New York, who has a setup like this and encounters various problems after a few days unless she manually restarts the database.

I have experienced a similar issue with my own Access database, which operates around the clock, sending emails and updating information. After a few days of continuous operation, it started to lock up or crash, leading me to reboot it manually. I discovered that restarting the database once an hour cleared up these issues.

This happens because no software is perfect. Access has its share of quirks, such as memory leaks. If you declare variables and do not clear them properly, over time, these small memory issues can accumulate, requiring a restart to clear them out. Think of it like cleaning a chalkboard: after writing and erasing multiple times, it eventually needs a thorough cleaning.

Before we dive deeper, note that this tutorial is intended for those with some VBA programming experience. If you're new to VBA, I recommend checking out some fundamental videos on my website and YouTube channel first.

Let's proceed with the task. I created a folder named "My DB" on my desktop, containing my Access database file. Since Access cannot launch another instance of itself directly, we'll utilize a Windows batch file to accomplish this.

First, create a text document named restartDB.txt. We need to locate the full path to msaccess.exe, which is the executable file for Microsoft Access. You can find this by searching for Access in your Start menu, right-clicking the app, and selecting 'Open file location.' Right-click the shortcut and go to 'Properties' to copy the path.

In the text document, use the "start" command followed by the path to msaccess.exe. Then, add the full path and file name of your database. Save and rename the text file to restartDB.bat. Double-clicking this batch file will launch your Access database.

Now we need the database to run the batch file periodically. In the Access database, we'll set up a form with a countdown timer to simulate its operations. Create or use an existing form and add a countdown field.

In the form's properties, set the Form Load event to initialize the countdown and start the timer interval. The Timer event will decrement the countdown and, upon reaching zero, it will run the batch file to restart the database. This involves shelling out to the batch file and then using the DoCmd.Quit command to close Access.

A few enhancements include adding a delay in the batch file for smoother operations and adjusting the code to restart the database once every hour rather than at shorter intervals. We'll also include a feature to pause the countdown timer if needed. 

Part two of this lesson will cover these additional details.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.

For a complete video tutorial on this topic, please visit https://599cd.com/RestartAccess?key=Blogger

A Function to Determine if There are Records in a Table Before Doing Stuff in Microsoft Access

 

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today we're going to talk about determining if there are records in a table or query before you do stuff with them. What kind of stuff? I'll open a form, open a report. You can basically check to make sure there's going to be data before you get an error with the open form or the open report. This is a developer-level video, and I've actually labeled it extra nerdy because we're going to talk about some extra nerdy theory today. So, uh, yeah, let's get to it. 

Today's video comes from a conversation we were having in the forums on my website, and Kevin, one of my moderators (love Kevin), posted this. He says, "I wrote a function to display a generic message if there are no records in a table," which also would work for a query. And he basically wrote it with a DCount. It just checks to see if there are records in whatever you're trying to open, whether it's a, you know, a form or report or whatever, and it'll just give you a message, "There's no records to display." This comes up more with reports than anything else, but it could also happen with forms.

Let's say you've got a contact report. It's based on your contact table, right, all your contact information. And if you've got a button, let's say that opens up this contact report, and there's no data in it, maybe you're doing like, you know, accounts receivable or whatever, and there's just no records. Well, if you make a button, let's make this button open that report. So I'll change it to DoCmd.OpenReport ContactR, acPreview. Okay, and it opens fine now, but if there's no records in it, let's go to contacts and just delete all the records. If I open it now, I get a blank record, right, or blank report, which isn't very friendly.

Now you can use On No Data. This is one of the things I pointed out to Kevin with reports. At least you can come in here, there's an event called On No Data, right, No Data right here. If no data, you could say, you know, MessageBox "No records," and say Cancel = True, but watch what happens if you do this. It says, "No records," and then you get "The OpenReport action was canceled." This happens in several different places. This is just one example. This guy here throws an error. Now sure, you could put On Error Resume Next above it, and that fixes the problem. But a better solution, and the one that Kevin is suggesting, is "Hey, let's just count the number of records that are in there before we do this," right, and let the user know that "Hey, there's nothing in this record set."

So in my Global module, let me open this up. Kevin wrote a function. We'll call it Public Function GetCountData, and then you send in a data source name, right. That could be a table or a query as a string, and this will return a Long, right, the count of the records in that data source. I'm paraphrasing Kevin. He wrote a more complicated one where you can, you know, have it checked based on different tables and stuff. This is the quick version. And then inside here, we'll say Dim L as Long L = DCount("*", dataSourceName). We're going to count all the records from the data source name. Now if you want to customize your message in here, you can. You can say, If L = 0 Then MessageBox "There are no records in" & dataSourceName. And you can do whatever other stuff you want in here. End If. Right, and then we'll just return the value as the function value. Right, GetCountData = L.

And now in our forms and such, before we try to even open that, we can just say, If GetCountData("contactT") = 0 Then Exit Sub, before it even tries to open the report. You come out here, hit the button. "There's no records in contactT." Okay, and if there is something in there, then it works, and there's your report.

Okay, now as we always do in the forums, someone else always has to chime in, and that someone else is me. This one here, you don't want to look at that one, right? I said, "If your goal is to see whether or not there are any records, you should use the DLookup instead of DCount. DCount is great if you want the count of all of the records, but DCount and DSum, they have to read through all of the records to determine how many there are. So if you got 50,000 records, it's got to say, "Okay, I got 50,000 records here. It's got to count through them to figure that out." Whereas with DLookup, it just pulls the first record, which is much, much faster. It's faster if you got huge tables; if you got little teeny tiny tables, you can't tell the difference, right?

So then I put together this one. It says AreThereRecords, right? You got to send it a field name with this version of it because this one up here, I tried to use a star, but you can't use the star with DLookup. So ignore this one, folks. All right, you send it the field name and the data source, and then it does a DLookup of the field name in the data source, and then if it returns a zero, right, because you're using NZ here, which will make it zero if it's null if it can't return a record, and that just returns true or false.

And then a few minutes later, I said to myself, "Well, what if the function could look up the first field name in the table?" All right, assuming of course, yeah, it should be - it's best if it's an ID, but it doesn't have to be; it could be any field. And so then I put together this guy. And let me copy this, and we'll put it over in the VB Editor. We'll go through it. Let's go back to Global Module and down here, paste that one in. All right, I'm going to call it AreThereRecords. You send it a data source name again. Okay, this one returns a true or false, Are There Records.

Now this one's going to open up a record set, which basically is a pointer to a table or query. Okay, and it's going to say FieldName as String = rs.Fields(0).Name. What does that mean? Well, every record set has a collection of fields, right, CustomerID, FirstName, LastName, Address. Those are all fields. So Field(0), the zero item of the Fields collection, is the first record. Give me its name, okay, which would be, you know, CustomerID or whatever the first field happens to be; it doesn't really matter. So then AreThereRecords is going to be Not IsNull. We're going to try to DLookup that field name from the data source. So DLookup CustomerID from the customer table. Okay, is that null? That'll be true or false, and then we're just not that. So if it returns a record, AreThereRecords will end up true, right. If it's null, this comes back false. Because IsNull will be true, and then this will be false, and then AreThereRecords will be false. Okay, close the record set, clean it up, and now you can use AreThereRecords. I didn't put a message box in mine. If you want to put a message box in yours, that's fine too.

Right, so what I did was, where's the thing, let's go over here. So I said, "If AreThereRecords("contactT") Then," we're going to open it up. Otherwise, MessageBox "No records." And if I choose, I like to put my stuff out here because usually I find that this is going to be different for every case, but it's up to you, right. So now, if I hit the button - oops hang on. Oh, contactR, duh, that's my fault. I'm looking in contactR instead of contactT, the data source. Okay, that was my bad. Hit it, and there it is. Now, if I delete the records, let's try it again. There we go, no records found.

So there's two great functions for you. Go back to where they were, right-click, Definition. There's Kevin's, there's mine. I'll put these in the Code Vault for you Gold Members. If you like this kind of super nerdy stuff, come check out the forums on my website. They're absolutely free. Also, I've got lots and lots of developer lessons where I talk about all kinds of nerdy stuff like this too. You'll find links down below for most of the things I talk about today: If-Then statements, DCount, record sets, the On No Data event, that kind of stuff. So check all that out.

And that's going to be your TechHelp video for today. Hope you learned something. Thanks to Kevin for this idea. Live long and prosper, my friends. I'll see you next time.

A special thank you and shout out to our Diamond Sponsors. First, we have Juan Soto with Access Experts, software solutions manufacturing experts specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at AccessExperts.com. 

Another shout-out to Semmy Shamam from Shama Consultancy. Semmy is a certified Microsoft Office Specialist, and he not only offers Access application development but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Semmy is your guy. Check them out at ShamaConsultancy.com.

For a complete video tutorial on this topic, please visit https://599cd.com/AreThereRecords?key=Blogger

Filtering Long Customer Lists by Date of Birth in Forms and Combo Boxes in Microsoft Access

 

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we're going to talk about filtering by date of birth.

What does that mean? Well, it's filtering by the customer, patient, or user's date of birth, so that when you go to look them up in a list, it's not a big, gigantic list with thousands of names in it. It basically makes it easier to find people.

Today's question comes from Owen in Sugarland, Texas, one of my Platinum members. Owen says, when I go to my doctor's office, they ask for my date of birth first. I assume that's to shorten the list of patients in their database, so when they ask for my name, it's on a much shorter list. I have the same situation with my database, patient records. Since I always have their date of birth, how can I limit the results so my combo boxes aren't filled with thousands of patients?

Well, Owen, this is a great technique. A lot of businesses use it to look up customers. If you don't have a key that you can index with no duplicates, like a phone number, social security number, client number, or order number, if you just got some bit of data like their date of birth, you can at least limit the list of results based on that. 

If you've got 10,000 customers, odds are one in 400 or so will be on any one particular date. If you type in their date of birth first, which is perfectly acceptable for a business like a doctor's office, then you might only have three patients left. You can say, "Oh, what's your first name?" "Oh, I'm Richard." "Oh, hi. How are you doing? Here you are. I got you."

So, that's what we're going to do today. There are a lot of different ways to do this, and I'm going to show you a couple of different techniques. First, I'm going to show you a non-programming way, a non-VBA way, so this will be an expert-level class.

Owen is an expert-level student. What does expert mean? You're a little bit beyond the basics, so you're not a beginner, but you're not quite a developer yet, so you don't have to use any VBA for this technique. But you should know solid access basics.

Let me tell you some prerequisites first. We're going to use my TechHelp free template, which I show you how to build in this blank template video. You should know how to get a value from another form. So, if you've got a customer form open and you want to know how to get a value off the order form, that's what's covered in this video. Likewise, knowing how to have your query criteria get a value from an open form. If you don't know what query criteria are, go watch this. 

I will also be setting up an alias in one of my queries. It's just another name for a field. If you haven't watched all these videos and you're not familiar with these concepts, go watch these and then come on back.

Here I am in the TechHelp free template. We're going to take this text box here, and we're going to make that equal to our date of birth filter. Then we're going to click the hello, hello, that button. We're going to click the hello world button, and then open up our customer list form, this thing, showing just the customers with that date of birth.

I already got a field in this table called customer since. We're going to cheat and pretend that's the date of birth field. Okay?

Let's go into design view. Let's change this guy to the DOB filter. I am going to change its name, come on, all the way up top here. Let's change your name to DOB filter. Let's get rid of the control source. The format of short date is fine, and I'm going to put a default value in here so that I don't have to keep typing in a date in here. So, I'm going to put in my birth date. I'm going to put in 1972-10-23, just like that. I use the ISO date format, which is year-month-day, that way it's not ambiguous. I am on a mission to change the whole world over to the ISO date format. Go watch this video for more information.

We could change this button so it says open customers by DOB or whatever you want to have on the button caption. In fact, we can delete this button now that I'm thinking about it. We have a button right here that opens up the customer list. Move that over there. Open customer list by DOB. Okay.

Now save this, close it, and open it back up again. Right now, if I open up my customer list, it shows everybody. This form is getting its data from the customer table. What we can do is make a query where the query says, "Hey, get your filter from this guy right here, and then show me a list of customers with that date of birth."

Let's make a query next. Create query design. Let's bring in the customer table. Where are you? Customer table right there. Bring in all the fields.

I'm going to cheat. I'm going to make a field called DOB. We already have a field in here called customer since. That's a date field. So, I'm just going to use that. Bring that over here, and we're going to alias it here. I'll zoom in so you can see it. Shift-F2. Oh, my zoom box got really big. There's my zoom box. We're going to go DOB: customer since. That's an alias right there. We're just saying, "Take the customer since field, and from this day forth, call it DOB." That way, we're just cheating and giving ourselves another field. See? It's over there. Now we can refer to it as DOB. It's the same thing as that one. Okay?

Now I can also tell the DOB field to get its criteria from that form field. The criteria, here I'll zoom in again. Well, I'm not going to zoom in because if you zoom in, you lose the IntelliSense. I'm going to go equals Forms! MainMenuF! DOBFilter, which is right there off the screen. See that? You can still type it in, you can see it better, but you don't get the little IntelliSense popup. That would be nice to add to the list, right, Sammy? Put that on our list for the Access team. IntelliSense in the zoom window. Why not? Because that can't be too hard to add. 

Let's save this as the customer list query, and I'm going to close it and open it. Where's customer list? There it is. Open her up, and there is nothing in there. Why is that? Well, probably because no one has my birthday. Probably not even me. Let's go to the customer form. Customer since is the birth date, so I'll put in 1972-10-23. Let's give one other person, one other lucky person, like Malcolm Reynolds, my birthday too, so we have two people, 1972-10-23. Yes, I'm old.

Now, if I run this query, oh, look at that, there we are. Because we matched that date of birth. Now all we have to do is tell our customer list to get the values for itself from that query. So, open up the customer list, go into its controls here, go into its properties, and where it says record source, we're going to make this the customer list query that we just made. Save it, close it, close it, open it, and there you go. That's the easy way.

We can open up one of these other customers just by doing that, which we covered in the other videos. See, that's one easy way to filter based on a date of birth. Now keep in mind, you have to have this main menu form open, because if you try to open up the customer list form and the main menu isn't open, you're going to get enter parameter value because you can't find it. So you could, if you wanted to, at this point, type in 1972-10-23, and it will work. But you have to have that form open if you want to use this criteria.

Now, what if you don't have a default value in here? What if you don't have this value set, which normally you wouldn't. If you're having a, you know, entered patient information form, you might not want to have to enter their data, but they're just looking for someone. See, now you get no records if that's blank. Because you're telling the criteria, it's got to be equal to that. So who's got a date of birth that's equal to null? Nobody in there, I don't think.

So what do you do if this is blank or null? Well, now we're getting a little more advanced. We can use two more functions, the if function, immediate if, and is null. I got videos for these. If you want to go learn more about them, here's the if function, immediate if. And here's a video that will teach you about null, is null, not null, all that stuff. And if you want to learn about null, go watch this.

So what we can do is go into that query, design view, click on that criteria, zoom in. I mean, I am going to zoom in this time. This gets a little complicated. I'm going to copy this to my clipboard. Now we're going to say if is null that guy. If that's null, what you're going to use for the criteria for this record is the value of the record itself, in this case, customer since. Otherwise, the criteria is going to be the criteria on the form. And that's it. Tricky, I know. But that's just how it works. So if the criteria is null, then use the date of this record as its own criteria. So you'll always get the record. Because it is its own criteria. Otherwise, if this is not null, then use it as the criteria. This is a little more advanced stuff. I usually teach this in my higher expert-level classes. Save it. Close it. Open it up.

Now, what's that? Expression is typed in incorrectly, or it's too complex to be available. Okay. Well, what does this mean? All right. Don't hit debug. Hit it.

Let me explain to you what happened here. I'm going to leave this in the video because this happens a lot. This happens to me all the time. And it's just a quirk with the way that we wrote the statement. Let's go back into the query real quick, design view. And again, let's zoom in on that field.

Can you see what's different now? In fact, let me do this so it's easier to read right there. Can you see what's different from what we typed in? Notice the quotes. I put customer since in here. And Access, trying to be nice, converted that into a string. The actual value, customer since. I don't want the actual value, customer since. But if you do that in the zoom box, it puts quotes around it for you. If you do it down here, if I just put in customer since like this and hit tab, look at that. It puts the brackets around it. See, it fixed it for you. This is a lot smarter than the zoom box. So the answer is you got to have brackets around this so Access sees it as a field and not as a text string. And this happens a lot. This comes up a lot in the forums. I get emailed this question all the time. So I'm leaving it in the video so you learn and see it. Quotes and brackets, very important in Access.

Save it. If you run the query right from here, you should get results. And since there's no criteria in there, that's what you want. See, perfect. But if I do type in 1972-10-23 and hit customer list, there it goes. It's working. See that?

So there you go. That's the basics of how to filter by DOB. Now I told you at the beginning of the video that I'm going to show you the non-programmer way how to do this. But it's actually, in my opinion, easier to do this if you know a little bit of VB. Instead of having to make a separate query and use all that criteria, we can do it with a couple of lines of VB code much, much easier. We'll cover that in part two. We'll do the developer version of the same thing, but with some VB code.

This video is going public on Friday, August 30, 2024. We will cover this in part two. Monday is a holiday. It's Labor Day here in the States. So we'll cover it on Tuesday, September 3. Check back then for part two. 

That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. Have a good weekend, and I'll see you on Tuesday for part two. 

You can check them out at AccessExperts.com. Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office Specialist, and he not only offers Access application development but he also provides one-on-one tutoring services. If you need someone to hold your hand and help you with your Access project, Sammy is your guy. Check him out at ShamaConsultancy.com.

Give a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free. Click the bell icon and select "All" to receive notifications when new videos are posted. 

Want to learn more? Click the "Show More" link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. Once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted like they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list, and you can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly.

If you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the "Join" button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my Code Vault, lots of VBA source code, template downloads, and lots more. I'll talk more about these perks at the end of the video.

Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the Tip Jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I got some puppies to feed. But don't worry, no matter what, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.

If you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over 4 hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. Did I mention it's completely free? The whole thing. Free. 4 hours. Go watch it.

And okay, okay, a lot of you have told me that you don't have time to sit through a 4-hour course. So I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster in about 30 minutes. And no, I didn't just put the video on fast forward. But I'll put a link to this down below as well.

Now, if you like Level 1, Level 2 is just a dollar. That's it. One dollar. That's another whole 90-minute course. Level 2 is also free for paid members of any level, including supporters. So if you're a member, go watch Level 2. It's free.

Okay, want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the TechHelp page, and you'll have a better chance of getting it answered.

And while you're on my website, be sure to stop by my Access Forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you.

Be sure to follow my blog, find me on Twitter, and of course on YouTube. Yeah, I'm on Facebook too, but I don't like Facebook. Don't get me started.

Now, let's talk more about those member perks if you do decide to join as a paid member. There are different levels: Silver, Gold, Platinum, and Diamond. Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks. Gold members get all the previous perks, plus access to download the sample databases that I build in my TechHelp videos, plus access to my Code Vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list for me to read them, and if I like your question, you got a good chance of it being answered. You'll also get one free expert level class each month after you've finished the beginner series.

Platinum members get all the previous perks, plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.

And finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.

So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.

For a complete video tutorial on this topic, please visit https://599cd.com/FilterByDOB?key=Blogger

Microsoft Access Quick Queries #19: Multi-Page Forms, Referential Integrity, Numeric Sort, More...

 

Welcome to another TechHelp quick queries video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Welcome to number 19. Can't believe there are 19 of these already. Is there a quick answer? Quick answers? I can't talk today. Quick answers to queries. Although, you try saying that 10 times fast. Quick answers to queries about Microsoft Access may or may not contain questions about actual queries, although probably, I think we have one on there today. 

Anyways, let's get to it. Today's first question comes from TXM More. He says, how do you approach a two-page form, that is, you have a form in which you are entering a ton of data and you want to break it up into two separate forms, aka page one and page two? 

Well, there's a lot of things you could do. First, you could just make it a really big form. You can make it a really long form and just scroll down, and as your user is typing, just you know, go to the next field and just scroll down. You could set the form size whatever you want on the screen, but the user can scroll up and down as much as they want. Another thing you could do is use tab controls.

Now, when tab controls first came out, I didn't like them because they were kind of buggy, but in recent versions, they've gotten much better, and you can put this tab control on your form, right, and you can put like contact info, an address block, or more info. So you've got the customer information up here and then all their other information, you know, you can put their phone numbers and stuff in here. So a tab control works too if you want to switch between different tabs, I guess, inside of the customer form. That's another option.

You could make subforms that you can switch between, and in this video, I show you how to do that with a vehicle maintenance database. You've got the vehicle over here, the maintenance history, and the future maintenance coming up, and those are just two different forms inside of a subform control and you just change it with the subform's SourceObject property. That requires a tiny little bit of VBA, but this is the method I personally prefer. I like doing this because these can be completely different subforms in here.

Or, you could use any navigation form that you want. I personally like making a form and then putting buttons on it to go to other forms. You could even set up something like this and make like a wizard, right, where the first page has just the fields you want, and then on the bottom is a button that opens up the next form and you make individual forms for it. You know, the first one is enter your name and address, you click next and it goes to another form and it says okay, enter in your whatever, you know, banking information. I don't care. All these forms are still linked to the same table, to the same record, right, but they just display different information and you can have forward and backward buttons and all that stuff. 

So it's all about how you want to do it. If you want to see some more examples, post a comment down below, and maybe if enough people are interested, I'll make a whole separate video just on this topic. 

Alright? Okay. 

Xavier says, are you going to teach us how to embed images in the HTML message? This comment was on my video on how to send email using CDO (Collaborative Data Objects) in Microsoft Access where you can send right from the database and not have to go through a program like Outlook. In the extended cut for the members, I show how to format HTML messages. 

Well, if you're formatting your message as HTML, then all you have to do is put an image tag in it. This is just a basic HTML command, right, starts off with image src=" and then a link to your picture. I recommend putting your pictures on your website or at least a public web folder, right, a Gmail or a Dropbox folder that's public because you don't want to be sending images through email. It makes the emails big and slows down the whole system. So just drop your image on your website somewhere or in a public folder and stick an image tag in there. And if you want to hyperlink that you can too. So that's just knowing a little bit of HTML. I do have an HTML basics course on my website. It's real basic, but I'll put a link to it down below.

Ralph PHP says, I understand that using short text field is a good workaround if you're not doing math on a field, but doesn't Access sort numbers incorrectly if they are stored as text? 

Yes, if you sort a short text field and you've got numbers in it, they're going to sort alphanumerically. Alright, so if I have a table and I've got, let's say, an ID, right, and I've got my value, let's call it, and that's a short text field, okay, my table, and if we put some numbers in here, alright, in my value, like 101, 2, 34, 14, whatever, okay, if we sort this field, alright, right-click, sort A-Z, you're going to get that. That's an alphanumeric sort, which looks at it letter by letter.

Okay, if you want to sort this numerically, just use a query and convert that over to a number. Alright, so you'll create a query and you'll bring in that table, right, my table. Okay, bring in whatever you want and then right here we're going to say my number is just convert to whatever you want, CLng, that's convert to long, my value. Okay, and if you run it now, you see there's numbers. Now here's the big difference, you can see these ones are lined up on the right side of that cell, let's call it the column, these are on the left. Left tells you that's a text value, right tells you it's a numeric value. Okay, and now if you sort it, whether you sort it in the query or not, you can sort it right here. Right, and now you'll see those sort by the proper number. That's all you've got to do, use a query. 

Things like zip codes, phone numbers, social security numbers, I store those as text. They're easier to manipulate, they're easier to work with, they're easier to deal with than actually storing them as numbers. Plus, with some things like zip codes, you lose that leading zero, but it's real easy to convert it to a number if you want to sort them that way. 

Okay, and that's just, again, the CLng function. There's a whole bunch of different type conversion functions. There's convert to byte, currency, date, double, long, whatever you want. Now, this video talks about all of them.

Lucian says, dude looks like Charlie Sheen. Thanks for the help. I certainly hope you mean the Major League young, handsome, attractive Charlie Sheen and not the older "winning" Charlie Sheen. This guy. Yeah, I could see it. Okay. Alright. This guy, I don't know, actually he's still not that bad looking. He's, what, pushing 60. So, not a bad looking guy for his age. 

I've been getting a lot of emails like this one, a lot of comments like this one. Shaky says, is there any way to receive emails without Outlook? Yeah, it's possible, but it's not easy. I've been using Outlook myself to retrieve emails. I've got my Access server database, right, sits in the corner. It sends and receives emails. I send through CDO and Gmail, but I receive from Outlook. It's really easy to receive email through Outlook. I cover it in my email seminar. I'll put a link to that down below. But you've got to have Outlook for it, and I'm worried that they're going to change it so you can't do that in the future. So I am looking for a solution myself. 

I know it's got to be possible to connect using Google's API or something like that to connect and pull in emails, but I just haven't done it yet. But I'll be on the lookout for a solution myself. If anybody knows of a good solution, let me know too, that doesn't require having to have an email program. 

Next up, Elise has a few questions. She says, how do I get Access? I use an iMac. My condolences. I believe I can't use this for Access, correct? And I do have a Microsoft Surface. Can I use Access on that device?

First of all, I pick on Mac users a lot, but I do have respect for Apple products. They're just, with Apple, you're kind of in the walled garden. It's whatever Apple says you can use, and they're very hard to tinker with and play with. I like PC and Android because you can get in there and get your hands dirty and break stuff, right? With a Mac, it's like this is what you're using, these are the colors you hang outside. It's like, no. I haven't really used a Mac product since an Apple II in high school.

But let's address these questions. First of all, how do you get Access? Well, I got a whole video on how you can buy Access. There is no free version available, like some of the other apps like Word and Outlook and Excel. They've got free versions. There is no free version of Access. I currently use the Microsoft Apps for Business, which is eight bucks a month, which I think for a business application like Access is a steal. Okay, now this was as of October 2022. I don't think the prices have gone up since. I could be wrong, check Microsoft's website, and they rename stuff all the time. So it might even have a different name by now, but they do definitely still have a version. I recommend the 365 subscription. It's a small monthly fee instead of a big upfront charge, but whatever works for you. But this video explains a lot more.

Alright, how do you use it on your iMac? Well, you are right, Access by itself does not natively run on a Mac. This question comes up often in our forums, and Alex, my right-hand man, he's got a bunch of different things here. You know, you can use VirtualBox, there's all kinds of things you can do. I'm going to leave this up to Alex. Alex, why don't you put together a video on how to get Access to run on your Mac because you're a Mac user? And there's those different options that are available. 

I just asked ChatGPT to see if it's got any new information, and basically it says, yeah, use a virtual machine, Parallels Desktop, VMware Fusion, VirtualBox. That'll basically create a virtual Windows machine inside of your Mac OS. There's Boot Camp which dual boots, you can remote access into a Windows PC if you've got a Windows PC running around. There's apparently a program now called Crossover. It says that not all features of Access may work perfectly with Crossover, so I don't know about that one. And of course, there's a cloud service. A cloud service is basically a PC in the cloud on the internet. You set up your own Windows desktop with whatever applications you want to install, including Access. 

And this is great even if you are PC users, and you want to have a shared Access database up in the cloud that a bunch of people can use. I've been recommending Access Database Cloud for years, and they're fantastic. So that's definitely another option.

And as far as using it on your Microsoft Surface, well, yeah, you should be able to. Give it a try. And again, I posted this to ChatGPT just to get its opinion. The versions of the Surface that run Windows should definitely be able to use it, like the Surface Pro. Some of the other ones, as long as you're not running in Windows S mode, which only allows apps from the Microsoft Store, and Access is not available there. The Surface Duo and Neo are using Android, so you can't use those. Some of our other users, like Adam, one of our moderators, says he uses it just fine with the Surface. Jeffrey Crafts says yep. So yeah, as long as it's one of those better Microsoft Surfaces, you shouldn't have any problems.

Gary asks, I use Microsoft Outlook 365 for my work emails, is there any way to have those emails appended to a database so I can search and/or sort them, etc.? 

Yeah, the easiest way to do that is to send those emails from Access using the techniques that I mentioned earlier with my sending email with CDO. You can also send the emails through Outlook if you want to, although I recommend getting away from that. Can you use Access to pull information in from Outlook? Yeah, you can. If you've already sent the emails from Outlook, you can import them into Access, or you can even attach to those folders in Outlook from Access to look through them. But again, this only works with classic Outlook, not the new Outlook. Okay?

And if you're not sure which version of Outlook you got, check this video out. I did a video on an Office Watch article talking about how the new Outlook is not ready. You lose a lot of functionality with the new Outlook about sending emails and stuff especially. So the new Outlook is basically just kind of like a web version of it. It's not the classic, beautiful Outlook that we've known for decades.

This was a comment on YouTube regarding my move it up and down with the keys. So you can use on a continuous form. You can up and down arrow to move like an Excel spreadsheet through your continuous form, and one user said that this is not needed. Go to Options, Client Settings, and change the up/down, how it works. 

Yes, this is correct. You can change how the keys work on your system, but that's only in your client. Okay, if you're distributing a database to everyone else, you'd have to go around to everyone else's machines and make the same change. Okay, whereas if you do it with the move up/down keys trick that I show you, it's built into the database, and everyone gets the same functionality. So it's all about how you want it to work. Here's a link to that video if you want to check it out.

Alright, this one's from my guys beating me up because I forgot to cover something in the previous quick queries. When we were talking about copying and pasting controls, and Sammy says, you can somewhat control where Access will paste after you've copied the control. You want to click on another control, and then paste, and Access will paste under the control you clicked on between the copy and paste. And I'm like, yeah, I didn't cover all the tricks in the last video. Yeah, I know there's tons of tricks. There's so many tricks I don't always use all of myself. So if it's not something that I use on a regular basis, I forget about it. And one of the great things about my job is that I love learning things from you guys too. You guys teach me a lot of cool stuff that I never knew before, just a little like keyboard shortcuts and tricks like that. So let me show you what Sammy's talking about.

So if you're in a form, right, and you take this guy and you go copy, paste, okay, if you are on this guy and you go copy, paste immediately, it goes below it. If you don't have anything selected, it goes up there. Okay, what Sammy's saying is if you want to go under here, click on that guy first and it puts it under there. Okay, it actually lines up with the label, but right, if you click on, let me delete this guy. 

Let's see, you want to put something on the bottom here.Let's see you click on this. Copy if you click on that and paste it goes right there. So there's so many little tricks, and I can't cover them all, but that's what Quick Queries are for so we can go over all this weird stuff together. So thanks, Sammy. Here's an interesting question in my form. I'm supposed to by Jerry. Uh, Jerry says they have ActiveControl and PreviousControl.

So ActiveControl tells you what control you're on, like a button you click on or what text box you're sitting in. PreviousControl tells you where you've been. Okay, but he said he couldn't find a NextControl or FutureControl. And sometimes when I think I heard about this stuff, my head hurts. 

Um, yeah, and not being funny, you can figure out what the next control would be if you're tabbing right through the tab order. In fact, I asked Chat GPT to give me some code, and I have not tested this, but this looks about right. What you could do is you could say, "Hey, you know, if you click a button or whatever, look through the tab index," because every control on your form has a tab index, right? One, two, three, four, five. So that Access knows where to tab next.

Okay, and you can customize that order. I covered tab order in a lot of videos. But this will actually see what's the control with the next tab order. All right, and again, I haven't tested this, but it seems about right. So, yeah, okay, that's definitely possible. I can't think of a reason to do this when it would be useful, but is it possible? Yeah, possible.

Here's a great question by William. He says, "I have a database I made myself. I'm trying to compact and repair like I was told to do. When I try to compact and repair, I get an error message saying the compact and repair operation has been canceled. You might not have adequate permission to the folder the database is located in. It's on my own laptop. It's my personal database just used by me. My question is, what can I do to fix this problem?"

All right, I have a question. Can I put my database in a Google folder to do the compact and repair? All right, that's the problem right there, but there are some things. Obviously, run down the troubleshooter. Make sure it's in a trusted location. Okay, now the problem with putting it in a Google Drive folder, okay, is that Google Drive is constantly trying to synchronize whatever is in that folder with its copy in the cloud and your other machines. Okay, so if you're doing a compact and repair while Google's trying to back that file up, it's not going to say exactly that you don't have adequate permission because the file is open. Someone's using it. 

All right, so you got a couple of options. One thing you could do is go to your Google Drive while it's running. All right, right-click on it, go to settings, and then pause syncing. Okay, that will pause Google Drive from trying to make backup, so whatever is in your drive folder, and at that point, you should be able to do your compact and repair. Just remember to make sure you turn it back on. Or the other option is to not store your database in your Google Drive folder. What I'd personally do is I keep my databases in my C drive, uh, in a different  uh, folder that's not backed up in my G drive, for example, and then I have my nightly Access backup copy my databases into my Google Drive folder for backup. 

Okay, so don't run your actual Access database out of a Google Drive or a Dropbox or a OneDrive or any of those shared folder-type setups. All right, this question comes up so much. I got a whole separate video on it. Do not run Access out of a shared folder like that. Even if you're the only person using it, you can have problems like this. All right, I used to use, um... I think it was Google Drive. This is going back probably four or five years, but I used to have an office, and I used to drive, you know, from my house to my office, and it was about a 30-minute drive from Cape Coral to Fort Myers. And in the morning, you know, if I was on my computer, I would, you know, have my database open, and it was in my Google Drive, and I'd close it. 

And then by the time I got to the office in Fort Myers, it had had enough time to sync up. It was just a small database file, maybe, you know, a couple hundred megs, and no problems if you're the only person using it. But if you got two or three people doing that, or the database doesn't sync properly, you're going to have problems, or if it tries to do something like a compact and repair. So just you're better off just not using these things with Access. It's not designed for that, and of course, I like to mention this as much as I can, make sure you got good backups of your data. Okay, okay, back up back up back up!

This question's from June. He says, "I'm having a problem creating a relationship between OrderT and OrderDetailT with referential integrity and cascade deletes. It says that data in the table violates referential integrity rules." This usually happens when you're missing a parent. Okay, you're missing an order, but you've got records in the OrderDetail table already that are looking for, you know, that are linked to a parent that you deleted. Let me show you.

All right, here's my TechHelp database. I got orders, and I got order details. Okay, these all should be matched up. I don't have any global relationships in this database. I don't use them that often, to be honest with you. I prefer to handle stuff like that in my code. But let's say, all right, we've got orders here, and we've got order details. Let's say I delete order one. All right, we got detail items in here, right? For order one, those are the three line items for order one. If I delete that order, all right, lets me do it because I've got no referential integrity. One of the things referential integrity does is it says, you know, everybody on the one side of a one-to-many relationship, you got to have that one record, or you can't have any many records. It prevents orphans and widows. Well, it prevents it prevents orphans. It doesn't prevent widows. That's a whole separate thing. 

A widow would be basically an order that doesn't have any line items which you can check for that too, but it's not quite as easy. I got a whole separate video on that one. But now if I try to go into relationships and create a relationship between OrderT and OrderDetailT, right, just to link them by order ID, enforce referential integrity, okay, hit create and say ah can't do it. All right, it violates referential integrity rules because you've got records in the system already that are no bueno.

Okay, so you're going to have to go back in there and figure out who's missing what before you can set up that referential integrity. And how do you do that? Well, let's make a query. Create, query design. I'll bring in OrderT and OrderDetailT. Now the relationship here in the query is formed because Access sees that you've got that and that they're named the same and they're the same data type. There's, they're both numbers. So this is called an ad hoc relationship. It's not an actual system relationship, but Access is like, yeah, these things are probably related. So I'll relate them. Now bring them both down here. 

And if you run the query now, you'll see they all match up. What's the problem? Well, remember a basic inner join like this says all the records from here have to match all the records from here. So I need to be able to see where this might be missing. So I need to make this an outer join. So double click on that guy. Now we're going to say I want to see all records from OrderDetailT. I want this one show me all the detail items. All right, and if this is missing, okay. See, and you get that little arrow pointing that way. It says show me all of these guys and the related one over here. Now if you run this, oh look at that. You can see there's someone's missing right here. 

Okay, so now I can go over here and I can say give me criteria is null, and now that'll show you just which orders are missing. So you've got four detail items in here that are missing a parent item. Now you can go and fix that. You can figure out what happened in the order. You can add something new. You can delete these line items. However, you want to do it, and then once you do that, you'll be able to create your referential integrity, and once you've created the referential integrity, you can't have this problem anymore in the future. Okay, okay. 

If you want to learn about referential integrity, go watch this video. I've also got a video on cascade deletes, which I don't like them. I mean they've got their place sometimes, but be very careful because cascade delete says if you've got a relationship set up between like orders and details, that's fine. You delete an order. You know, you want all the line items gone even though you shouldn't delete stuff. You should archive stuff or mark it, you know, invalid or whatever. 

But if you've got like a customer related to your orders related to the contacts, you accidentally delete that customer, all of this stuff is gone. That's going to mess up your accounting. So be very careful with cascade deletes. I also spend a lot of time with relationships and stuff like that in my Access Expert Series Level 1. We go over relational database concepts. We go over referential integrity and Expert Level 2 normalization, global relationships, all kinds of cool stuff. And if you want to learn about outer joins, go watch this missing records video. Usually you see this where, you know, you've got a relationship set up between customers and contacts or customers and orders, and you do a query, and you're like, "I'm not seeing all my customers. Why aren't they showing up in this list?" Well, that's because you've got an inner join, so they have to have an order or a contact for you to see them in that list. 

And that's where you usually set up an outer join, left join, right join, outer join, inner join, self joins, all kinds of joins. And of course, I cover all the different kinds of relationships you could possibly want to know in my relationship seminar. Yeah, this is an older image. They're not as cool as my new ones, but this is one of my first seminars I did, but it's classic, and it's got lots of great information in it. All right, so I think that's going to about do it for today. There's your Quick Queries Number 19. I hope you learned something. Live long and prosper, my friends. I'll see you next time for Quick Queries Number 20 or whenever we decide to come out with it. 

All right, take care. A special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts. Software solutions manufacturing experts specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at AccessExperts.com. Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office Specialist, and he not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guide. Check them out at ShamaConsultancy.com.

For a complete video tutorial on this topic, please visit https://599cd.com/QQ19