September 2010 ~ LEARN MS-ACCESS TIPS AND TRICKS

Hexadecimal Number System

a.p.r. pillaiSeptember 29, 2010Hexadecimal Numbers, How Tos No comments

Continuation of earlier Articles:

Hexadecimal numbers provide yet another way of writing binary values, offering a more compact form than octal numbers. This number system is based on 16 as its radix (or base). Following the general rule of number systems, the Base-16 system has numeral values ranging from 0 to 15 (i.e., one less than the base). Similar to the decimal system, it uses the digits 0 to 9 for the first ten values. For the values 10 to 15, the letters A to F are used as single-digit representations.

Hexadecimal Decimal
 0            0
 1            1
 2            2
 3            3
 4            4
 5            5
 6            6
 7            7
 8            8
 9            9
 A           10
 B           11
 C           12
 D           13
 E           14
 F           15

Now, let us see how we can convert binary numbers into hexadecimal form. We will use the same method we have used for converting Binary to Octal form. We have formed groups of 3 binary digits, each with a binary value of 011,111,111 (equivalent to 255 decimal), to find the Octal Number &O377. For a Hexadecimal Number, we must take groups of 4 binary digits (Binary 1111 = (1+2+4+8) = 15, the maximum value of a hexadecimal digit) and add the values of binary bits to find the hexadecimal digit.

For hexadecimal conversion, we instead group the binary digits in sets of four. This works because the largest value represented by four binary digits (1111) is equal to 15, which corresponds to the highest single-digit value in hexadecimal (F). Once the digits are grouped, simply add the positional values of the binary bits in each group to determine the hexadecimal digit.

Let us find the Hexadecimal value of the Decimal Number 255.

Decimal 255 = Binary 1111,1111 = Hexadecimal digits FF

It takes 3 digits to write the quantity 255 in decimal as well as in Octal 377 (this may not be true when bigger decimal numbers are converted into Octal), but in Hexadecimal it takes only two digits: FF or ff (not case sensitive).

Let us try another example:

Decimal Number 500 = Binary Number 111110100

111,110,100 = Octal Number 764

0001,1111,0100 = Hexadecimal Number 1F4

To identify Octal Numbers, we have used prefix characters &O or &0 with the Number. Similarly, Hexadecimal numbers have &H (not case-sensitive) as the prefix to identify when entered into computers, like &H1F4, &hFF, etc.

You may type Print &H1F4 in the Debug Window and press the Enter Key to convert and print its Decimal Value.

MS-Access Functions

There are only two conversion Functions: Hex() and Oct() in Microsoft Access, both use Decimal Numbers as the parameter.

Try the following Examples, by typing them in the Debug Window to convert a few Decimal Numbers to Octal and Hexadecimal:

? OCT(255)
Result: 377

? &O377
Result: 255

? HEX(255)
Result: FF

? &hFF
Result: 255

? HEX(512)
Result: 200

? &H200
Result: 512

MS-Excel Functions

In Microsoft Excel, there are Functions for converting values to any of these forms. The list of functions is given below:

Simple Usage: X = Application.WorksheetFunction.DEC2BIN(255)

Decimal Value Range -512 to +511 for Binary.

DEC2BIN()
DEC2OCT()
DEC2HEX()
BIN2DEC()
BIN2OCT()
BIN2HEX()
OCT2DEC()
OCT2BIN()
OCT2HEX()
HEX2DEC()
HEX2OCT()
HEX2BIN()

You can convert a Decimal Value to a maximum of 99,999,999 to an Octal Number with the Function DEC2OCT() and its equal value of Octal to Decimal OCT2DEC() Function.

499,999,999,999 is the maximum decimal value for DEC2HEX() and its equal value in Hexadecimal form for HEX2DEC() Function.

With the Binary functions, you can work with the Decimal value range from -512 to 511 up to a maximum of 10 binary digits (1111111111). The left-most bit is the sign bit, i.e., if it is 1, then the value is negative, and 0, positive.

We have created two Excel Functions in Access to convert Decimal Numbers to Binary and Binary Values to Decimal.

But first, you have to attach the Excel Application Object Library file to the Access Object References List.

Open VBA Window, select References from the Tools Menu, and look for Microsoft Excel Object Library File, and put a check mark to select it.
Copy the following VBA Code and paste it into a Standard Module:

Public Function DEC2_BIN(ByVal DEC As Variant) As Variant
Dim app As Excel.Application
Dim obj As Object

Set app = CreateObject("Excel.Application")
Set obj = app.WorksheetFunction
DEC2_BIN = obj.DEC2BIN(DEC)

End Function

Public Function BIN2_DEC(ByVal BIN As Variant) As Variant
Dim app As Excel.Application
Dim obj As Object

