I have a field (non rimary, but unique) it has text i.e.: "Q123", i need to make this increase by 1 form the last known highest number. i.e. "Q123" to "Q124".
I have a field (non rimary, but unique) it has text i.e.: "Q123", i need to make this increase by 1 form the last known highest number. i.e. "Q123" to "Q124".
I have a table1 with a Field jjj that will have strings in like Q123. I have to increment the number by one.
Assuming that you want to do this in a form I have attached it to the oncurrent event of the form
I have basically seperated the numeric part and then assigning it to the variable intMyNumber which is integer. Then i add 1 to it and the assign the value back to the field jjj in the form adding "Q" to it.
Private Sub Form_Current()
Dim strMyString As String
Dim strMyNum As String
Dim intMyNumber As Integer
if Me.NewRecord then
strMyString = IIf(IsNull(DLast("[jjj]", "Table1")), 0, DLast("[jjj]", "Table1"))
strMyNum = IIf(strMyString = "0", "0", Right$(strMyString, Len(strMyString) - 1))
intMyNumber = strMyNum
intMyNumber = intMyNumber + 1
Me.jjj = "Q" & intMyNumber
End if
End Sub
this code will ensure a alpha numerical number incremented by 1 added to jjj on the form.
please mark the thread solved if this solves your problem.
Cool, now to modify this as the "Q" in the field is also subject to change. But thanks for the info.
This code will increment your Number and as well as the alphabet. When alphabet reaches Z the next number will have alphabet A.
Private Sub Form_Current()
Dim strMyString As String
Dim strMyNum As String
Dim intMyNumber As Integer
Dim myAlphaAssign As String
Dim intAlphaNumber As Integer
Dim myalpha(25) As String
Dim intAlpha As Integer
Dim intCounter As Integer
Dim strMyAlphabet As String
myalpha(0) = "A"
myalpha(1) = "B"
myalpha(2) = "C"
myalpha(3) = "D"
myalpha(4) = "E"
myalpha(5) = "F"
myalpha(6) = "G"
myalpha(7) = "H"
myalpha(8) = "I"
myalpha(9) = "J"
myalpha(10) = "K"
myalpha(11) = "L"
myalpha(12) = "M"
myalpha(13) = "N"
myalpha(14) = "O"
myalpha(15) = "P"
myalpha(16) = "Q"
myalpha(17) = "R"
myalpha(18) = "S"
myalpha(19) = "T"
myalpha(20) = "U"
myalpha(21) = "V"
myalpha(22) = "W"
myalpha(23) = "X"
myalpha(24) = "Y"
myalpha(25) = "Z"
If Me.NewRecord Then
strMyString = IIf(IsNull(DLast("[jjj]", "Table1")), 0, DLast("[jjj]", "Table1"))
strMyNum = IIf(strMyString = "0", "0", Right$(strMyString, Len(strMyString) - 1))
intMyNumber = strMyNum
intMyNumber = intMyNumber + 1
'Me.jjj = "Q" & intMyNumber
intCounter = 0
For intCounter = 0 To 26
If myalpha(intCounter) = Left$(strMyString, 1) Then
Exit For
End If
Next intCounter
intAlpha = intCounter
If intAlpha = 25 Then
intAlpha = 0
Else
intAlpha = intAlpha + 1
End If
Me.jjj = myalpha(intAlpha) & intMyNumber
End If
End Sub
How if I have text sequence like "abcdef1234"? number of alphabets or number of digits are unknown.
well is that a requirement or a mere satisfaction of your curiosity. what every may be the case work ur grey cells a little. U will get the answer.