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 are yet another method to write Binary Values into shorter forms than Octal Numbers. This Number System has the Number 16 as its Base. As per the basic rules, which we are following to devise new Number Systems, the Base-16 Number System has its Numeral values from 0 to 15 (i.e., one less than the base value 16). Like the decimal number system, the hexadecimal number system also has digit values from 0 to 9. Since there are no single digit values for 10 to 15 the alphabets A to F are used.

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 made groups of 3 binary digits of binary value 011,111,111 (equal to 255 decimal) to find the Octal Number &O377. For Hexadecimal Number, we must take groups 4 binary digits (Binary 1111 = (1+2+4+8) = 15, the maximum value of a hexadecimal digit) and add the values of binary digits to find the hexadecimal digit.

Let us find the Hexadecimal value of 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 Debug Window and press Enter Key to covert 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 Decimal Value to a maximum of 99,999,999 to 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 when 0 is 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 xx.0 Object Library File, and put a checkmark 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 Binary Number System.

2. Learn Binary Number System-2.

Please go through 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:

Source: www.wikipedia.org

Later, programming language like Assembly Language was 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 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 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 contents of register B to A then the last six bits must be altered as 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 see 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 conversion, we could take the highest integer value from the table above and subtract it from 255. But here we cannot do that. By looking at the table above, we can see that 512 is more than 255, so we must see if the next number 64 goes how many times in 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 into 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 binary values of each group and write the Octal value.

011 = 1+2 = 3

111 = 1+2+4 = 7

Result: = 377 Octal.

You may try converting a few more numbers of your own to understand this Number System better. First, convert some Decimal Numbers to Binary, group the Binary Digits in sets of 3 bits, and add up the values of the group as if they are the first three bits of the binary number.

Since Octal Numbers are written with digits 0 to 7, it looks like decimal numbers to both man and machine. There must be a way to distinguish Octal Numbers from decimal numbers. Therefore, it is written with the prefix characters &O. When Octal numbers are written alone or used in expressions it is written with the prefix characters &O (the letter O, not case sensitive) or &0 (digit zero), like &O0377 or &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 Binary Number System

Last week https://www.msaccesstips.com/2010/09/octal-numbering-system.htmlwe have gone through the fundamentals of the Binary Number System and learned how to convert decimal number 10 to binary and saw different methods also.

I hope you have tried converting the sample number 255, that I have given at the end of last week's 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 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 get the value of the unit 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 and take the remainder and write 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 of Decimal Number 255.

You can experiment with this 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 carries 2 to the next position
next 1+1 carry = 2(binary 10), put 0 and carries 2 to the next position
Next 1+1 carry = 2(binary 10), put 0 and carries 2 to the next position
Next 1+1+1 carry = 3(binary 11), put 1 and carries 2 to the next position
Next 1+1+1 carry = 3(binary 11), put 1 and carries 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, now 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 successive 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 afraid of learning Computer's own language, the Binary Number System? I suppose you are not! If you are a Computer Programmer or intend to become one, then I suggest you better learn it. No, you are not going to write programs in Binary Numbers, but sooner or later you will come across references to binary, Octal, and Hexadecimal Numbers. If you don't like surprises then better try getting some general understanding of this Number System.

I think that is enough to keep you interested. It is not that hard to learn. In fact, if you look at a few simple rules that go with the Decimal Number System then you can devise any number system that you like if others understand it and accept it.

Few Simple Rules that Govern the Decimal Number System.

Let us look at a few simple rules that go with the Decimal Number System, that 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: Any new Number System that you create using a particular value as a Base, the number of digits in that system will be equal to the base value and the highest value of the digit in that system will be one less than the base value. We will see this when we learn about some other number systems used in Computers (Octal & Hexadecimal Systems).
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¹ + 1 x 0 or 10 + 1 x 0.

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

=======

Going by the above rules we can devise any number system. For example, if we devise a Number System with Base-8 then this will have the digits 0 to 7; the highest single-digit value is one less than the base value 8 and this number system doesn't have digits 8 or 9. In fact, this Number System has been already there in the computer world and we will learn it after going through 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 (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:

Find the highest integer value in the table above that can be subtracted from decimal number 10? 8 is the highest value that can be taken from the above table.
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 balance 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. There is nothing left to calculate the binary value so put a 0 in the unit position of the binary table. So the result of Decimal Number 10 in binary form is 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 to the decimal number by adding up the values in the second row wherever one is there in the third row; 8+2 is 10.

It is not always convenient to build the above table every time when 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 get the remainder value (always integer division and the remainder is 1 or 0) and start building the binary value from right to left with the remainder value.

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 then try converting some numbers bigger than what we have tried. As you can see from the binary table above the highest value we have plotted on the table above is 32768 (2¹⁵) but you can try converting any value below the decimal Number 65536 in the above table.

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