Query to Pipe Deliminated TXT file

[cstimart]

Access 2007:
I have a query that I am exporting to a pipe deliminated txt file. The problem I am having is that one field is a "short date", but the export converts/adds the time format.

The field holds "09/23/2016", but the export will show as "9/23/2016 00:00.0". I've tried changing the field to Text format, but still get the same result.....my export needs to be "mm/dd/yyyy", retaining any leading zeros.

Any ideas?

 

[Joe4]

In your query, use the FORMAT function on this field (in a Calculated field), which will convert it to Text, and it will be formatted exactly as you want, i.e.
FORMAT([myDateFieldName],"mm/dd/yyyy")

 

[cstimart]

In your query, use the FORMAT function on this field (in a Calculated field), which will convert it to Text, and it will be formatted exactly as you want, i.e.
FORMAT([myDateFieldName],"mm/dd/yyyy")

I tried that, too. No dice.

 

[Joe4]

I tried that, too. No dice.

Then I fear that you may have done something wrong. I have use this method quite a lot with success.
Note that I am talking about creating a Calculated field using the FORMAT function, not using the formatting property under each field.

Can you post the SQL code of your query, and the let us know the name of the field in question.

 

[cstimart]

After further research, I was able to resolve my issue by pushing my query/recordset to Excel and using a variation of the following.

Dim fso As Object
Dim TS As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set TS = fso.CreateTextFile("C:\test.txt")
 
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To LR
        strTemp = "|"
        For x = 1 To 24
            strTemp = strTemp & Cells(i, x) & "|"
        Next x
        strTemp = Temp & vbCrLf
        TS.Write Temp
    Next i
 TS.Close