Set app = CreateObject("Excel.Application")
Set obj = app.WorksheetFunction
BIN2_DEC = obj.Bin2Dec(BIN)

End Function

Demo Run from the Debug Window, and the output values are shown below:

? DEC2_BIN(-512)
1000000000

? DEC2_BIN(-500)
1000001100


? DEC2_BIN(511)
111111111

? BIN2_DEC(1000000000)
-512 

? BIN2_DEC(111111111)
 511

In the first example, if the leftmost Bit (10th-bit value is 512) is 1, then the value is negative. For positive values, 9 bits are used for up to a maximum decimal value of 511. This limitation is only for the Functions, and as you are aware, computers can handle very large positive/negative numbers.

In the first example, you can see that the leftmost sign Bit is 1, in the binary value 512 position, indicating that it is a negative value, and the other positive value Bits are all zeroes.

In the second example, the number -500 output in binary shows that the positive value Binary Bit value at 4 + 8 is On. That means the positive value 12 is added to -512, resulting in the value -500.

You can convert any value between -512 to 511 for Binary value conversions, as we have stated earlier with the above functions.

You may implement other functions given in the Excel Function list, in a similar way in Access, if needed.

Technorati Tags: Microsoft

Earlier Post Link References:

Octal Number System

a.p.r. pillaiSeptember 24, 2010How Tos, Octal Numbers No comments

Continued from Last Week's Post.

This is the continuation of earlier Articles:

1. Learn the Binary Number System.

2. Learn Binary Number System-2.

Please refer to the earlier Articles before continuing.

We will bring forward the result of the Decimal (Base 10) Number 255 converted to Binary for a closer look at these two numbers: 11111111.

Decimal Number 255 needs only 3 digits to write this quantity, but when we convert it into binary, it needs 8 binary digits or Bits. Earlier, Computer Programs were written using Binary Instructions. Look at the example code given below:

Right-click to open Large Image in New Tab/Window.

Source: www.wikipedia.org

Later, programming languages like Assembly Language were developed using Mnemonics (8-bit based binary instructions) like ADD, MOV, POP, etc. Present-day Compilers for high-level languages are developed using Assembly Language. A new number system was devised to write binary numbers in short form.

Octal Number System.

The Octal number system has the number 8 as its base and is known as the Octal Numbers. Based on the general rule that we have learned, Octal Numbers have digits 0 to 7 (one less than the base value 8) to write numerical quantities. Octal Numbers don't have digits 8 or 9. This Number System has been devised to write Binary Instructions for Computers in a shorter form and to write program codes easily.

For example, an 8-bit Binary instruction looks like the following:

00010111 (instruction in Octal form 027), ADD B, A (Assembly Language).

The first two bits (00) represent the operation code ADD, the next three bits (010) represent CPU Register B, and the next three bits (111) represent CPU Register A. The 8-bit binary instruction says to add the contents of register A to register B. If the instruction must be changed to (ADD A, B), add the contents of register B to A, then the last six bits must be altered to 00,111,010. This can be easily understood if it is written in Octal form 027 to 072 rather than Binary 00111010.

So, the Octal (Base-8) Number System was devised to write binary-based instructions in short form. Coming back to the Octal Number System, let us consider how we can work with these numbers. First, we will create a table similar to the decimal/binary Number Systems.

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1

We will use the same methods we have used for Binary to convert Decimal to Octal Numbers.

Example: Converting 255 into an Octal Number.

For binary-to-decimal conversion, we could simply take the highest integer value from the table and subtract it from 255 to begin the process. However, that approach does not work in this case. Looking at the table, we can see that 512 is greater than 255, so it cannot be used. The next lower value is 64, and our task is to determine how many times 64 can fit into 255.

Method-1:

255/64= Quotient=3, Remainder=63 (Here, we have to take the Quotient as the Octal Digit).

In this method, we must take Quotient 3 (64 x 3 = 192) for our result value, and the balance is 63 (i.e., 255 - 192)

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
			3

63/8 = Quotient = 7, Remainder=7

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
			3	7

7 is not divisible by 8, hence 7 goes into the Unit's position

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
			3	7	7

Method-2:

255/8 = Quotient = 31, Remainder=7

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
					7

31/8 = Quotient = 3, Remainder=7

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
				7	7

3 is not divisible by 8, hence it is taken to the third digit position.

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
			3	7	7

Writing Binary to Octal Short Form.

As I mentioned earlier, the Octal Number System has been devised to write Binary Numbers in short form. Let us see how we can do this and convert binary numbers easily into Octal numbers.

The Decimal Number 255, when converted into Binary, we get 11111111. To convert it into Octal Numbers, organize the binary digits into groups of three bits (011,111,111) from right to left and add up the binary values of each group, and write the Octal value.

011 = 1+2 = 3

