changing the apperance of a text field to mimic a MAC address

[rmiell]

I have a field in my table "serial number", text, which consists of mainly numbers & letters that make up a MAC address. When it was 1st entered, it was entered as just a string of numbers/letters with out any placeholders. "0004F2C7FCC8" I would like to display the data in a report so that is mimics the layout of a MAC address, that is "00:04:F2:C7:FC:C8".

Any easy ways to handle this?

Access 2007, Windows 8, novice

Thanks in advance



Rick Miell

[JoeM]

How about something like this:

MAC_Add:LEFT([FieldName],2) & ":" & MID([FieldName],3,2) & ":" & MID([FieldName],5,2) & ":" & MID([FieldName],7,2) & ":" & MID([FieldName],9,2) & ":" & MID([FieldName],11,2)

[ssanfu]

Or, using the Format function

Mac_Add: Format([MacAddress],"&&\:&&\:&&\:&&\:&&\:&&")

Saved as..................Formatted
0004F2C7FCC8 .....00:04:F2:C7:FC:C8

[JoeM]

Steve,

Pretty cool! I didn't realize you could use the FORMAT function on Text fields (I have only ever used it on Numeric fields).

[ssanfu]

I use the Format function in queries quite a lot for SSN and phone numbers.
I store just the numbers (text field) and format in the query for reports. See Help for more options...

[rmiell]

Thanks for the replies.

Rick Miell