Date format to csv

[kip]

This should be simple but I'm stuck. I have a table that is exported to a csv with one of these:

DoCmd.TransferText acExportDelim, , "Standard IG Order", "C:\Cardinal Order\Exports ready for Cardinal" + PartNumber3 + ".csv", True

The problem is that one of the fields is a date and must be in the following format:

05/15/2018
(MM,DD,YYYY)

That's where I am stuck I can't seem to get that format in my table.

I'm getting this:
5/15/18 0:00

How do I make this happen?

Thanks
Kip

 

[Cronk]

Use the format function in a query which is then exported

format(YourDateField,"mm/dd/yyy")

 

[isladogs]

"C:\Cardinal Order\Exports ready for Cardinal" + PartNumber3 + ".csv"

I assume PartNumber3 isn't the date field you are referring to as that won't work
Recommend you use the default '&' instead of '+' for concatenation.
Both may work here, but '+' handles nulls in a different way which you might not want

 

[kip]

Thanks Cronk but that did not work. I’m still getting “5/16/2018 0:00:00” instead of “05/16/2018”. I’m starting to think that this is an issue with my computer settings and that I should be passing this along in text format instead of date.

Any Thoughts?

I’m guessing that extracting a day, month, and year out of a date is not very hard and that all I need are three functions.

And thank you ridders, I have changed that and will keep that in mind in the future.

 

[isladogs]

Cronk omitted a 'y' by mistake. It should be:

Format(YourDateField,"mm/dd/yyy[COLOR="Red"]y[/COLOR]")

 

[kip]

Actually I caught the missing "y" but I was entering "mm/dd/yyyy" in the format line in the properties of the query.

I won't have access to Access until tomorrow. Can I use that in the Visual Basic code that loads that data into the field?

And it does look right in the table that has been populated in Access. It's not until after the csv is populated that it looks wrong (in the csv file).

 

[Cronk]

Missing 'y' => DUH!!

 

[isladogs]

Actually I caught the missing "y" but I was entering "mm/dd/yyyy" in the format line in the properties of the query.

I won't have access to Access until tomorrow. Can I use that in the Visual Basic code that loads that data into the field?

And it does look right in the table that has been populated in Access. It's not until after the csv is populated that it looks wrong (in the csv file).

Yes you can. In fact, if using dates in WHERE clauses, you need to use that format.

 

[CJ_London]

if using dates in WHERE clauses, you need to use that format.

not that it saves any effort but I recently learned in another thread the format yyyy-mm-dd also works

 

[isladogs]

not that it saves any effort but I recently learned in another thread the format yyyy-mm-dd also works

We probably read the same thread as I also only found that out a few months ago.
The yyyy-mm-dd format has the advantage of correctly sorting files by date

 

[Minty]

The yyyy-mm-dd format also works seamlessly with SQL server, so has a lot of advantages in removing ambiguity.

I changed my version of Allen Browns SQLDate() function to return that format by default.

 

[arnelgp]

Edit your table structure and select the appropriate format

 

[kip]

Thanks guys however still not working. I'm still getting the time 0:00:00 with the date.

I am getting closer though.

The date comes from a Form where it looks fine.

Then in the code I assign the fields after changing them to strings like so:

Dim POD As String
Dim SD As String
POD = Forms![Create Order 2]![OrderDate]
SD = Forms![Create Order 2]![DueDate]
Tab2.PODate = POD
Tab2.ShipDate = SD

At some point after the table has been populated I create the csv file with this:

DoCmd.TransferText acExportDelim, , "Standard IG Order", "C:\Cardinal Order\Exports ready for Cardinal" & PartNumber3 & ".csv", True

I am now getting:

,”5/17/2018”,

You’d think that would be good but the company that is receiving the data must have:

,”05/17/2018”,

I’m guessing that the 1st through the 9th of every month will be bad too.

Any ideas?

 

[CJ_London]

The date comes from a Form where it looks fine.

exactly the point - it is formatted to look like what you want it to look like. Dates are stored as numbers, what you see is the number formatted - might be dd/mm/yyyy, might be mm/dd/yyyy, might be any other format you care to think of.

So when you use that date anywhere else, unless you format it, it will use the standard format of dd/mm/yyyy hh:mm:ss based on your windows settings. Otherwise it will use the US standard of mm/dd/yyyy which is what you are getting.

ergo you need to tell access the format you want to use. So try

POD = format(Forms![Create Order 2]![OrderDate],"dd/mm/yyyy")

alternatively dim POD as a date, not a string

 

[kip]

Thanks CJ_London,

That worked as well as changing it to a text. However I'm still getting this:

,”5/17/2018”,

But wanting this:

,”05/17/2018”,

The company that I'm sending it to said that the leading 0 is important.

 

[CJ_London]

what code are you using? if you used mine exactly as provided you would not get what you appear to be getting

you assign POD to Tab2.PODate - where does that get used? what is it?

 

[kip]

This is what I'm using:

POD = Format(Forms![Create Order 2]![OrderDate], "mm/dd/yyyy")

 

[CJ_London]

OK so that gives the format for 17th May as 05/17/2018

do you see the difference between what I provided - "dd/mm/yyyy"

and what you have used - "mm/dd/yyyy"

Sorry - getting mixed up with the dates

your format is correct for what you want, so after you assign it to Tab2.PODate - where does that get used? what is it?

 

[Cronk]

Tab2.PODate is probably a text box bound to the table on which the report is probably based. If the field in the table is a Date/Time, then formatting achieves nothing for the report.


I did suggest in #2 that the formatting be put into a query on which the report is based.

 

[Mark_]

Is "Standard IG Order" a table or a query?

If it is a table, make a query that contains the fields you need to export, including using an expression to format the field that is not exporting properly.

If it IS a query, open the query to see if the field looks proper prior to export.


Trying to track down where this is going wrong before giving a lot of advise (other than to remove space/special characters from table/field names).