Mac Address Formatting in MS Access

[Velt]

Hi Everyone, New to MS Access. Had a question regarding formatting of a MAC Address in one of my Access forms. There is a field I have set up using an input mask aa:aa:aa:aa:aa:aa;;a where users can manually enter a 48 bit hexidecimal address. e.x 11:44:5E:33:53:AF.




However sometimes there are missing values that occur in this data entry e.x 0:A:B:11:22:C (happens from time to time) but I would like be able to automatically fill the missing values with leading zeros to be like 00:0A:0B:11:22:0C.
I realize that this may not be possible through just MS Access Input masks, but all of the VBA codes and after updates code building I have been looking at so far have not lead me to the desired format.


Thanks for your time and appreciate any help in this!


I tried Format(fieldname, "00000000") code, but it just fills from the left-hand side instead of between the colons. e.x 00:00:0A:B1:12:2C instead of the desired 00:0A:0B:11:22:0C.

[ArviLaanemets]

Write an UDF, which
1. Processes the string (SourceString), searching for 1st occurrence of character ":" (position x), and calculates 1st hexadecimal into ResultString as Right("00" & MID(SourceString, 1, x-1),2) & ":"
2. Recalculates SourceString as MID(SourceString, x+1, 250)
3. Repeats 1.-2. until no ":" is found, every time adding next 2-character hexadecimal and ":" to ResultString;
4. Adds Right("00" & SourceString,2) to ResultString;
5. Returns ResultString.

Use the UDF to update original data (e.g. in Update Query, or in BeforeUpdate event of form control).

NB! The function described above can't cope with cases, where some ":" was omitted, or where more than 2 characters were in a section of original hexadecimal!

[Welshgasman]

Crossposted https://www.access-programmers.co.uk...access.322639/

[June7]

An alternative to Mid() is Split().

Function FixMac(strS As String)
Dim strMac As String, x As Integer
For x = 0 To 5
    strMac = strMac & Right("00" & Split(strS, ":")(x), 2) & ":"
Next
FixMac = Left(strMac, 17)
End Function

Assumes there will always be 6 parts separated by colon.

I have used textbox AfterUpdate event for this sort of data fix.

Could change data entry so users are not typing this string all into one textbox.