Arg! Need formatting help during concatenate!

[jvanhyfte]

Hi Everyone! I've always found this to be the best source for help when I get stuck, but now can't find any resources to get out of a jam.

I have a table where I am trying to generate a work order number that is formed by concatenating several fields. One of the fields is a Work Order generations date formatted as yyyymmdd. When I concatenate this into my work order number, this date reverts to the "standard" format as mm/dd/yyyy. I have tried to change the format property within the concatenated number, but to no avail.

Is there a way to maintain the yyyymmdd format after concatenating?

Thanks for all your help!

 

[etaf]

Assuming thr date is in A1 try using
=TEXT(A1,"YYYYMMDD")
so you can then concatenate

using any other cells
=B1 & TEXT(A1,"YYYYMMDD") & C1 & D1

 

[jvanhyfte]

Thanks @etaf, I need to clarify...

I am using Access 2013 and have a field called "Work Order Number" that I am generating from a concatenation of several other user input fields, namely, "Job Number"&"-"&"Entry Date"&"-"&"Series" (all numbers). I have the use enter "Entry Date" in the form of yyyymmdd and want to use this within the "Work Order" number.

The issue I have is the concatenated number returns the form 3333-12/01/2014-02, when what I would like is 3333-20141201-02 in the field. I can't find out how to format the date, even with the field formatting.

Thanks for your help!
john

 

[revver]

It seems you are using an Input Mask on the Entry Date field. This does not change the way the date is stored only the way it is entered.

There are 3 things to consider about your data -
How it is entered
How it is stored
How it is displayed

Your concatenation formula is accessing the stored value not the entered value.
Therefore you need to use the Format() function in your formula.