Remember, you stated that the table field was of type Long and it was required. If you attempt to use this function in a query calculated field and the field that the function references contains any Null values it will fail as it is written. Your requirement dictates that the output of the function must be a string; e.g. 123456789 becomes 123.456.789 thus cannot be a number. To test function, you can call it like insertdot(12345678)
To use it in a query, you'd have a calculated field like Dotted: InsertDot([Nums]) where [Nums] is the name of the field that contains your Long values.
Code:
Public Function InsertDot(lngIn As Long) As String
Dim n As Integer, x As Double, y As Integer
x = Len(CStr(lngIn)) / 3 'convert to string to get its length; divide by 3 to count 'sections'
'if x has decimlas, subtracting the integer of x will leave the decimal, thus not be zero
If x - Int(x) = 0 Then
y = 1
Else
'if there is a decimal portion, round up by adding 1 to the integer value (e.g. 2.6666667 becomes 3)
x = Int(x) + 1
y = 1
End If
For n = 1 To x
'Mid function begins at y (1 on first pass) and gets 3 characters. We append '.' to it and assign to function's return value
InsertDot = InsertDot & Mid(lngIn, y, 3) & "."
y = y + 3 'increment y so as to begin at (e.g. 1+3=4) position in string for next use of Mid function
Next n
If Right(InsertDot, 1) = "." Then InsertDot = Left(InsertDot, Len(InsertDot) - 1) 'assign all but the ending '.' to the function's return value
Debug.Print InsertDot 'comment out this line when ready to use the function in a query
End Function
At this point I realize I could shorten the first IF block (which I will probably do in my own version) but I'm in the middle of a posting now, and if it ain't broke....
EDIT: don't know if you realize that such a function needs to be in a standard module for it to work from a query. Also, you'd need to add a textbox to your form to show the results of the calculated query field. You can keep (or not) the form control that displays the original table field number. If you keep it there, you can also hide it or not. Depends on whether or not you need the original field on the form for other purposes.
Input mask on a form
Windows 10 
Access 2003
Reply With Quote
