exporting dates from query to txt file as string

[CraigDolphin]

I have a complex query that I am exporting to a tab delimited text file.

The query has three date fields, and a time field. The query export results in both the date and time portions of the value being shown instead of one or the other.

I have tried using the format function to force the dates to only show the date portion, and the time field to only show the time portion. So far, without luck.

I would greatly appreciate help from someone who can spot what I am doing wrong. The sql for my query is below.

SELECT tblSiteVisits.RunID AS TripID, Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Trip Start Date], Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Trip End Date], [RunName] & " " & [tblSampleRuns]![RunDate] AS [Trip Name], "LUMMI001" AS [Project ID], tblSites.SiteNumber AS [Station ID], STORET_SiteVisitNum.VisitNum AS [Station Visit Number], [tblSiteVisits]![SiteVisitID] & "-" & [tblParameters]![Matrix] AS [Activity ID], [tblParameters]![Matrix] AS Medium, Storet_ActivityCategories.Activity_Type AS [Activity Type], Storet_ActivityCategories.[Activity Category], tblSampleData.QCIndicator AS [QC Indicator], STORET_Replicate.RepNo AS [Replicate Number], Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Activity Start Date], "" & Format([VisitTime],"hh:nn:ss") & "" AS [Activity Start Time], STORET_TimeZoneOutput.TimeDatum AS [Activity Start Time Zone], "" AS [Depth to Activity], "" AS [Depth to Activity Units], tblSiteVisits.SiteComments AS [Activity Comments], Storet_MethodsProcedures.ProcedureID, IIf([LabParam]=-1,"WSWB","") AS [Gear ID], Storet_Characteristics.CharacteristicName, IIf(IsNull([PracticalDetectionLimit]),Str([Value]),IIf([Value]<[PracticalDetectionLimit],"Present < QL",Str([Value]))) AS [Result Value], tblUnits.UnitName AS [Result Value Units], Storet_Fractions.Fraction, "" AS [Result Comment], "" AS Personnel, IIf([tblParameters]![LabParam]=-1,[LabID],"") AS [Laboratory ID], Storet_MethodsProcedures.ProcedureID AS [Field/Lab Procedure], Storet_MethodsProcedures.SourceAcronym AS [Field/Lab Procedure Source], "" AS [Analysis Date], "" AS [Analysis Time], "" AS [Analysis Time Zone], "" AS [Lab Sample Prep Procedure], "" AS [Lab Sample Prep Procedure Source], tblSampleData.PracticalDetectionLimit AS [Detection Limit], tblSampleData.UnitID AS [Detection Limit Unit], "F" AS [Result Status], tblStatisticTypes.StatisticType AS [Statistic Type], IIf(IsNull([tblSampleData]![StatisticTypeID]),"Actual",[ValueType]) AS [Value Type]
FROM ((Storet_StaticFields RIGHT JOIN tblSites ON Storet_StaticFields.StaticFieldID = tblSites.StaticFieldID) RIGHT JOIN ((tblRuns RIGHT JOIN (tblSampleRuns LEFT JOIN STORET_TimeZoneOutput ON (tblSampleRuns.RunDate = STORET_TimeZoneOutput.RunDate) AND (tblSampleRuns.RunID = STORET_TimeZoneOutput.RunID)) ON tblRuns.RunTypeID = tblSampleRuns.RunTypeID) RIGHT JOIN (tblSiteVisits LEFT JOIN STORET_SiteVisitNum ON tblSiteVisits.SiteVisitID = STORET_SiteVisitNum.SiteVisitID) ON tblSampleRuns.RunID = tblSiteVisits.RunID) ON tblSites.SiteID = tblSiteVisits.SiteID) RIGHT JOIN ((Storet_Fractions RIGHT JOIN (Storet_Characteristics RIGHT JOIN tblParameters ON Storet_Characteristics.CharacteristicID = tblParameters.CharacteristicID) ON Storet_Fractions.FractionID = tblParameters.FractionID) RIGHT JOIN (Storet_MethodsProcedures RIGHT JOIN (Storet_ActivityCategories RIGHT JOIN (STORET_Replicate RIGHT JOIN (((tblSampleData LEFT JOIN tblStatisticTypes ON tblSampleData.StatisticTypeID = tblStatisticTypes.StatisticTypeID) INNER JOIN STORET_ActivityType ON tblSampleData.ResultID = STORET_ActivityType.ResultID) LEFT JOIN tblUnits ON tblSampleData.UnitID = tblUnits.UnitID) ON STORET_Replicate.ResultID = tblSampleData.ResultID) ON Storet_ActivityCategories.ID = tblSampleData.Activity_Category) ON Storet_MethodsProcedures.MethodID = tblSampleData.MethodID) ON tblParameters.ParameterID = tblSampleData.ParameterID) ON tblSiteVisits.SiteVisitID = tblSampleData.SiteVisitID
WHERE (((Storet_Characteristics.CharacteristicName) Is Not Null) AND ((IIf(IsNull([PracticalDetectionLimit]),Str([Value]),IIf([Value]<[PracticalDetectionLimit],"Present < QL",Str([Value])))) Is Not Null) AND ((tblSampleRuns.RunDate) Between [forms]![fmExportStoret]![StartExport] And [Forms]![fmExportStoret]![EndExport]) AND ((tblSampleData.SampleStrata)=1 Or (tblSampleData.SampleStrata)=2 Or (tblSampleData.SampleStrata)=3) AND ((tblSampleData.Exported_To_Storet)=[Forms]![fmExportStoret]![ExportType] Or (tblSampleData.Exported_To_Storet)=0));

