Saturday, March 14, 2020

Bitwise Operation in an Access Query

Let me preface this article by saying this is not a topic I am really familiar with. I only decided to write it to share an experience I had when I recently participated in a forum thread discussion on this subject. So, if I misspoke or provided inaccurate information below, please do not hesitate to let me know. I would really appreciate it.

If you are not familiar with "bitwise operation," it is basically a way to perform value calculations or comparisons at the bit level. For example, one might need to use bitwise operation to check if a particular bit within a value is either ON (1) or OFF (0). In essence, performing a bitwise operation is a way to determine the state (1 or 0) of each individual bit of a particular value.

There are [as far as I know] three bitwise operators: AND, OR, and XOR

The AND operator returns a value of 1 if both the bits being compared are 1.

The OR operator returns a value of 1 if either of the bits being compared is a 1.

And the XOR operator returns a value of 1 if, and only if, one of the bits being compared is a 1.

Okay, so how do we normally perform a bitwise operation in Access. Well, we would usually use VBA for this. For example, the following function can be used to perform an AND bitwise operation.

Public Function bitwiseAND(var As Long, bit As Long) As Boolean
'thedbguy@gmail.com
'3/14/2020
'Returns True if the bit being tested is ON

bit = 2 ^ bit

bitwiseAND = var And bit

End Function

To check if bit position #3 is ON (remember, bit position is zero-based), we might do something like:

?bitwiseAND(45,2)
True

The above function returned True because 45 is represented as 101101 in binary, and the third bit from the right is a 1.

I hope the above example is clear. So, as you can see, we can use VBA in Access to perform bitwise operation. However, the topic of this article is how can we do it in an Access query? Obviously, we can use the above function in a query, but it would have been better if we can simply use the bitwise operators (AND, OR, and XOR) in SQL. Unfortunately, not in Access. However, the bitwise operators are available in T-SQL. So, one potential solution, if someone needs to perform bitwise operation against multiple sets of data in a query is to migrate the data into SQL Server.

Okay, now we come to the point of this article. So, a forum poster asked how to perform a bitwise AND operation in an Access query because, for some reason or another, they don't want to use VBA and migrating to SQL Server is not an option. I did a little research and came up with the following alternate solution.

([FieldName]\2^BitPosition) Mod 2

In the above expression, we perform an integer division against a numeric field using the decimal value of the bit position we want to check and then check if the result is divisible by 2. If the bit position being checked is ON, then the result of this expression will be a 1. If the bit is OFF, then the result will be a 0.

And there you have it. You can use the above expression in an Access query to check if a bit is on or off. I can't think of a good example or a situation where this solution would apply, so please let me know if you have any situation where this information could be useful.

As always, thanks for reading...

Friday, November 22, 2019

Access Error: "Query is Corrupt"



By now, most of you would have already known about this latest Access bug from Microsoft as explained on their website and discussed in many forums and blog posts. If so, you may be wondering why am I writing this blog.

That's actually a very good question. Well, the simple answer is I decided to do my part and offer a temporary solution I think is a lot simpler than what Microsoft recommended. This approach avoids the need to modify any saved queries or SQL statements in VBA code (up to an extent - I can't imagine all the possible scenarios out there).

This utility can be deployed to your users, and they can apply the fix themselves. And when the actual fix becomes available from Microsoft, the users can rerun the utility to remove the temporary fix.

To download the "Query is corrupt" bug fix utility, go to this post at UtterAccess. If you find any problems or have any recommendations, please do not hesitate to let me know.

I hope this helps at least those using Access 2010 or 2013, where a fix from Microsoft is still pending. (If you're using Access 2016 or 2019, the fix is already available to you.)

Sunday, June 2, 2019

List Table Fields

I recently needed a way to list all the table fields from an Access database project, so I could export it into Excel and format it for the user's review (just to make sure I covered all the user's requirements). Unfortunately, all I found, looking around for a utility to do it, was a way to print a report showing all the fields. Unless I simply miissed it, I couldn't find one that exports it to Excel for further consumption. So, I decided to create this demo.

This demo is similar to my other "list" demos where you get to browse for the database file and clicking a button will populate a temporary table with all the information gathered from the selected database. You can then export the table's data into Excel, as desired. The only difference with this new version is that it should be able to handle a password-protected file. If the selected file is password protected, the user is presented with a prompt to enter the password.

To download this demo, please click on the image below. As usual, if you have any comments or suggestions for improvements or found any bugs with this demo, please do not hesitate to let me know. I would really appreciate it.

(click on the above image to download the demo file)

Thank you for reading!

Saturday, May 11, 2019

Listbox Selected Property and the Form Load Event

Here's something new I just learned from a recent forum discussion: A Listbox's AfterUpdate event will fire when VBA is used to select an item on the list using the Form's OnLoad event. What? Did you all know that? I wasn't aware of it until now.

Typically, the AfterUpdate event of a control does not fire when the value of that control was updated using code. If you want the code behind the AfterUpdate event to execute when you update a control using VBA, you will have to specifically call it. For example:

Call ListboxName_AfterUpdate

However, as I was saying, there is a specific scenario (I know of only one, so far) where a Listbox's AfterUpdate event will fire, without specifically calling it, when the Listbox is manipulated using VBA code. Here's the deal...

As demonstrated in this forum discussion, the OP used the Selected property to "highlight" an item in a Listbox during the Form's Load event. Interestingly enough, this causes the form to fire the Listbox's AfterUpdate event as well. Typically, using the Selected property does not update the value of a Listbox, which means the AfterUpdate event shouldn't fire. But for some reason, this is not the case during the Form's Load event. It turns out, setting the Selected property of an item in a Listbox during the Form's Load event also updates the Listbox's Value property. However, there is a twist... If we used the "normal" way of updating the Value of the Listbox, then the AfterUpdate event does not fire. To clarify, we can use any of the following methods to change the Value of the Listbox.

Me.ListboxName = "SomeValue" 'if it's bound to a Text column
Me.ListboxName = 1 'if it's bound to an ID column
Me.ListboxName = Me.ListboxName.ItemData(2)

So, the puzzle is, what is going on here? Why does it happen when we use the Selected property? Also, why does it only happen during the Form's Load event? Once the Form is fully opened, using the Seleted property merely highlights the item. It does not update its Value, and the AfterUpdate event does not fire. Equally, using any of the methods shown above to modify the Value of the Listbox at this point also does not fire the AfterUpdate event.

If you have any ideas or logical explanation for this, I would really like to hear it. If I find out the answer somewhere else, I will definitely let you know.

Thank you for reading!




Thursday, December 27, 2018

Keyboard Key Code Values

This was an old article on my website, which I thought might be good to resurrect in case people might still find it useful.

The Form's Key events can be used to determine which key on the keyboard was pressed so that certain user actions can be intercepted. Access provides some built-in KeyCode Constants that can be used for this situation. However, I couldn't find a complete list of all the possible Key Code Values anywhere, so I decided to create this page.

Below is an image of a typical keyboard layout. Hovering your mouse over each key will display the corresponding Key Code Value.











Enjoy!