111 = 1+2+4 = 7

Result: = 377 Octal.

To get a better grasp of this Number System, try converting a few more numbers on your own. Start by converting some decimal numbers into binary, then group the binary digits into sets of three bits. Next, calculate the value of each group as though they represent the first three bits of a binary number.

Since octal numbers use digits 0 through 7, they can easily be mistaken for decimal numbers by both humans and machines. To avoid confusion, octal numbers are always written with a prefix. In MS-Access (and VBA), the prefix is &O (the letter O, not case-sensitive) or &0 (digit zero). For example, the octal number 377 can be written as &O0377, &O377, or &0377.

You can try this by typing the above number in the Debug Window in the VBA Editing Screen of Microsoft Access or Excel.

Examples:

? &O0377

Result: 255

? &0377

Result: 255

? &0377 * 2

Result: 510

Next, we will learn the Base-16 (Hexadecimal) Number System.

Technorati Tags: How-Tos.

Learn Binary Number System-2

a.p.r. pillaiSeptember 15, 2010How Tos, Numbering System 1 comment

Continued from Last Week's Post

This is the continuation of last week's Article, Learn the Binary Number System

Last week, we went through the fundamentals of the Binary Number System and learned how to convert the decimal number 10 to binary, and saw different methods also.

I hope you have tried converting the sample number 255, which I gave at the end of the earlier article, using both methods shown there.

If you could not do it, then let us do it here.

Method-1:

Find the highest Integer Value from the Binary Table that goes into the Decimal Number and subtract that value from it. Here, 128 is the highest value that can be taken.

255

-128

=127

Write Binary digit 1 at the 128 (2⁷) number position underneath the Binary Table.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
								1

The next highest integer number from the Binary Table that goes into 127 is 64.

127

-64

=63

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
								1	1

Repeat this method till you arrive at the unit Value position.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
								1	1	1	1	1	1	1	1

You can cross-check the result by adding up all values taken from the 1s bit (the name of the binary digit) position to arrive at the value you were trying to convert into Binary.

Method-2:

Divide the decimal number by 2, take the remainder value, and write it at the unit position in the Binary Table.
255/2 = Quotient = 127, Remainder = 1
Next step, take the Quotient Value (127) of the previous calculation, divide it by 2, and find the remainder. Write the remainder value to the left of the earlier written binary digit (bit). Repeat this process till nothing is left to divide and write the final remainder value to the binary table.

127/2 = Quotient = 63, Remainder = 1

63/2 = Quotient = 31, Remainder = 1

31/2 = Quotient = 15, Remainder = 1

15/2 = Quotient = 7, Remainder = 1

7/2 = Quotient = 3, Remainder = 1

3/2 = Quotient = 1, Remainder = 1

1/2 = Quotient = 0, Remainder = 1

You will get the Binary Number 11111111 equal to the Decimal Number 255.

You can experiment with bigger Decimal Values or write some unknown Binary Values with random 1s and 0s and try converting them back into Decimal Numbers.

Next, let us try some additions and subtractions with Binary Numbers. If you know how to do additions and subtractions with Decimal Numbers, then you will have no problems with Binary Numbers.

Example: Addition

11101110	238
+1110111	119
101100101	357

Start adding the rightmost digits:

