Option Compare
By Richard Rost
12 months ago
Option Compare: Database v Binary v Text in VBA
In this Microsoft Access tutorial, you will learn how to utilize the Option Compare statement in VBA, exploring the differences between Database, Binary, and Text settings. Understand how these options affect string comparisons and sorting, and ensure accurate case-sensitive or case-insensitive searches in your Access applications.
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, Option Compare, Binary versus Text, Microsoft Access VBA, InStr function, case-sensitive search, case-insensitive search, default Binary, regional settings, Option Explicit, legacy compatibility, database sorting, ASCII values, international sorting, email lookup, TechHelp video, Richard Rost
Subscribe to Option Compare
Get notifications when this page is updated
Transcript
Welcome to another TechHelp fast tips video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Today, we're going to talk about the Option Compare keyword. There are three options: Compare, Binary, and Text. We're going to talk about those in today's video.
I was recently helping someone with a database problem, and it goes something like this. This isn't exactly what it is, but this shows you the problem. Okay, so I've got a button here, and the goal is to check to see if the email address listed here is somewhere in the notes field. It's really simple code. It says if InStr, the InStr function, if inside of notes I find the email address, then I just want to say "yep," otherwise "nope."
If you're not familiar with the InStr function, here's a video on it. It returns a zero if it doesn't find one string inside the other one. Otherwise, it returns its location in the string. Now just looking at this, I see my email address is right there. Okay, so this should return "yep," but when I hit it, it gives me a "no." Why is that? Well, let's take a look and see if we can figure it out.
Here's the entire module. In fact, I'm going to delete everything after that. We don't need all that stuff. This is it. This is the entire module. Can you figure out what the problem is and why InStr isn't working? There's the code, and I'll move this down so you can see what's in the stuff too. There's the email address, and there it is inside of notes. Why isn't it working? Pause the video, see if you can figure it out.
Well, the reason is, and if you guessed it because of the name of this video, we're missing the Option Compare statement. Now, up top by default, you should have Option Compare Database up here, and I also strongly recommend Option Explicit. Option Explicit - I got a whole separate video on that one. I'll put a link to it below. That one says that if there are any undeclared variables in here, let me know, give me a warning, don't compile properly. If you go to debug, compile, it'll catch it.
So that's what Option Explicit does. The other one, Option Compare, tells Access how to compare text, how to do comparisons, how to do sorting. There are three different options: Compare, Binary, and Text. Let's take a look at the differences.
Here they are: Compare Database, Binary, and Text. Now, the only two I usually ever use are Database and Binary. Database is a case-insensitive search, and that's the one that I use 99% of the time. So in this particular type, if I make sure I specify Option Compare Database, now when I run this, it works. I get a "yep."
Because if you don't have that specified, if you don't have this here, the default is Binary. A Binary search means it's got to be exact, character for character. And if I do it now, I get the "nope." That was the default. If you don't specify it, you get Binary. Why? Because capital A is not the same as lowercase. If I change this now to lowercase here, and hit check, I get the "yep" because now they're exactly the same.
The major difference here between Database and Binary is it's case-sensitive versus case-insensitive. If you want to do an exact search, like if you're doing passwords, you might want to make sure that it's case-sensitive. Uppercase A is different from lowercase A.
As far as the difference between Database and Text goes, again, I almost never use Text, but the difference is Option Compare Database is location-based. So if you're in, for example, one of the many countries with a different alphabet, it'll have different rules for you, whereas Option Compare Text is generic text comparisons.
So, Database Binary case-sensitive, case-insensitive, apple and apple are the same here, apple is not the same as apple. The capital A is going to be sorted lower than the lowercase A because this is actually ASCII 65, and this is ASCII something higher. I don't know the values off the top. I just always remember that ASCII 65 is a capital A. It's 97 for lowercase. I had to look it up. Sorry, I hate not knowing something.
The differences between Database and Text is that Database is location-aware. It's tied to your local regional settings. Where does this come up? For example, in Sweden, in English, Z always comes after Y, but in Sweden, you got this Ö character, and that may come after Z, and that's if you've got your location set to Swedish, so that it makes a difference.
So basically, just make sure you've got Option Compare Database, which is the normal thing that gets inserted if you create a new module. If you go Create and then Module, you should get Option Compare Database and Option Explicit at the top of any new module. The client I was helping before that didn't have this just had somehow deleted that or was working with an old, old database that didn't have that in there.
As far as Option Explicit goes, that's in here under Tools and then Options. You want to make sure this box is checked: Require Variable Declaration. Very important, leave that guy on.
As far as the other option goes, there really isn't a place where you can set it if you want, like all your modules to be Binary. But if you go into Access Options under General, there's this new database sort orders when you create a new database, and the options in here, you've got General Legacy, General, and then a whole bunch of different languages in here. So if you are Swedish, for example, pick Swedish, and you'll get that type of default sort.
There really isn't an option to default to Binary. If you want Binary search or sorting, you just have to come into here and just change this manually to Binary. But this is something that I do like maybe once in a while. I'll set up a database that's got, you know, I'll set up a module that has Binary set there so that any functions I put in that module are case sensitive. Usually, I don't bother with it.
As far as General versus General Legacy, I leave it in Legacy so it's compatible with older databases, but I've never had a problem with it. But then again, I don't really deal with foreign language databases. English is it for me. So it's one of the things I tell lots of people to email me, like, hey, can you help me with this specific rule? I'm in Germany. No, sorry. I don't have any experience with other language versions of Access. That's my one downfall. Sorry, I don't know any other languages. All the languages I know are either English or programming languages, a little bit of Klingon.
So that's it. There you go. There's your fast tip TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Option Compare keyword
Option Compare Database
Option Compare Binary
Option Compare Text
InStr function overview
Case sensitivity in text comparison
Impact of Option Compare on InStr
Binary vs. Database comparison
Region-specific text sorting
ASCII values for text comparison
Setting default sort order for new databases
Difference between General and General Legacy sort orders
COMMERCIAL:
In today's video, we're going to explore the Option Compare keyword in Access VBA - Compare, Binary, and Text. You'll learn why your InStr function might not work if you skip specifying Option Compare Database, which performs case-insensitive searches. Find out why Binary demands exact character matches and how Text falls under generic text comparisons. Discover how locale settings affect sort orders, and understand the importance of Option Explicit for catching undeclared variables. Get these fundamentals straight to ensure your code runs smoothly. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main purpose of the Option Compare keyword in Access?
A. To specify how Access should compare text and perform sorting
B. To define how variables are declared in a module
C. To allow for the use of specific language settings in a database
D. To manage user permissions within a form
Q2. What are the three options available for the Option Compare keyword?
A. Database, Legacy, and Text
B. Database, Binary, and General
C. Compare, Binary, and Text
D. Compare, General, and Legacy
Q3. What is the default Option Compare setting if none is specified in Access?
A. Database
B. Text
C. Legacy
D. Binary
Q4. What is the major difference between Option Compare Database and Option Compare Binary?
A. Database considers case sensitivity, while Binary ignores it.
B. Binary is case-sensitive, while Database is case-insensitive.
C. Binary is faster than Database for text comparison.
D. Database uses ASCII values for sorting, while Binary uses Unicode.
Q5. When would you typically want to use Option Compare Binary?
A. When doing case-insensitive comparisons
B. When performing case-sensitive comparisons like password checking
C. When comparing numbers instead of text
D. When working with databases in multiple languages
Q6. What does Option Compare Database take into account that Option Compare Text does not?
A. Binary encoding rules
B. Regional settings for sorting
C. File size limitations
D. Character limits in text fields
Q7. What should be done to ensure undeclared variables are caught during debugging?
A. Use Option Compare Text
B. Set Option Compare Binary
C. Enable Option Explicit
D. Disable Option Compare Database
Q8. Why might Swedish users need to be particularly aware of the Option Compare setting?
A. The sorting order for certain characters differs from English
B. Access defaults to Binary for Swedish localization
C. Swedish users require Option Compare Text for all operations
D. Option Compare settings are irrelevant for Swedish users
Q9. What setting should you ensure is enabled in Tools -> Options for better error handling in your code?
A. Automatically correct errors
B. Require Variable Declaration (Option Explicit)
C. Case Insensitive Sorting
D. Enable Legacy Database Support
Answers: 1-A; 2-C; 3-D; 4-B; 5-B; 6-B; 7-C; 8-A; 9-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone is all about the Option Compare keyword in Microsoft Access VBA. I'm your instructor, Richard Rost, and today we'll explore how to effectively use the Option Compare statement in your Access projects.
Recently, I assisted someone dealing with a database issue that revolved around the Option Compare keyword. This situation involved a button set up to check if a specific email address appears in a notes field. The logic here is straightforward: use the InStr function to verify if the email is present in the notes. If found, the result is "yep"; otherwise, it's "nope."
For those unfamiliar, the InStr function checks for the presence of one string within another. It returns a zero if the string is not found, or the position of the match if it is found. In the given situation, although the email address was clearly visible, the result was "nope" instead of "yep." So why wasn't it working as expected?
The problem stemmed from the absence of the Option Compare statement in the module. By default, you should have Option Compare Database at the top, along with Option Explicit. Option Explicit ensures that all variables must be declared, helping prevent errors and enabling more efficient debugging.
Option Compare guides Access on how to handle text comparisons and sorting. There are three choices: Compare, Binary, and Text. Most often, I use Compare Database, which conducts a case-insensitive search. This scenario, lacking the Option Compare Database statement, defaulted to Binary, which requires an exact match, including case sensitivity. For instance, if the email strings' cases don't match exactly, the result is a "nope." In this situation, Binary compels a letter-for-letter match where capital letters differ from lowercase ones.
The distinction between Database and Binary comes down to case sensitivity. For instance, passwords often require Binary for precise, case-sensitive matching. Meanwhile, the difference between Database and Text relates to location-based sorting. While I seldom utilize Text, it's available for general text comparisons, as opposed to Database, which considers regional settings. This becomes significant in countries like Sweden, where characters sort differently.
To ensure proper functionality, confirm that your new modules begin with Option Compare Database and Option Explicit. If these aren't present, you might be working with an outdated database or have accidentally removed them.
For Option Explicit, go to Tools and Options, and ensure you check "Require Variable Declaration." Although there's no direct place to default all modules to Binary, you can manually adjust this in each module when necessary. Additionally, in Access Options under General, you can select sort orders for new databases, adjusting them to match specific regional settings if required.
Regarding the General versus General Legacy settings, I default to Legacy for compatibility with older databases. Personally, I haven't encountered issues with this, but consider sticking with what suits your database's language requirements.
That's all for today. If you're interested in comprehensive, step-by-step guidance on these topics, visit my website for complete video tutorials. Live long and prosper, my friends.
Topic List
Option Compare keyword
Option Compare Database
Option Compare Binary
Option Compare Text
InStr function overview
Case sensitivity in text comparison
Impact of Option Compare on InStr
Binary vs. Database comparison
Region-specific text sorting
ASCII values for text comparison
Setting default sort order for new databases
Difference between General and General Legacy sort orders
|
