Date format to csv (1 Viewer)

What I am doing is importing data that other software creates and manipulating it in access code making a table “Rough Order” with about 20 fields to be exported at a later date in a comma delimited format for easy viewing later because it has been manipulated and it may or may not be finished.


Ultimately it will be exported to a table called “Standard IG Order” with about 120 fields - mostly empty but all required.


The date comes from a form in an unbound field with the default value Date().


I actually do not care what the date looks like in my database – AT ALL. The only thing that matters about the date is that when exported it is month followed by day and then year in the format mm/dd/yyyy. Meaning that Fridays date would be “05/18/2018”. The “0” in front of the 5 is required.


The format code that I used was:
POD = Format(Forms![Create Order 2]![OrderDate], "mm/dd/yyyy")


The export code that is used later is:
DoCmd.TransferText acExportDelim, , "Standard IG Order", "C:\Cardinal Order\Exports ready for Cardinal" & PartNumber3 & ".csv", True

What is the data source of the report? (Open the report in design view, and in the properties of the report, go to the data tab and look at the Recordsource).


If the data source is your table “Standard IG Order” and you are showing the PO date based on a date field in your table, it does not matter a tinkers what is in your variable POD, or what is on your unbound text box on your form. The data is coming from the date field in your table and displayed in the report in your default date format.


Use a query to give the date in the format you want. (While there are other ways, the query is by far the easiest.)

You are exporting directly from the table and the date field you are having problems with is a date datatype.

Either :
- specify the format on the table field (check if there is one set already), or
- change the field datatype to text so that it stores exactly what is entered from the form, or
- as already said, create a query with the field formatted as you want and export the query instead of the table.

@Kip,

When you use a date field you are not storing the format in the table, you are simply storing a number. Your table itself does not store "05/18/18" it stores 43238. FORMAT will turn this number into something you can read, but you need to be clear on WHERE you are requesting the data to be formatted.

As you are using your table to do the export, what is the format for the field in your table definition?

The date is formatted in the Format line in the design view of the table. I have chosen "Short Date" but the time is still being displayed "0:00:00".

So this is not working at all. And if it was working I'm guessing that June 2nd would show up like this:

"6/2/2018"

rather than the required:

"06/02/2018".

Have you tried changing

Short Date

Click to expand...

to be

mm/dd/yyyy

Click to expand...

and then export?

I believe I did try that and my leading 0s were dropped.
So 05/18/2018 became 5/18/2018.

But now it's moot. I've changed the dates to text.

Thank you to everyone for trying to help!