Formatting "Now" date as a Letter

[swingdummy]

Hello,


As part of our business, we have a custom "Purchase Order Number" consisting of a letter followed by a short date consisting of the month and day. It's simple: our current year is defined by a letter (example: the year 2011 was defined as the letter "M"; this year is defined as the letter "N"; and so on). In addition, if we have several purchase orders on the same day we have a suffix to the purchase order number consisting of "-1", "-2", and so on.
Therefore, if we were to create a new purchase order on today's date it would be "N0418-1". If we were creating our fourth purchase order today it would be "N0418-4".
Is there a way to create an auto-number based on the above information?
I would appreciate the genius of anyone who can help.

[maximus]

This is what I have done:

I have a simple table Table1 with the Following Fields:

1. ID (Text Field PK)
2. T_Date (Date/Time) This is to record the trasaction Date
3. Details

The Code below is attached to a form bound to Table1.

What Does the Code Do?

Generates the ID automatically in the format mentioned e.g. N0418-1, N0418-2

How does it work?

I have used a simple Select Case to assign Alpha value to the current year. I use a simple recordset to see if I have any entries for the current date this is done using:

if rst.EOF and rst.Bof

if no record is found the then it becomes the first entry e.g. if for today 04/19/2012 first entry N0419-01. If record is found I use do while loop to loop through the records and find the number of records and so my new record will be i+1. e.g. I have 5 Entries for 04/19/2012 then the next entry will be N0419-6


I have attached this code to a command button on my form please use your discretion.

Private Sub Command9_Click()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Dim intCurYear As Integer
Dim strYearCode As String

i = 0

intCurrentYear = Year(Date)

Select Case intCurrentYear
    Case Is = 2011
    strYearCode = "M"
    Case Is = 2012
    strYearCode = "N"
End Select

MsgBox strYearCode

strSQL = "Select * From Table1 Where T_Date=#" & Me.T_Date & "#;"
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF And rst.BOF Then
    Me.ID = strYearCode & IIf(Month(Date) <= 9, "0" & Month(Date), Month(Date)) & IIf(Day(Date) <= 9, "0" & Day(Date), Day(Date)) & "-" & 1
Else
    Do While Not rst.EOF
        i = i + 1
        rst.MoveNext
        Loop
End If
rst.Close
Set rst = Nothing

Me.ID = strYearCode & IIf(Month(Date) <= 9, "0" & Month(Date), Month(Date)) & IIf(Day(Date) <= 9, "0" & Day(Date), Day(Date)) & "-" & i + 1
End Sub

let me know if you have problems.

[maximus]

If Transaction Date is not recorded than this code may be used to generate the ID. The Code is basically similar in Logic only difference being I have used Like to filter my records for my recordset.

Dim rst As DAO.Recordset
Dim strSQL As String
Dim intCurYear As Integer
Dim strYearCode As String
Dim strAlphaCode As String

i = 0
intCurrentYear = Year(Date)

Select Case intCurrentYear
    Case Is = 2011
    strYearCode = "M"
    Case Is = 2012
    strYearCode = "N"
End Select

strAlphaCode = strYearCode & IIf(Month(Date) <= 9, "0" & Month(Date), Month(Date)) & IIf(Day(Date) <= 9, "0" & Day(Date), Day(Date))

strSQL = "Select * From Table1 Where ID Like '" & strAlphaCode & "'+ '*'"
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF And rst.BOF Then
    Me.ID = strAlphaCode & "-1"
Else
    Do While Not rst.EOF
        i = i + 1
        rst.MoveNext
        Loop
End If
rst.Close
Set rst = Nothing
MsgBox i
Me.ID = strAlphaCode & "-" & i + 1
End Sub