# LEARN MS-ACCESS TIPS AND TRICKS

Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

### Continuation of earlier Articles:

Hexadecimal Numbers are yet another method to write Binary Values into shorter form than Octal Numbers.  This Numbering System has Number 16 as its Base.  As per the basic rules, which we are following to devise new Numbering Systems, the Base-16 Numbering System have its digit values from 0 to 15 (i.e. one less than the base value 16).  Like decimal numbering system, hexadecimal numbering System also have 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

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

You can type ? &H1F4 in Debug Window of VBA Editing screen to covert and print its Decimal Value.

### MS-Access Functions

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

Try the following Examples, by typing them in the Debug Window to convert 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 form.  The list of functions are given below:

1. DEC2BIN()
2. DEC2OCT()
3. DEC2HEX()
4. BIN2DEC()
5. BIN2OCT()
6. BIN2HEX()
7. OCT2DEC()
8. OCT2BIN()
9. OCT2HEX()
10. HEX2DEC()
11. HEX2OCT()
12. HEX2BIN()

You can convert Decimal Value 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 positive).

Technorati Tags:
Share:

### Continued from Last Week's Post.

This is the continuation of earlier Articles:

Please go through the earlier Articles before continuing. . .

We will bring forward the result of the Decimal 255 to Binary conversion 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:

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 numbering system was devised to write binary numbers in short form.

### Octal Numbering System.

This numbering system has number 8 as its base and is known as Octal Numbering System.  Based on the general rule that we have learned, Octal Numbering System have digits 0 to 7 (one less than the base value 8) to write numerical quantities. There is no 8 or 9 digits in this numbering system. Octal Numbering System has been devised to write Binary instructions into short 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, next three bits (010) represents CPU Register B and next three bits (111) represents CPU Register A, the 8 bit binary instruction says add the contents of register A to register B.  If the instruction must be changed to (ADD A,B) add 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 027 to 072 rather than Binary 00111010.

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

 85 84 83 82 81 80 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 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 next number 64 goes how many times 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)

 85 84 83 82 81 80 32768 4096 512 64 8 1 3

63/8 = Quotient = 7, Remainder=7

 85 84 83 82 81 80 32768 4096 512 64 8 1 3 7

7 is not divisible by 8, hence 7 goes into the Units position

 85 84 83 82 81 80 32768 4096 512 64 8 1 3 7 7

#### Method-2:

255/8 = Quotient = 31, Remainder=7

 85 84 83 82 81 80 32768 4096 512 64 8 1 7

31/8 = Quotient = 3, Remainder=7

 85 84 83 82 81 80 32768 4096 512 64 8 1 7 7

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

 85 84 83 82 81 80 32768 4096 512 64 8 1 3 7 7

### Writing Binary to Octal Short Form

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

The Decimal Number 255 when converted into Binary we get 11111111. To convert it into Octal Numbers set 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

111 = 1+2+4 = 7

Result: = 377 Octal.

You may try converting few more numbers of your own to understand this Numbering System better.  First, convert some Decimal Numbers to Binary and 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 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 Base-16 (Hexadecimal) Numbering System.

Technorati Tags:
Share:

### Continued from Last Week's Post

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

Last week we have gone through the fundamentals of Binary Numbering System and learned how to convert decimal number 10 to binary and seen 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:

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.
2.

 255 -128 =127

3. Write Binary digit 1 at the 128 number position underneath the Binary Table.
4.  215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 32,768 16,384 8,192 4,096 2,048 1,024 512 256 128 64 32 16 8 4 2 1 1
5. The next highest integer number from the Binary Table that goes into 127 is 64.
6.  127 -64 =63
 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 32,768 16,384 8,192 4,096 2,048 1,024 512 256 128 64 32 16 8 4 2 1 1 1
7. Repeat this method till you get the value for unit position.
 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 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 for the binary digit) position to arrive at the value you were trying to convert into Binary.

#### Method-2:

1. Divide the Decimal Number by 2 and take the reminder value and write at the unit position in the Binary Table.

255/2 = Quotient = 127, Remainder = 1

2. Next step, take the Quotient Value (127) of 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 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 this with bigger Decimal Values or write some unknown Binary Values with random 1s and 0s and try converting it 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.

 11101110 238 +1110111 119 101100101 357

