concatenating field with specific date format included

[Ravensleach]

Seasons greetings!

I want to create a single field in a form (that can copy and paste into Word) with the following format:

FCD-DID-07-001

Purpose is to generate unique references for letters. The 07 is the year in yy format and the 001 needs to increment by one for each new record.

This is the latest expression: ="FCD-DID-" & [yy] & "00" & [id], producing

FCD-DID-12/26/2006 9:42:14 AM-001

[yy] is from a date field in the underlying table but I can't get it to format as yy despite setting this format in the table and query. [id] is an autonumber field, I would prefer it to update to 010 and 101 as it gets to that point rather than have to re-do the expression.

Can you help? Sorry if this has been covered before but the forum search doesn't seem to be working.

 

[Remou]

You can use:

Format([yy],"yy")

and

Format([ID],"000")

That is:
="FCD-DID-" & Format([yy],"yy") & Format([ID],"000")

 

[Ravensleach]

Thank you Remou you are a star, works exactly how I want it