Need Same Results in Report

[Sondra]

I have a form with the code described below. I need to have a report show
the codes with the same format; i.e., 05D-0001, 05D-0002, etc. When the year
2006 comes along, it will start over 06D-0001, 06D-0002, etc. I'm trying to
format my report to show the correct format, but with no luck. The stored
number in the database looks like D-50001, D-50002, etc. Any help would be
appreciated on how to format so that the end result is 05D-0001, 05D-0002,
etc.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim mTable As String, mField As String, mYearPart As Long, mNextNumber
As Long
'number is returned as 6 characters: YY####
'where YY is the last 2 digits of the year
'#### is the next number for that year
'D is the DSCR Code
'format code to display this number should be "00D-0000"
mTable = "DSCR"
mField = "DSCRNumber"
mYearPart = Right(CStr(Year(Me.DSCRYear)), 2) * 10000
mNextNumber = Nz(DMax(mField, mTable, mField & ">=" & mYearPart), 0)

If mNextNumber = 0 Then
mNextNumber = mYearPart
End If

mNextNumber = mNextNumber + 1
Me.DSCRNumber = mNextNumber

End Sub

 

[John Spencer]

You need a calculation such as
Format(Val(Mid(TheField,3,1)),"00") & Left(TheField,1) & "-" &
Mid(TheField,4)

You might need to add a test to that to make sure you have good data in the
field
IIF(TheField Like "D-#####",Format(Val(Mid(TheField,3,1)),"00") &
Left(TheField,1) & "-" & Mid(TheField,4),TheField)

You can either use that calculation as the control's source or as a
calculated column in your query.