Calculated Field Formatting

[RJC]

I have a database table with 4 fields. One is calculated from the others - one of which is a date/time field. The calculation works but I can't get the date portion in the format that I want (yyyymmdd) even though the date field is itself formatted this way. See attachments. How do I get the resultant calculated field format to look like this: 000a:wzyx:20130730. The first part works fine but the date portion always comes out mm/dd/yy - so, it looks like this: 000a:wzyx:07/30/2013. Any ideas?

[pbaldy]

I haven't used calculated fields, but can your expression use the Format() function?

A & B & Format(Date, "yyyymmdd")

[ssanfu]

Before heading down the path of calculated fields, you might want to read this:
http://allenbrowne.com/casu-14.html

Be sure to read the last 3 paragraphs.... multiple times.

[RJC]

Before heading down the path of calculated fields, you might want to read this:
http://allenbrowne.com/casu-14.html

Be sure to read the last 3 paragraphs.... multiple times.

Good point; thanks. So I put the calculated field in a query instead of the table. The formatting problem remains the same. And, the Format (Date, "yyyymmdd") suggestion doesn't work either. In the resultant calculated field the date portion always comes out mm/dd/yy.

[RJC]

Good point; thanks. So I put the calculated field in a query instead of the table. The formatting problem remains the same. And, the Format (Date, "yyyymmdd") suggestion doesn't work either. In the resultant calculated field the date portion always comes out mm/dd/yy.

[ketbdnetbp]

RJC -

I might try...

1. Go to the table in design view under ItemDate, where you have yyyymmdd in the format row, change yyyymmdd to Short Date.
2. Go to query, delete ItemDate column then add ItemDate back into the query.
3. Add calculated column to query, like:

xUnique: [Lot #] & ":" & [ItemType] & ":" & DatePart("yyyy",[ItemDate]) & IIf(Len(Month([ItemDate]))=1,"0" & Month([ItemDate]),Month([ItemDate])) & IIf(Len(Day([ItemDate]))=1,"0" & Day([ItemDate]),Day([ItemDate]))

Hope this helps,

Jim

[RJC]

Perfect Jim! This works. Thanks for the help.

[ketbdnetbp]

RJC -

Happy to help. Also, that code assumes that there will never be a null value in each of the fields used in the calculation. If there are null values in any of those fields, the calculation will not work properly. Are nulls a possibility? Please advise.

Lastly, most experts recommend not using special characters in field names; just a heads-up.

All the best,

Jim

[RJC]

Jim,

Thanks for the heads-up; I'll change the field name accordingly. As for null values, there should never be any in the finished product. Since the database is still in development there currently are a few and they don't seem to effect the calculation (except, of course, some pieces are just missing). I haven't restricted the table to prevent nulls yet but I will. Thanks again for the help.

RJC