AnsweredAssumed Answered

Tricky Inventory Number System

Question asked by AndrewJudd on Jun 12, 2015
Latest reply on Jul 12, 2015 by philmodjunk

Title

Tricky Inventory Number System

Post

I'm working on a database in which the client would like to have quite a complicated numbering system (reference number for each record). So far, I've been able to achieve the numbering system by using the below -

Left (SURNAME;5) & DATE & "_" & Inventory Number

Inventory Number is just set to auto enter a serial number which is pulled into the calculation.

However.. the client would like the numbers to run in accordance with the date. To describe, what they would like -  examples are as follows: 

SMITH2014_001
SMITH2014_002
SMITH2014_003
...
SMITH2015_001
SMITH2015_002
SMITH2015_003
...

INSTEAD OF:

SMITH2014_001
SMITH2014_002
SMITH2014_003
...
SMITH2015_004
SMITH2015_005
SMITH2015_006
...

Does anyone have ideas on how this can be achieved? What I think I need is a calculation for the inventory number which understands what the date is and what the last number for say, 2014, was.

THANK YOU

 

Outcomes