Help with formatting of calculated field

[chegra26]

I have three fields in my Table.
Field 1 is today's date formatted as yyyymmdd
Field 2 is Case - Auto NUmber formatted 000
Field 3 is Case # - Calculated field - [today's date] & "-" & [case]

I am trying to create a case number that would result in the date coming out as 20170530 and the number as 001 so my case # looks like this 20170530-001. However the result I get is 5/30/2017-1.
Can someone help me with the correct formatting?

Thank you

 

[Joe4]

Formatting of a value is not carried over into calculations, just the underlying value.
You need to use the FORMAT function in your calculated field, i.e.

FORMAT([today's date],"yyyymmdd") & "-" & FORMAT([case],"000")

 

[chegra26]

Said that it could not be used in a calculated Column

 

[Joe4]

Are you trying to do the calculation at the Table level?
If so, I would recommend NOT doing that.

Access recently introduced calculations at the Table level, but they are frought with limitations and issues. It is highly recommended that you NOT do your calculations there.
Do them in a Query instead.

 

[chegra26]

I am doing it as a calculated field in the table

 

[Joe4]

I am doing it as a calculated field in the table

As I said, don't. They have limitations and problems. It isn't "best practice" and no other database program (SQL, Oracle, etc) allows for this either.

Do it in a Query. There should be no drawback to doing it in a Query instead. Pretty much anything that you would use a Table for you can use a Query instead for.
Can you provide any reason why you think it needs to be done at the Table level as opposed to the Query level?

Read this here: http://allenbrowne.com/casu-14.html
Allen Browne is one of the most foremost experts on Microsoft Access.

 

[chegra26]

no I can do it in a query, but figured there was a way to get the correct format using a calculated field in a table.
Thanks so much!

Michelle