Question Export 01-JAN-2011 format to a txt file

[andy.russell@suncorp]

I need to export dates in the format dd-mmm-yyyy (eg 01-JAN-2011) as part of a tab delimited txt file. I cant pass it as a string, it must not have text delimiters in the field. Using Access 2007. text file will be picked up by Oracle application.
Thanks
Andy

 

[georgedwilkinson]

I assume you're using a query to get the data to export.

Your query should look something like:

select format(mydate, 'dd-mmm-yyyy'), myothercolumns
from mytable
where myconditions = somecondition

Take a look at http://office.microsoft.com/en-us/access-help/format-property-date-time-data-type-HA001232739.aspx

 

[andy.russell@suncorp]

Hi George - thanks for helping with this one - but no. I'm afriad it's more complex than that - I know all about date formatting IN Access, and I have a table that holds the dates in the format I need (01-JAN-2011)
But the problem occurs when I try to Export FROM Access to a txt delimited. Using a File Specification and checking the sample data view - it all looks good, but the resulting txt file will always show 01/01/2011 00:00:00.

I've put some examples of data in the table and the file specifications used for export as attachments - i hope this helps.

 

[JANR]

Try and explicitly remove the timecomponent of your datefield.

select format([COLOR=#ff0000]Int([/COLOR]mydate[COLOR=red])[/COLOR], 'dd-mmm-yyyy'), myothercolumns
from mytable
where myconditions = somecondition

JR

 

[andy.russell@suncorp]

Hi,
Yes, the stripping of the time component is great - but my biggest issue is still that I want the date in the text file to still look like 01-JAN-2011. It needs to remain a date type, not a string with text delimiteers (which was my other idea)

 

[boblarson]

So you need the text delimiters on the text fields though, correct? But not on the date field.

 

[andy.russell@suncorp]

Hi Bob,
Yes, that's correct.
Cheers
Andy

 

[boblarson]

Well, I think we're going to have to build the text file via code and not using export features. Are you up for that? I can help put it together for you.

 

[andy.russell@suncorp]

Hi Bob,
Yes, you'r validating where I was fearing I'd have to tread. I know how to build it in code, I was just REALLY, REALLY trying to avoid all that work just for one little issue.
Never mind - thanks for your help. Maybe I can help someone else in turn.
Cheers

Andy

 

[boblarson]

Unfortunately while it seems like a little issue, it isn't because it is a non-standard result that is required. The exporting isn't all that robust to be able to handle those type of exceptions (although I wish it were).