Start adding from the rightmost digits:

1.   0+1 = 1
2.   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)
3.   next 1+1+1 carry = 3(binary 11) , put 1 and carry 2 to the next position
4.   next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position
5.   Next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position
6.   Next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position
7.   Next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position
8.   Next 1+1 carry = 2(binary 10), put 0 and put 1 to the next position.

Example : Subtraction

 11001110 206 -1111111 127 1001111 79
1.   0-1 cannot be done, so take 2 from the next position, now 2-1 = 1, but the next position on the first line become 0.
2.   0-1 cannot be done, so take 2 from the next position, now 2-1 = 1, next position on the first line become 0.
3.   0-1 cannot be done, so take 2 from the next position, now 2-1 = 1, now next 3 positions become 0.
4.   Take Value from the 8 value position and move forward to the 4 position and to the 2 value position, 2-1 = 1
5.   1-1 = 0
6.   1-1 = 0
7.    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../-

Share:

### Introduction

Are you afraid of learning Computer's own language, the Binary Numbering 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 Numbering System.

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

### Few Simple Rules that Govern the Decimal Numbering System.

Let us look at few simple rules that goes with the Decimal Numbering System, that we are already familiar with.

### The Decimal Numbering System.

1. Decimal Numbering System's Base value is 10 or is known as Base-10 Numbering System.
2. Base-10 Numbering 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 10.
3. Any value more than 9 is expressed in multiples of 10.

Note: Keep this simple rule in mind: Any new numbering System that you create using a particular value as 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 some other numbering systems used in Computers (Octal & Hexadecimal Systems).

4. Decimal value 10 cannot be written with a single digit, instead we put a zero at the unit's position and write 1 in the 10th position. So, decimal Value 10 = 101 x 1 + 0 or 10 x 1 + 0.

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

 106 105 104 103 102 101 100 1,000,000 100,000 10,000 1,000 100 10 1 2 5 5

2 x 102   OR   2 x 10 x 10   OR  2 x 100  = 200

5 x 101    OR  5 x 10                              =  50

5 x 100    OR  5 x 1                                 =  5

=======

255

=======

Going by the above rules we can devise any numbering system.  For example, if we devise a Numbering System with Base-8 then this will have digits 0 to 7; the highest single digit value is one less than the base value 8 and this numbering system don't have digit 8 or 9.  In fact this Numbering System is already there in the computer world and we will learn it after going through the Binary Numbering System.

### Binary Numbering System.

By keeping the above simple rules in mind we will learn Binary or Base-2 Numbering System.  This Numbering System have 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 shown above so that converting Decimal Number to Binary and vice versa is easy.

 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 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 210). 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:

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.
2. Subtract 8 from 10 and find the result.

10

-8

====

2

====

3. Put 1 under the slot of value 8 in the binary table.
4.

 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 32,768 16,384 8,192 4,096 2,048 1,024 512 256 128 64 32 16 8 4 2 1 1

5. 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.
6.

 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 32,768 16,384 8,192 4,096 2,048 1,024 512 256 128 64 32 16 8 4 2 1 1 0

7. Next value 2 can be subtracted from the balance value 2 above.

2

-2

=====

0

=====

8. Put 1 under the value 2 in the binary table. There is nothing left to calculate binary value so put a 0 in the unit position of binary table. So the result of Decimal Number 10 in binary form is as given below:

 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 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 decimal number 10 to binary with this new method.

#### Method-2:

1. 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.
2.

10/2 = Quotient = 5, Remainder=0

 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 32,768 16,384 8,192 4,096 2,048 1,024 512 256 128 64 32 16 8 4 2 1 0

3. Each time take the Quotient Value from the previous division and divide it by 2 again.
4.

5/2 = Quotient = 2, Remainder = 1

 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 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

 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 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

 215 214 213 212 211 210 29 28 27 26 25 24 23 22 21 20 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 understood this simple numbering 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 (215) but you can try converting any value below the decimal Number 65536 with the above table.

If you need a sample number then try converting decimal number 255 into binary.

Share:

## Translate   Subscribe in a reader Your email address:

Delivered by FeedBurner

## Featured Post

### Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...