Results 1 to 6 of 6
  1. #1
    Mclaren is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164

    Increase by 1

    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".

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    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.

  3. #3
    Mclaren is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164
    Cool, now to modify this as the "Q" in the field is also subject to change. But thanks for the info.

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    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

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    How if I have text sequence like "abcdef1234"? number of alphabets or number of digits are unknown.

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Increase search speed
    By kjuelfs in forum Queries
    Replies: 3
    Last Post: 07-12-2010, 05:11 AM
  2. Replies: 0
    Last Post: 03-23-2009, 01:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums