Help with Incrementing a Custome Formatted Unique Number

[posterman]

Help with Incrementing a Custom Formatted Unique Number

I have a database with a table "tblCAPARMain" in which there are numerous fields some of which are:

BaseNum
FormatNum
CAPAR#
CAPARDate

I also have a form "frmCAPARMain" in which I want to create a unique number for each record to be stored in the "CAPAR#" field in "tblCAPARMain". The format of the number is ”C09-001”, where the “C” is a constant, the “09” is the last two digits of the current year when the record was entered, and the “001” is a unique consecutive number.

I am using the following code to generate the number “After Update” of a combo box.

If IsNull(Me.[BaseNum]) Then
Me.[BaseNum] = Format(Nz(DMax("[BaseNum]", "[tblCAPARMain]", "[CAPARDate]='" & Year(Date) & "'"), 0) + 1)
End If
Me.[CAPAR#] = [FormatNum] & [CAPARDate] & "-" & Format([BaseNum], "000")
Me.Refresh

Where "CAPARDate" has the "Default Value" in "tblCAPARMain" set to “=Right(Date(),4)”

With this code the unique number increments correctly, and everything works fine except that I get the four digit year. i.e. "C2009-001". If I change the "CAPARDate" "Default Value" to “=Right(Date(),2)”, then the format comes out right as "C09-001", but the unique number will not increment.

I have also tried the following code with the "CAPARDate" default value set as“=Right(Date(),4)”

If IsNull(Me.[BaseNum]) Then
Me.[BaseNum] = Format(Nz(DMax("[BaseNum]", "[tblCAPARMain]", "[CAPARDate]='" & Year(Date) & "'"), 0) + 1)
End If
Me.[CAPAR#] = [FormatNum] & Format([CAPARDate], “yy”) & "-" & Format([BaseNum], "000")
Me.Refresh

This returns “C05-001” no matter what the current year is set to, but does increment.

I also tried the above with the "CAPARDate" default value set as “=Right(Date(),2)”, this returns “C00-001” for all years and does not increment.

Can anyone help me get the correct format “C09-001” and have it increment correctly?

Thank you in advance for your help.


Paul

 

[RainLover]

Re: Help with Incrementing a Custom Formatted Unique Number

In CAPAR# get rid of the Pound sign. Just my opinion.

Please bear in mind that no matter what the default is or the format dates are always stored as numbers. Like 1.0 or 34567.987

So use DatePart or DateSerial to solve your problem. Right() is not good.