and the code for the export is

    Dim MYPATH As String

    MYPATH = CurrentProject.Path & "\EXPORTRESULTS\" & Format(Date, "mmddyyyy") & ".txt"
    
    DoCmd.TransferText acExportDelim, "STORETEXPORT", "STORET_MSRRESULTS_TABLE2", MYPATH, True

Thanks for taking the time to help

 

[CraigDolphin]

Too many New Year's hangovers? No one willing to help? Wrong forum section? Or is more information needed?

I did a lot of searching of the board before posting without finding anything beyond 'use the format function' answer. Which I've tried without success hence my question.

The issue is important to me since I'm trying to export into a tab delimited file that will be imported to an oracle database run by the US EPA for water quality data. The import file must conform to their standards.

I really can't believe it is so difficult to export a date as only a date!

 

[Moniker]

Can you post a picture of the QBE on what those JOINS look like? There's all sorts of funky stuff going on in there. I have to get back to work, but in the meantime, I've formatted that SQL so that it's almost readable now. (The screenshot showing the relationships will help a lot.) Maybe someone can get to this before I do.

~Moniker

SELECT
tblSiteVisits.RunID AS TripID
, Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Trip Start Date]
, Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Trip End Date]
, [RunName] & " " & [tblSampleRuns]![RunDate] AS [Trip Name]
, "LUMMI001" AS [Project ID], tblSites.SiteNumber AS [Station ID]
, STORET_SiteVisitNum.VisitNum AS [Station Visit Number]
, [tblSiteVisits]![SiteVisitID] & "-" & [tblParameters]![Matrix] AS [Activity ID]
, [tblParameters]![Matrix] AS Medium, Storet_ActivityCategories.Activity_Type AS [Activity Type]
, Storet_ActivityCategories.[Activity Category]
, tblSampleData.QCIndicator AS [QC Indicator]
, STORET_Replicate.RepNo AS [Replicate Number]
, Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Activity Start Date]
, "" & Format([VisitTime],"hh:nn:ss") & "" AS [Activity Start Time]
, STORET_TimeZoneOutput.TimeDatum AS [Activity Start Time Zone]
, "" AS [Depth to Activity], "" AS [Depth to Activity Units]
, tblSiteVisits.SiteComments AS [Activity Comments]
, Storet_MethodsProcedures.ProcedureID
, IIf([LabParam]=-1,"WSWB","") AS [Gear ID]
, Storet_Characteristics.CharacteristicName
, IIf(IsNull([PracticalDetectionLimit])
, Str([Value]),IIf([Value]<[PracticalDetectionLimit]
, "Present < QL",Str([Value]))) AS [Result Value]
, tblUnits.UnitName AS [Result Value Units]
, Storet_Fractions.Fraction, "" AS [Result Comment]
, "" AS Personnel
, IIf([tblParameters]![LabParam]=-1,[LabID],"") AS [Laboratory ID]
, Storet_MethodsProcedures.ProcedureID AS [Field/Lab Procedure]
, Storet_MethodsProcedures.SourceAcronym AS [Field/Lab Procedure Source]
, "" AS [Analysis Date], "" AS [Analysis Time], "" AS [Analysis Time Zone]
, "" AS [Lab Sample Prep Procedure]
, "" AS [Lab Sample Prep Procedure Source]
, tblSampleData.PracticalDetectionLimit AS [Detection Limit]
, tblSampleData.UnitID AS [Detection Limit Unit]
, "F" AS [Result Status]
, tblStatisticTypes.StatisticType AS [Statistic Type]
, IIf(IsNull([tblSampleData]![StatisticTypeID]),"Actual",[ValueType]) AS [Value Type]

FROM 
((Storet_StaticFields 
RIGHT JOIN tblSites ON 
    Storet_StaticFields.StaticFieldID = tblSites.StaticFieldID) 
RIGHT JOIN ((tblRuns 
    RIGHT JOIN (tblSampleRuns 
    LEFT JOIN STORET_TimeZoneOutput ON 
    (tblSampleRuns.RunDate = STORET_TimeZoneOutput.RunDate) 
    AND (tblSampleRuns.RunID = STORET_TimeZoneOutput.RunID)) ON 
    tblRuns.RunTypeID = tblSampleRuns.RunTypeID) 
    RIGHT JOIN (tblSiteVisits 
    LEFT JOIN STORET_SiteVisitNum ON 
    tblSiteVisits.SiteVisitID = STORET_SiteVisitNum.SiteVisitID) ON 
    tblSampleRuns.RunID = tblSiteVisits.RunID) ON tblSites.SiteID = tblSiteVisits.SiteID) 
    RIGHT JOIN ((Storet_Fractions 
    RIGHT JOIN (Storet_Characteristics 
    RIGHT JOIN tblParameters ON Storet_Characteristics.CharacteristicID = tblParameters.CharacteristicID) ON 
    Storet_Fractions.FractionID = tblParameters.FractionID) 
    RIGHT JOIN (Storet_MethodsProcedures 
    RIGHT JOIN (Storet_ActivityCategories 
    RIGHT JOIN (STORET_Replicate 
    RIGHT JOIN (((tblSampleData 
    LEFT JOIN tblStatisticTypes ON 
    tblSampleData.StatisticTypeID = tblStatisticTypes.StatisticTypeID) 
    INNER JOIN STORET_ActivityType ON 
    tblSampleData.ResultID = STORET_ActivityType.ResultID) 
    LEFT JOIN tblUnits ON 
    tblSampleData.UnitID = tblUnits.UnitID) ON 
    STORET_Replicate.ResultID = tblSampleData.ResultID) ON 
    Storet_ActivityCategories.ID = tblSampleData.Activity_Category) ON 
    Storet_MethodsProcedures.MethodID = tblSampleData.MethodID) ON 
    tblParameters.ParameterID = tblSampleData.ParameterID) ON 
    tblSiteVisits.SiteVisitID = tblSampleData.SiteVisitID

WHERE 
    (((Storet_Characteristics.CharacteristicName) Is Not Null) 
    AND ((IIf(IsNull([PracticalDetectionLimit]),Str([Value]),IIf([Value]<[PracticalDetectionLimit],"Present < QL",Str([Value])))) Is Not Null) 
    AND ((tblSampleRuns.RunDate) Between [forms]![fmExportStoret]![StartExport] And [Forms]![fmExportStoret]![EndExport]) 
    AND ((tblSampleData.SampleStrata)=1 Or (tblSampleData.SampleStrata)=2 Or (tblSampleData.SampleStrata)=3) 
    AND ((tblSampleData.Exported_To_Storet)=[Forms]![fmExportStoret]![ExportType] Or (tblSampleData.Exported_To_Storet)=0));

 

[CraigDolphin]

Thanks for taking the time to reply Moniker Have attached a screenshot of the relationships used by the query.

The query itself seems to produce the right output within access. The trouble comes when exporting the results to a tab delimited file.

 

[KeithG]

Have you tried the CStr function to change the Date to a string?

 

[Bat17]

what are you opening the exported file with? Sometimes xl will see text dates and turn them back to real dates

Peter

 

[CraigDolphin]

I have not tried the CStr function Keith. I'll look into it and let you know how it goes. Thanks for the suggestion.

Bat17, I have opened the txt file in notepad and the time portion was still there after the date. Thanks though.

 

[boblarson]

I think the problem is that Access isn't exporting the data as "formatted" and will include all data regardless of how it's formatted. You may have create a field in your query that builds the data the way you want it. I think you can get around this, if you use a actual saved query to create your fields, in the format you want and then set your other query to use that as the base.

 

[Bat17]

the basic method seems fine, I just tried it on a simple query with no problem.
try just right-clicking the query and exporting and see if it works manually.

peter

 

[CraigDolphin]

The CStr function doesn't seem to have any effect on the txt file output.

However, I think I have solved the problem independently. When I deleted the export specification name from the transfertext code:

DoCmd.TransferText acExportDelim, "STORETEXPORT", "STORET_MSRRESULTS_TABLE2", MYPATH, True

thusly...

DoCmd.TransferText acExportDelim, , "STORET_MSRRESULTS_TABLE2", MYPATH, True

...the txt file output is just fine.

So, it seems like something in the export specification is creating the problem. Now I can't access the export specification to delete it but that's another problem.

Thanks very much to those who took the time to reply. I really appreciate it.