Job number formatted with 2 digit year and leading zeros

[cver22]

Hey there,

I've searched everywhere and can't seem to sort this out. I'm looking to have our job numbers auto numbered with a two-digit year out front:

YY######

The first job number created would be (as this is 2017 right now):

17000001

Next year we'd be happy to continue with the same numbering so let's assume we've done 87 jobs and this would be our first one in 2018:



18000088

I've tried creating it all using autonumbering but it doesn't like taking the date. So instead I've just left autonumbering on its own, created a date Field Name and then tried to calculate them together in "Project_Number" field

This is the code I've managed to concoct, however, the data output deletes a bunch of zeros and adds spaces for no reason:

Str(Right(Year([Date_Created]),2)) & Right(Str("000000") & Str([ID]),6)

The example above would come out to:

18 0 88

Any thoughts would be greatly appreciated.

Cheers!

[Bob Fitz]

In a query you could use a calculated field. Something like:

Result:Right([YourDateField],2) & Format([YourAutoNumField],"000000")

[ssanfu]

You shouldn't have the STR() function around the string of zeros. Try:

Str(Right(Year([Date_Created]), 2)) & Right("000000" & CStr([ID]), 6)

I create a sub with two variables to mimic your fields... the string "17000088" was returned.

<snip> created a date Field Name and then tried to calculate them together in "Project_Number" field

Did you really create a date field to store a string????

[cver22]

You shouldn't have the STR() function around the string of zeros. Try:

Str(Right(Year([Date_Created]), 2)) & Right("000000" & CStr([ID]), 6)

I create a sub with two variables to mimic your fields... the string "17000088" was returned.




Did you really create a date field to store a string????

Taking the Str off it worked beautifully, thanks!

Yes, I need both the date and the job number for different purposes and couldn't figure out how to make it work. I've done a bit of Java, but I'm very new to access.