0+1 = 1
Next 1+1 = 2, put 0 and carry 2 to the next position (like 5+5=10, we put 0 at the unit's position and carry 1 to the next position to add)
next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position
next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position
Next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position
Next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position
Next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position
Next 1+1 carry = 2(binary 10), put 0 and put 1 in the next position.

Example: Subtraction

11001110	206
-1111111	127
1001111	79

0-1 cannot be done, so take 2 from the next position, now 2-1 = 1, but the next position on the first line becomes 0.
0-1 cannot be done, so take 2 from the next position, now 2-1 = 1, but the next position on the first line becomes 0.
0-1 cannot be done, so take 2 from the next position, now 2-1 = 1, and the next 3 positions become 0.
Take Value from the 8 value position and move forward to the 4 positions and to the 2 value position, 2-1 = 1
1-1 = 0
1-1 = 0
After moving the value forward from the 7th digit position on the top line, it is now 0. So move 2 from the next position. 2-1 = 1

You can try it out yourself, starting with smaller binary values and progressively, with bigger ones.

For your information, there is no Multiplication or Division in computers. These calculations are achieved by successive addition or subtraction of values.

Continued../-

Earlier Post Link References:

Learn Binary Number System

a.p.r. pillaiSeptember 08, 2010How Tos, Numbering System 4 comments

Introduction

Are you hesitant about learning the computer’s own language—the Binary Number System? I hope not! If you are a programmer or plan to become one, then I strongly recommend that you learn it. No, you won’t be writing full programs in binary, but sooner or later, you will encounter Binary, Octal, and Hexadecimal numbers. If you want to avoid surprises, it’s best to build a solid understanding of these number systems early on.

The good news is—it’s not as hard as it may sound. In fact, once you understand a few simple rules that apply to our familiar decimal system, you’ll discover that you can devise and work with any number system, as long as others can also interpret it and agree on its usage.

Simple Rules that Govern the Decimal Number System.

Let us explore a few simple rules that go with the Decimal Number System, which we are already familiar with.

The Decimal Number System.

The decimal Number System's Base value is 10, which is known as the Base-10 Number System.
The Base-10 Number System has 10 digits to express quantities; 0 to 9, and the highest digit value is 9, i.e., one less than the Base value of 10.
Any value more than 9 is expressed in multiples of 10.
Note: Keep this simple rule in mind: when you create a number system with a particular Base, the total number of digits in that system will always be equal to the Base value. The largest single digit in that system will be one less than the Base. You’ll see this rule in action when we explore other number systems commonly used in computers, such as Octal and Hexadecimal.
Decimal value 10 cannot be written with a single digit; instead, we put a zero in the unit's position and write 1 in the 10th position. So, decimal Value 10:
10^¹ = 10x1= 10
10⁰ (1 x 0) = 0
10+0 = 10

Let us create a table to see how each digit value is calculated and added up to the decimal quantity.

Note: Please use your Laptop or Tablet to view the Table correctly.

10⁶	10⁵	10⁴	10³	10²	10¹	10⁰
1,000,000	100,000	10,000	1,000	100	10	1
				2	5	5

2 x 10² OR 2 x 10 x 10 OR 2 x 100 = 200

5 x 10¹ OR 5 x 10 = 50

5 x 10⁰ OR 5 x 1 = 5

=======

255

=======

Following the above rules, we can devise any number system. For example, if we create a number system with Base 8, it will use the digits 0 to 7. The highest single-digit value is always one less than the base, so in this case, 7. This system does not include the digits 8 or 9. In fact, this Base-8 system—known as the Octal Number System—has long been used in the computer world. We will study it in detail after exploring the Binary Number System.

Binary Number System.

By keeping the above simple rules in mind, we will learn the Binary or Base-2 Number System. This Number System has only two digits, 0 and 1 (the highest digit value is 1, i.e., one less than the base value 2) to write any Decimal value in Binary form.

First, let us create a Binary Table, similar to the decimal table, like the decimal number table, so that converting Decimal Numbers to Binary and vice versa is easy.

Note: Please use your Laptop or Tablet to view the Table correctly.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1

In the above table, each digit position value is given in the second row. For example, if you put 1 below the value 1024 and fill the other slots to the right with all zeroes, then the value of Binary Number 10000000000 is 1024 (or 1K or 2¹⁰). If you put 1 replacing the rightmost 0 (10000000001), then the Binary Value becomes 1024+1 = 1025.

Let us try converting the small decimal number 10 to binary.

Method-1:

To convert the decimal number 10, we look at the table and find the highest integer value that can be subtracted from it. In this case, the highest value is 8.
Subtract 8 from 10 and find the result.

10
-8
====
2
====
Put 1 under the slot of value 8 in the binary table.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1

Now, we have 2 as the balance value, and the next binary position value is 4. 4 cannot be subtracted from 2, so put a 0 under the value 4.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1	0

Next, value 2 can be subtracted from the balance value 2 above.
2
-2
=====
0
=====
Put 1 under the value 2 in the binary table. The remainder is zero (0), so put a 0 in the unit position of the binary table. So the result of Decimal Number 10 in binary form is 1010 as given below:

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1	0	1	0

Cross-Checking the Result.

You can quickly cross-check whether the binary number is correct for the decimal number by adding up the values in the second row wherever 1 is there in the third row; 8+2 is 10.

It is not always convenient to build the above table whenever we want to convert a decimal number to binary. Instead, we can do it with a simple calculation. Let us convert the decimal number 10 to binary with this new method.

Method-2:

Divide the decimal number by 2 and record the remainder (always 0 or 1, since this is integer division). Begin constructing the binary value from right to left, placing each remainder in order.

10/2 = Quotient = 5, Remainder=0

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
															0

Each time takes the Quotient Value from the previous division and divides it by 2 again.

5/2 = Quotient = 2, Remainder = 1

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
														1	0

2/2 = Quotient=1, Remainder = 0

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
													0	1	0

1/2 = Quotient = 0, Remainder=1

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1	0	1	0

If you have understood this simple number system so far, try converting some larger numbers than we have practiced. As seen in the binary table above, the highest value listed is 32,768 (2¹⁵). However, you can attempt to convert any decimal number below 65,536 using the same table.

If you need a sample number, then try converting the decimal number 255 to binary.