Coverting Date Format to Text

[MonarTech]

Hi
I have 2003 Access Dbase from which some of the fields must be exported as comma deliminated txt and email to a repository.

I need the end user to see DOB, date format dd/mm/yyyy (15/06/1959) but it must be exported as ,15061959,Town,State....etc

Now I have formatted Table, Form & Query Fields (dd/mm/yyyy) and then carefully chosen the text export options...Removing the / date deliminator etc and saved the export format & kind. Included the correct path etc in to a macro and used Notepad.exe to display.

When it displays... as follows...15061959 0:00:00, IT ADDS A TIME?????

Short term workaround I have end user entering 2x IE 01/01/0001 & 01010001 as seperate text field. If I format date as ddmmyyyy then it displays this way for end user of course and not acceptable for other reports

Is there some way I get around this...or code/query/function I can use to convert for export purposes??????

 

[ghudson]

The table field must be formatted as text to store just the mmddyyyy for the date.

You can format the date using the Format() function when exporting or just displaying a date.

Format([YourDateField],"mmddyyyy")

 

[MonarTech]

Thanks ghudson... I thought I had been Formating correctly? but I will try using this.
When exporting where do I apply/attach the Format()?

 

[MonarTech]

I applied Properties - Format: ddmmyyyy to the field in query design. Query displays perfectly. Go to export query records to txt file. go to avanced tab of export spec's remove / as date separator, check allow leading zeros, check allow 4 digit date format etc etc. Exports as ,ddmmyyyy 0:00:00, Hmm removed time separator : then displays as ,ddmmyyyy 00000, ?????????????

 

[Bat17]

apply it to the field itself, then the field will become text not a fomatted number.
So using a field caled MyDate you get
MyDate2:Format([MyDate],"mmddyyyy")

Hope that is clearer

Peter

 

[Pat Hartman]

Maybe I can clear up the confusion. The Format Property only affects how a field is displayed when you view the query/form/report. The Format() function actually modifies the value in the recordsource so this is the technique you need to use when you are exporting data. You actually want the field modified to become a text string in the query's resulting recordset as that is what is being exported.

 

[MonarTech]

Can't thank you enough...it's working

 

[MonarTech]

Can't thank, you enough between all of the replies the cloud of confusion and much frustration has now evaporated...Applied the formatting in the SQL statement....tested the export....Hey presto....don't even have to tweak the export properties...YE HA