Convert MAC address in MS Access

Sometime ago I wrote an article about updating MAC Addresses before (Convert MAC address in MS Excel or OO Spreadsheet) so the subject is not new :-). But this time I’ll try to explain how to Convert MAC addresses in MS Access. The standard format for printing MAC addresses in human-friendly form is six groups of two hexadecimal digits, separated by hyphens (-) or colons (:), in transmission order, e.g. 01-23-45-67-89-ab, 01:23:45:67:89:ab. In this article I’ll give you examples of how to add, remove or change the separator for MAC addresses in MS Access.

The whole concept of updating records in MS Access is based upon creating queries. So you’ll have to create some queries depending on what you want to achieve. In the examples below I have an MS Access database that holds a table called “Table1”. Within that table I have a Column called “MacAddress”.

Adding a seperator, changing 0123456789ab into 01-23-45-67-89-ab.

UPDATE Table1 SET Table1.MacAddress = MID([Table1].[MacAddress],1,2) & ‘-‘ & MID([Table1].[MacAddress],3,2) & ‘-‘ & MID([Table1].[MacAddress],5,2) & ‘-‘ & MID([Table1].[MacAddress],7,2) & ‘-‘ & MID([Table1].[MacAddress],9,2) & ‘-‘ & MID([Table1].[MacAddress],11,2) WHERE LEN([Table1].[MacAddress])=12;

Adding a seperator, changing 0123456789ab into 01:23:45:67:89:ab.

UPDATE Table1 SET Table1.MacAddress = MID([Table1].[MacAddress],1,2) & ‘:’ & MID([Table1].[MacAddress],3,2) & ‘:’ & MID([Table1].[MacAddress],5,2) & ‘:’ & MID([Table1].[MacAddress],7,2) & ‘:’ & MID([Table1].[MacAddress],9,2) & ‘:’ & MID([Table1].[MacAddress],11,2) WHERE LEN([Table1].[MacAddress])=12;

Removing a separator, changing 01-23-45-67-89-ab into 0123456789ab.

UPDATE Table1 SET Table1.MacAddress = REPLACE(Table1.MacAddress,’:’,”) WHERE instr(Table1.MacAddress,”:”)=true;

Removing a separator, changing 01:23:45:67:89:ab into 0123456789ab.

UPDATE Table1 SET Table1.MacAddress = REPLACE(Table1.MacAddress,’:’,”) WHERE instr(Table1.MacAddress,”:”)=true;

Changing a separator, changing 01-23-45-67-89-ab into 01:23:45:67:89:ab.

UPDATE Table1 SET Table1.MacAddress = REPLACE(Table1.MacAddress,’-‘,’:’) WHERE instr(Table1.MacAddress,”-“)=true;

Changing a separator, changing 01:23:45:67:89:ab into 01-23-45-67-89-ab.

UPDATE Table1 SET Table1.MacAddress = REPLACE(Table1.MacAddress,’:’,’-‘) ; WHERE instr(Table1.MacAddress,”:”)=true;

And that’s all folks!!!

Suggestions for improving this article are welcome, please let me know and drop me a line.