Many Fields into one Field

[batnose]

I need to make one big identifcation number called Cert# based on data in four fields in a table. Four fields are: Event, Year, Cat, DID

First question - I need to say if Event = Live then enter L, if Event = Red then enter R, if Event = anything else then enter S

Year is fine as it is an auto entry and always is 10, as in 2010.
Cat is fine as it is a single digit

Second question - DID is an auto number in the table. I need it to be three digits in the Cert#. I need to add some leading zeros if they are needed.

An example of what the resulting Cert# should look like is L104001.

I don't even know where to begin with this, if it should be in a query or what. If you have to use code, please be very specific because I don't even know where it would go. Any and all suggestions are appreciated.

Thank you!

[dcrake]

You can display this in a query using the following code

Cert:IIF(Event="Live","L",IIF(Event="Red","R","S") & Year & Cat & Format(DID,"000")

However you need to change the field name Year as this is an Access reserved word. I suspect the Event may be one too.

David

[batnose]

Thank you so much for your quick reply.

An error message told me it's missing a parenthesis so it should read like this:
Cert:IIF(Event="Live","L",IIF(Event="Red","R","S") ) & Year & Cat & Format(DID,"000")

No biggie! This is fabulous. Thanks for the tip about changing the field names. I will have to learn more about these IIF statements now.

Thanks, again!