Concatenate Date and AutoNumber

[Mactavishman]

I need to make a requisition number that is the combination of the date entered (Field Name = Date_Entered in format 99/99/00) and the Auto Number for the record, or some other sequential number that I can define.

Format needs to be YYMMDDnn where YY is the Year, MM is the Month, DD is the Date and NN is the sequential number (no, there won't be any more that 99 records a day!).

This needs to be filled into a field called "Req_Number" in my table.

Ideas? Impossible?

 

[lhite]

Try:

=CStr(Format([DateEntered],"yymmdd&quot) & (CStr(Format([AutoID],"00&quot))

-Larry

 

[KenReay]

Hi

You are the third person to ask virtually the same question in two days!!

Something like:

Public Function NextNo(strPrefix As String) As Long
Dim Db As Database
Dim Rs As Recordset
'
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("SELECT lngAuto FROM MyTable WHERE strPrefix ='" & strPrefix & "' ORDER BY lngAuto DESC;&quot
If Rs.RecordCount = 0 Then
NextNo = strPrefix & "01"
Else
NextNo = strPrefix & Format(Rs!lngAuto + 1,"00&quot
End If
Rs.Close
Set Rs = Nothing
Set Db = Nothing

End Function

Notes

The above is not tested

If you are using ADO, rather than DAO, you will have to substitute appropriate ADO commands

You need to put in error trapping, particulalarly if your application is multiuser, to trap possibility of two users asking for next number at same time, and getting a duplicate Number

You need to substitute your own table and column names

If you call above so:

strReqNumber = NextNumber(Format(Date(),"yymmdd&quot)
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK