Exported date has time in it

[Bonnie]

Hi there everyone. Using A02 in XP. Have a DB that needs to export a comma
delimited file. Tried using export specs, macro transfertext, code
DoCmd.Transfer etc. When I export to .xls, dates are fine (01/01/2006) but if
I export to .csv or .txt I keep getting 01/01/2006 0:00. UGH!!! I need to
have a .csv file to avoid any user intervention before it is imported into
another system from it's downloaded file path.

My export query data looks great. It's just landing ugly. Must I export to
..xls to avoid this? Any help or advice would be very much appreciated.

Thanks in advance for your time.

 

[OfficeDev18 via AccessMonster.com]

Hi, Bonnie,

Been there; tore hair out because of it. The workaround is to use a query to
do the actual export, instead of directly from the table. For that one field,
have the query say something like DateOnly: Format(DateFieldName,
"mm/dd/yyyy") in order to truncate the time component. It worked for me.

Sam

 

[Bonnie]

Hi Sam. Thanks for the reply. I am using a query. I have one date working
great but the other 2 are fighting me. Here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised![SSN])= '000000000', "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy![SSN], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised![SSN]) AS Social, "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.FName, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.LName, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Gender, "
strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.DOB, ""mm/dd/yyyy"") as BirthDate,"
strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.DOH, ""mm/dd/yyyy"") as HireDate,"
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Hours, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.Comp, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.DefAmt, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.ExclComp, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Sec125, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.StatusCode, "
strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.StatusDate, ""mm/dd/yyyy"") AS DateStatus "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy RIGHT
Join "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised ON "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.ID = "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.ID "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

MsgBox strSQL
Debug.Print strSQL

'the name of your query goes in here
Set qryTest = dbsCurrent.QueryDefs("qCensus1ExportRevised")

qryTest.SQL = strSQL
'Debug.Print strSQL
'other code

Set dbsCurrent = Nothing
Set qryTest = Nothing
'--------------------------------

'Uncomment the MsgBox line or add a Debug.Print strSQL line.
'Debug.Print strSQL
'It should give you the correct SQL for the value selected in the
"RunThisOne" combo box.

'Export the data
DoCmd.RunMacro ("mCensus1.ExportRevised")
MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

StatusDate is working well as 01/01/2005 but DOB and DOH still give me
01/01/2005 0:00. It must be my quotes or commas. I mess them up easily.

Any suggestions?

 

[OfficeDev18 via AccessMonster.com]

Hi, Bonnie,

Try changing

strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.DOB, ""mm/dd/yyyy"") as BirthDate,"
strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.DOH, ""mm/dd/yyyy"") as HireDate,"

to

strSQL = strSQL & "'" & Format([Forms]![fCensus1Conversion]![RunThisOne] &
Revised.DOB, "mm/dd/yyyy") & "'" as BirthDate,"
strSQL = strSQL & "'" & Format([Forms]![fCensus1Conversion]![RunThisOne] &
Revised.DOH, "mm/dd/yyyy") & "'" as HireDate,"

For a brief word of explanation, the Format() function is not an SQL function
but a string function, so the results should be strictly between single
quotes. That's how I understand it anyway. I think it's correct, but you
might analyze the string when you display it in your MsgBox statement to see
if it contains the correct info. If it doesn't you can always play with it.

Hope I helped,

Sam

Hi Sam. Thanks for the reply. I am using a query. I have one date working
great but the other 2 are fighting me. Here's my code:

StatusDate is working well as 01/01/2005 but DOB and DOH still give me
01/01/2005 0:00. It must be my quotes or commas. I mess them up easily.

Any suggestions?

Hi, Bonnie,

[quoted text clipped - 16 lines]