[AccessD] Some code help please X posted

Is the Memo_Key a primary key?  Are you looking for something like this?  I hope this helps.

Thanks,
Jeremy
 
 
 
Sub Seqno_Increment()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strYr As String, strSeq As String, _
        strSQL As String
    
    strYr = Left(Year(Date), 1) & Right(Year(Date), 1)
    
    strSQL = "SELECT tblSeqno.* " _
            & "FROM " _
                & "tblSeqno " _
            & "WHERE " _
                & "(((tblSeqno.[Memo_Key]) Like " _
                & Chr(34) & strYr & "-*" & Chr(34) & ")) " _
            & "ORDER BY " _
                & "tblSeqno.[Memo_Key] DESC;"
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    If rs.RecordCount > 0 Then
        strSeq = Format(Val(Right(rs("[Memo_Key]"), 2)) + 1, "00")
    Else
        strSeq = "01"
    End If
        
    strSeq = strYr & "-" & strSeq
    
    Debug.Print strSeq
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub
 
 
 
 
 


Joe Hecht <jmhecht at earthlink.net> wrote:


Silly me. The only work I can get lately is rebuilding someone else's
database; It is tragic for it is the only way right now I can keep my one
bedroom apartment.



I need code that will in the field MEMO_KEY



Grab the current year. 2005 

Get the first and last digit of the year 25

Do a count to get the existing highest incident x

Number 

Add 1 to it
x+1

End up with a value like 25-01


25-02



And so on.





Thanks





Joe

Los Angeles, CA



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com