Date format when exporting tables to csv files

[silvia]

Hello!

When I run the following code:

Private Sub CmdExportFiles_Click()
Dim dbs As Database, tb As TableDef
Set dbs = CurrentDb

For Each tb In dbs.TableDefs
If Left(tb.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , _
tb.Name, "S:\shared\sad\tests\" & tb.Name & ".csv", True
End If
On Error Resume Next
Next tb
MsgBox "The tables have all been exported"

End Sub

Everything works fine apart from the dates that are converting in the following format:

17/11/97 00:00:00

I would like to get "dd/mm/yyyy" format.

Any ideas?

Thank you

Silvia

 

[silvia]

Hello again!

To make clearer my question:

All the original date formats in the Access database to be exported have the following imput mask: "dd/mm/yyyy"

Does anyone know why the conversion into csv files, sets up the dates as "dd/mm/yy 00:00:00"

I hope this helps to get any ideas!

Thanks!!

Silvia

 

[BukHix]

silvia,

The input mask does not actualy change how the data is set in the table. All it does is affect how it is shown in a form or a report. You should be able to change the properties of the field in the table to get it in the format that you want or if you are using a query you could edit the SQL to format the field with something like this:

Format(TableName.DateFieldName,"mm/dd/yy")

To set it at the table level you just need to add this to the format properties:

mm/dd/yy

[This message has been edited by BukHix (edited 04-15-2002).]

 

[silvia]

Thanks BukHix!

I think the problem is due to the exporting process into .csv files. The date formats in all the tables in the Access database were initially set up as "dd/mm/yyyy", but the exported files don't keep that format and instead the dates are kept "dd/mm/yy 00:00:00"

Thanks anyway!

Silvia

 

[BukHix]

Slyvia you should be able to accomplish what you want. I was in the exact same postition as you are a while back and was able to overcome it first by using an update query and then later by using a query on top of my table, altering the format from there and then exporting the query to a CSV instead of the table.

Here is a quick example of how it can be done in a query:

SELECT BidtekRejoin.EmployeeNumber, 
BidtekRejoin.JobDate, 
BidtekRejoin.JobNumber, 
BidtekRejoin.PhaseCode, 
Format(BidtekRejoin.EarnCode,"0") AS Expr1, 
BidtekRejoin.Mon, 
Format(BidtekRejoin.GreaterValue,"#.##") AS Expr2, 
Format(BidtekRejoin.Total,"#.##") AS Expr3
FROM BidtekRejoin
ORDER BY BidtekRejoin.JobDate;

Notice the formats in there, in my case I am doing dollars and not dates but the concept is the same. This gets exported to a Unix database in exactly the format that the application requires.

Post back if you need more help with this.




[This message has been edited by BukHix (edited 04-16-2002).]