New Record Issue (1 Viewer)

[Haynesey]

Hi,

I am trying to create a procedure that creates a unique reference number in the format of 'year/number'. For example, 07/0001.

This worked fine until I found out that the year is not based on the calendar year but the financial year (April 1st to March 31st).

I have changed my code to read:

If Me.CaseNumber.OldValue = "" Or IsNull(Me.CaseNumber.OldValue) Then
Me.CaseNumber = IIf(Format(Date, "mm") > 3, Format(Date, "yy") & "/" & Format(Nz( _
DMax("Mid([CaseNumber], 4)", "tblWelfareAdaptations", "[CaseNumber] Like '" & _
Format(Date, "yy") & "*'"), 0) + 1, "0000"), Format(DateAdd("yyyy", -1, Date), "yy") & "/" & Format(Nz( _
DMax("Mid([CaseNumber], 4)", "tblWelfareAdaptations", "[CaseNumber] Like '" & _
Format(Date, "yy") & "*'"), 0) + 1, "0000"))
End If

However, this works when the year is equal or greater than April, but when it is before April, the year displays correctly but the number does not move on to the next number, it just stays the same.

Please help!

Thanks in advance

Lee

 

[Dennisk]

Hint ,
never use the inline if statement in code only in queries.
It is inefficient and verrrrry difficult to read.

use if then else instead