Formatting Mac Address in MS Access (1 Viewer)

[Velt]

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.

 

[arnelgp]

you create a function in a Module:

public function fncFixMACAddress(byval pMAC As String) As String
Dim var, v
Dim i As Integer
fncFixMACAddress=pMAC
If Len(pMAC)< 1 Then
    Exit Function
End If
var = Split(pMAC, ":")
For i = 0 To Ubound(var)
    v = Trim$(var(i) & "")
   If Len(v) < 2 Then
       v = "0" & v
   End If
   var(i)=v
Next
fncFixMACAddress=Join(var, ":")
End Function

then add code to your Textbox's AfterUpdate event:

private sub txtMACAddress _AfterUpdate()
Me!txtMACAddress = fncFixMACAddress(Me!txtMACAddress & "")
End Sub

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=85711