用于向具有特殊条件的字段添加前导零的查询或 VBA 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25393861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query or VBA Function for adding leading zeroes to a field with special conditions
提问by Bill
I have a macro I am trying to turn into a VBA Function or Query for adding leading zeros to a field.
我有一个宏，我试图将其转换为 VBA 函数或查询，以便向字段添加前导零。
For my circumstances, their needs to be 4 numeric digits plus any alphabetic characters that follow so a simple format query doesn't do the trick.
对于我的情况，它们需要是 4 个数字加上任何后面的字母字符，因此简单的格式查询不起作用。
The macro I have uses Evaluate and =Match but I am unsure how this could be achieved in Access.
我使用的宏使用了 Evaluate 和 =Match，但我不确定如何在 Access 中实现这一点。
Sub Change_Number_Format_In_String()
Dim iFirstLetterPosition As Integer
Dim sTemp As String
For Each c In Range("A2:A100")
If Len(c) > 0 Then
iFirstLetterPosition = Evaluate("=MATCH(TRUE,NOT(ISNUMBER(1*MID(" & c.Address & ",ROW(:),1))),0)")
sTemp = Left(c, iFirstLetterPosition - 1) 'get the leading numbers
sTemp = Format(sTemp, "0000") 'format the numbers
sTemp = sTemp & Mid(c, iFirstLetterPosition, Len(c)) 'concatenate the remainder of the string
c.NumberFormat = "@"
c.Value = sTemp
End If
Next
End Sub
In my database the field in need of formatting is called PIDNUMBER
在我的数据库中，需要格式化的字段被称为 PIDNUMBER
EDIT:
编辑：
To expand on why FORMAT doesnt work in my situation. Some PIDNUMBERS have an alpha character after the number that should not be counted when determining how many zeroes to add.
扩展为什么 FORMAT 在我的情况下不起作用。某些 PIDNUMBERS 在确定要添加多少个零时不应计算的数字后面有一个字母字符。
In example:
例如：
12 should become 0012
12 应该变成 0012
12A should become 0012A
12A 应该变成 0012A
When using format, it counts the letters as part of the string, so 12A would become 012A instead of 0012A as intended.
使用格式时，它将字母作为字符串的一部分计算在内，因此 12A 将变成 012A 而不是预期的 0012A。
回答by jforbes
You could try:
你可以试试：
Public Function customFormat(ByRef sString As String) As String
customFormat = Right("0000" & sString, 4 + Len(sString) - Len(CStr(Val(sString))))
End Function
回答by PatricK
Try utilize this function, if you only want this to be available in VBA, put Privatein front of the Function:
尝试使用此功能，如果您只想在 VBA 中使用此功能，请将其放在FunctionPrivate前面：
Function ZeroPadFront(oIn As Variant) As String
Dim zeros As Long, sOut As String
sOut = CStr(oIn)
zeros = 4 - Len(sOut)
If zeros < 0 Then zeros = 0
ZeroPadFront = String(zeros, "0") & sOut
End Function
回答by Chel
The Val()function converts a string to a number, and strips off any trailing non-numeric characters. We can use it to figure out how many digits the numeric portion has:
该Val()函数将字符串转换为数字，并去除任何尾随的非数字字符。我们可以用它来计算数字部分有多少位数：
Function PadAlpha$(s$)
Dim NumDigs As Long
NumDigs = Len(CStr(Val(s)))
If NumDigs < 4 Then
PadAlpha = String$(4 - NumDigs, "0") & s
Else
PadAlpha = s
End If
End Function
? padalpha("12")
> 0012
? padalpha("12a")
> 0012a
回答by bp_
Bill,
账单，
See if this will work. It seems like a function would better suit you.
看看这是否有效。似乎一个函数更适合你。
Function NewPIDNumber(varPIDNumber As Variant) As String
Dim lngLoop As Long
Dim strChar As String
For lngLoop = 1 to Len(varPIDNumber)
strChar = Mid(varPIDNumber, lngLoop, 1)
If IsNumeric(strChar) Then
NewPIDNumber = NewPIDNumber & strChar
Else
Exit For
End If
Next lngLoop
If Len(NewPIDNumber) > 4 Then
MsgBox "Bad Data Maaaaan...." & Chr(13) & Chr(13) & "The record = " & varPIDNumber
Exit Function
End If
Do Until Len(NewPIDNumber) = 4
NewPIDNumber = "0" & NewPIDNumber
Loop
End Function
Data Result
012a 0012
12a 0012
12 0012
85 0085
85adfe 0085
1002a 1002
1002 1002
