Need help to derive a unique number leveraging a particular date format + Record ID#

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'd like to have a unique record ID that includes elements of dates and record ID. The dates should help identify loans visually.

Desired output:(YY)(MM)(6 digit unique #)

Example: 1601001000   (from Jan 2016 + 6 digit number)

Notes:
*Month needs to be 2 digits (01 rather than 1)
*Digits following YYMM to be 6

Used this formula but it does not maintain the number of digit format desired:
Right(ToText(Year(Today())), 2) & " - " &  Right(ToText(Month(Today())), 2) & (1001 + [Record ID#])

Produces:1642433       (16)(4)(2433)
Desired: 1604002433  (16)(04)(002433)

Any help would be greatly appreciated!

Photo of Kevin

Kevin

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Hi Kevin,

Try this:



Right(ToText(Year(Today())),2)


&"-"&right(ToText(Month(Today())+100),2)

&"-"&right(totext(1000000+[Record ID#]),6)
or if you don't like the dashes in there, do this:

Right(ToText(Year(Today())),2)
&right(ToText(Month(Today())+100),2)
&right(totext(1000000+[Record ID#]),6)
Photo of Kevin

Kevin

  • 0 Points
Very clever approach. Thank you, Eric!