URGENT!! Erroneous date format in table export

[MattBegg]

Looking for some help please.

I am exporting several tables to delimited text files to be uploaded and importeed into an online database.

I have formated the date field for the tables as dd/mm/yyyy due to the fact that the in built formats all have a 2 digit year. Visually the data in the tables looks fine but in the file export the date field looks like:-

"a",b,c,dd/mm/yyyy 00:00,x,y,z

AS you can see an a time entry is exported aswell, is there any way of stopping this. Also in the above b,c,x,y,z are in number format and a is a text field, is there any way of stoppping the quotation marks surrounding it.

Both of the above will cause major problems if imported into the online database which is Omnis in origin.

Look forward to any replies

Regards

Matt

matt@begg-uk.co.uk

 

[hennep]

try to export a query. In the query you can use the format function

select format( [field], "dd/mm/yyyy" ) from tbl;

 

[richardii]

Hello Matt you cannot escape!!!! Build a function like this:

Function Con_Date(dDate)
Con_Date = dDate
End Function

Then build that into a query passing the date to it. The resulting query will have the date formatted as you want. Seems weird, but it also seems to work.

You can stop the quotation marks by saving the export specification, and using it (see the way a macro does this -then convert it to vb). It's on the Advanced button on the Export Wizard.

 

[MattBegg]

Ack
Tried both of the above but still getting a time entry of 00:00:00 in the export file !!! Regards

Matt

matt@begg-uk.co.uk

 

[richardii]

Give this a go:

Function Con_Date(dDate As Date)
Con_Date = Mid(dDate, 1, 10)
End Function

It may be something to do with the system date, because 'it works here'.

 

[PaulF]

can't you create a query with all of the fields and set the format for the date field to "dd/mm/yyyy", and then export the query, instead of the table?

PaulF

 

[hennep]

are you sure there is a time in the date field. When you use the date() function to supply a value to the field the time is always "00:00", the now() function returns a date including time

 

[hennep]

> "a",b,c,dd/mm/yyyy 00:00,x,y,z

did you really use this format string ?

or did you use: "a",b,c,dd/mm/yyyy hh:mm,x,y,z