Results 1 to 2 of 2

Thread: Who Can Help

  1. #1
    Join Date
    Oct 2010
    Posts
    7

    Unanswered: Who Can Help

    How do I:

    ‘This code increments a number starting with one
    Me.VMD.Value = Nz(Me.VMD.Value, 0) + 1

    ‘This code changes the year in my table after Sep. 30th of each year
    StartDate = 2012

    If Date > DateSerial(StartDate, 10, 0) Then YearDate = Year(Date) + 1
    If Date < DateSerial(Year(Date), 10, 1) Then YearDate = Year(Date)

    How is the best way to capture each record in my Access table and reset the number to 1 when the year changes
    Example: My table would like this
    Record 2012-001
    Record 2012-002
    Record 2012-002 and so on until the next year change


    Record 2013-001 New year change
    Record 2013-002 until the next year change


    Record 2014-001

    Thanks,

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Maybe something like this
    Code:
        If Date < DateSerial(Year(Date), 10, 1) Then
            YearDate = Year(Date)
        Else
            YearDate = Year(Date) + 1
        End If
    
        Dim YearMonth As String
        
        YearMonth = YearDate & "-" & Format(Month(DateAdd("m", -9, Date)), "000")
        MsgBox YearMonth
    ??

    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •