Generate Number from other fields

[HBG6]

I have an Access database and need to generate an Invoice number in the following way:
"RPI"("YYMM&quot000 (Autoincrease)= for example:"RPI0306000" for the first invoice generated on June 2003. RPI0306001 (for the second, etc, etc...)
ok, let me explain what this means: RPI should be default (I know how to do that) the problem begins when I want to enter the Year and the Month. I don't want the system to determine these based on the current date. I want these to be determined based on the date I INPUT. How? there is a field called DATE on the database. I want to use the data inputed there and then add a "000" for the first invoice that month "001" for the second "002" for thethird, so on and so forth..
Does anyone know how can I do this?

Please help me if you do.
Thanks.

HBG6

 

[FaneDuru]

I don't work in Access....
In Excel I would solve the problem with the next code:

Sub InvoiceCounting()
Dim Dat, Found As Long, Rng As Range, C As Range, InvoiceNr As String
  Dat = Cells(2, 1).Value         'Date which is taken from Access table
  Root = "RPI" & Format(Dat, "yymm") 'The common root from the monthly invoice
  'We look for the root if already exists
  Set Rng = Range(Cells(2, 2), Cells(1, 2).End(xlDown))
  For Each C In Rng
    If C.Value = Root Then
         C.Offset(0, 1) = C.Offset(0, 1) + 1
         Found = C.Offset(0, 1).Value
    End If
  Next
  'If the root is a new one it is inserted
  If IsEmpty(Found) Then
     'for the first record to avoid error for "Cells(1, 2).End(xlDown).Row + 1"
     If Application.WorksheetFunction.CountA(Range("B:B")) = 1 Then
        Range("B2") = Root
        Range("C2") = 0
        Found = 0
     Else
        Cells(Cells(1, 2).End(xlDown).Row + 1, 2) = Root
        Cells(Cells(1, 2).End(xlDown).Row, 2).Offset(0, 1) = 0
        Found = 0
     End If
  End If
  InvoiceNr = "RPI" & Format(Dat, "yymm") & Format(Found, "000")
  MsgBox InvoiceNr
End Sub

I memorised the monthly root in column B:B, the counter for each root in column C:C.
Try to do the same in Access.....

I hope this helpls....