Text To Date wrong format

[PCX]

Im exporting a query to a text delimited file using "TransferText acExportDelim" and each time I export, I get the time along with the date. The formating for the table and the query are short date. The data is an input from the user and has the short date mask w/o any time.

How can I keep it from exporting the time also?

Thanks
Lee

 

[DougP]

I know of one way but it requires a lot of VBA code
You are actually making your own export function which then you have complete control over.

Public Function DoItMyWay()
‘To call this function x = DoItMyWay
‘Note these are my fields and table name changes yours to match

'Open the recordset
Dim db As Database, Rst As Recordset
Dim StringToWrite As String
Set db = CurrentDb
Set Rst = db.OpenRecordset("Employee&quot
'Open the ASCII text file
Open "C:\Myfile.txt" For Output As #1
Do While Not Rst.EOF 'loop through the recordset
' Get each field and write it out to the text file
StringToWrite = Format(Rst!Date, "mm/dd/yy&quot
StringToWrite = StringToWrite & "," & Rst!EmpID
StringToWrite = StringToWrite & "," & Rst!Name
' add as many of these lines as you need ^

Print #1, Left(StringToWrite, Len(StringToWrite) - 3)
Rst.MoveNext
Loop
Close #1
' close recordset
rst.close
db.close
End Function


results of my c:\Myfile.txt
03/04/00,1,F
05/06/99,2,Sa
DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.
Or visit my WEB site

http://www.barcodeone.com

 

[richardii]

Create this function (and then build it into your query):

Function TruncDate(cField As Date)
TruncDate = Mid(cField, 1, 10)
End Function

 

[richardii]

In fact you only need this function:

Function TruncDate(cField As Date)
TruncDate = cField
End Function

Not sure I really know/understand why this works!