How do I convert date yyddmm to mmddyyyy

[conerlysduck]

Can anyone help me... I need to convert a date field that is in format yyddmm to format mmddyyyy. Thanks.

[TheSmileyCoder]

In the format property of the table OR the query used for your form/Report OR the control used to display your field you can set how you want it formatted. Simply write mmddyyyy in the format field.

[conerlysduck]

Thank you for your response. I tried that but it does not change the format in my table. The date in the table shows 113010, I need it to show 10302011. The format needs to be reversed and the year needs to show 2011. Any suggestions?

[Rabbit]

Are you sure it's a date data type? Because dates don't show as yyddmm unless there's already a format on there. I suspect it's actually a string data type and you have to convert it to a date first. Just because it looks like a date doesn't mean it's a date data type.

[conerlysduck]

Yes, I'm sure it's a date. It's an invoice date in format yyddmm. I imported a file into Access and now I need to change the date format to mmddyyyy. Maybe using DateSerial, but I don't know how to tell it what I want. Can you help?

[Rabbit]

Then all you have to do is go into the property where it says yyddmm and change it to say mmddyyyy. Someone had to put yyddmm in there for it to show up like that. If no one did, then it most likely means the data type is not a date. To be sure, go into the table design and look at what it says in Data Type.

[colintis]

I guess the data type in that table field is in text, as Conerlysduck said the data is imported from file.

If that's the case, it will be a bit complicate to convert the date formats. First from string to date, change date format, and convert it back to string. Here's a concept of update query might help

UPDATE [Table name] 
SET [date field name] = CStr(Format(Format(CDate([date field name]), "20yyddmm"), "mmddyyyy"))

Not sure if it fully works this way, you need to try it out with something similar as well.

[conerlysduck]

Thank you for all your help. I got it to work using this:

Right([NBC Cancellation File]![INVOICE RECEIPT DT],2) & Mid([NBC Cancellation File]![INVOICE RECEIPT DT],3,2) & "20" & Left([NBC Cancellation File]![INVOICE RECEIPT DT],2)