,[PartNumber]) AS PartNumAndRev, tblParts.Code,
tblPartsReceipt.PartNumberID, tblParts.PartNumber, tblParts.PartDescription,
tblPartsReceipt.PartDateCode, tblPartsReceipt.NewDateCode,
tblPartsReceipt.InserviceDate, tblPartsReceipt.FailureDate,
tblPartsReceipt.InvalidDates, tblPartsReceipt.Mileage,
IIf(IsDate([NewDateCode]) And
IsDate([FailureDate]),DateDiff("d",[NewDateCode],[FailureDate]),IIf(IsDate([
InserviceDate]) And
IsDate([failuredate]),DateDiff("d",[InserviceDate],[FailureDate]),Null)) AS
DaysToFailFromMfg, [DaysToFailFromMfg]/30 AS MonthsToFailFromMfg,
IIf(IsDate([InserviceDate]) And
IsDate([FailureDate]),DateDiff("d",[InserviceDate],[FailureDate]),Null) AS
DaysUntilFailureInservice, [DaysUntilFailureInservice]/30 AS
MonthsToFailureInservice, tblPartsReceipt.PlantReturnQuantity,
tblPartsReceipt.SpecificComplaint, tblPartsReceipt.DateReceived,
tblPartsReceipt.InputBy, tblPartsReceipt.TestPass, tblPartsReceipt.TestFail,
tblFailureClassifications.FailureClassificationCode,
tblFailureSubClass.FailureSubCode, tblPartsReceipt.Result,
tblPartsReceipt.Comment, tblPartsReceipt.CommentOther,
tblPartsReceipt.AnalysisDate, tblPartsReceipt.AnalysisBy,
tblPartsReceipt.AnalysisComplete, tblPartsReceipt.Symptom,
tblPartsReceipt.AnalysisDisp, tblPartsReceipt.AnalysisDispDate,
tblPartsReceipt.Scrapped, tblPartsReceipt.CreditIssued,
tblPartsReceipt.[CA#], tblPartsReceipt.SerialNo,
tblPartsReceipt.PackingSlipNo, tblPartsReceipt.AnalysisDispCompl,
tblPartsReceipt.DispositionDate, tblPartsReceipt.PartsCostsPaid,
tblPartsReceipt.HandlingCostsPaid, tblPartsReceipt.LaborCostsPaid,
tblPartsReceipt.OutsideCostsPaid, tblPartsReceipt.CoreCostsPaid,
tblPartsReceipt.ClaimResponsibility, tblPartsReceipt.AddtlComments,
tblPartsReceipt.FailedPartsDisposition, tblPartsReceipt.DispositionComplete,
tblPartsReceipt.ReceiptID,
IIf([Suffix]="-50","Display",IIf([Suffix]="-52","Module",IIf([Suffix]="-60",
"Fuel Senders",IIf([Suffix]="-62","Sensor","")))) AS AcctType,
IIf([CodeGroup]="Display","-50",IIf([CodeGroup]="Module","-52",IIf([CodeDesc
ription]="Fuel Senders","-60",IIf([CodeGroup]="Sensor","-62")))) AS Suffix,
IIf([CodeGroup]="Display","-50" & [BWResp],IIf([CodeGroup]="Module","-52" &
[BWResp],IIf([CodeDescription]="Fuel Senders","-60" &
[BWResp],IIf([CodeGroup]="Sensor","-62" & [BWResp])))) AS AcctSuffix,
IIf([NewDateCode]=[InserviceDate],"fixed","") AS BWFixDateCode,
IIf([NewDateCode] Is Not Null,[NewDateCode],IIf([InserviceDate] Is Not
Null,[InserviceDate],[DateReceived])) AS CalcDate, IIf([FailureDate] Is
Null,[DateReceived],[FailureDate]) AS CalcFailDate,
IIf([CalcDate]>[CalcFailDate],0,IIf([CalcDate]<=#9/30/2003# And
[CalcFailDate]<=#9/30/2005#,1,IIf([CalcDate]>#9/30/2003#,2,IIf([CalcFailDate
]<=#9/30/2003#,1,0)))) AS Test, IIf([PartNumAndRev]="1",0,1) AS Test2,
IIf([CalcFailDate] Is Null,"-BW","") AS OldIssues,
IIf([OldIssues]="-BW","-BW",IIf([Test]=1 And [Test2]=1,"-BW",IIf([Test]=2
And [Test2]=1,"-MD",""))) AS BWResp, [PartNumAndRev] & [BWResp] AS
[PartNum&Suffix]
FROM tblReceipt INNER JOIN (((tblPartsReceipt LEFT JOIN tblParts ON
tblPartsReceipt.PartNumberID = tblParts.PartNumberID) LEFT JOIN
tblFailureClassifications ON tblPartsReceipt.FailureClassificationID =
tblFailureClassifications.FailureClassificationID) LEFT JOIN
tblFailureSubClass ON tblPartsReceipt.FailureSubClassID =
tblFailureSubClass.FailureSubID) ON tblReceipt.ReceiptID =
tblPartsReceipt.ReceiptID
ORDER BY tblPartsReceipt.BWTagID DESC;
Explanation: Part of what this query is doing is testing if some of the
dates fall between a certain range. Based on the outcome, a field in that
record has a designation (suffix) for a particular "company". Would it help
to see some of the actual data?
-jb
[QUOTE="Ken Snell"]
Please post the SQL of the qryReceipts&Parts query too. Thanks.
--
Ken Snell
<MS ACCESS MVP>
[QUOTE="JudyB"]
We created the query using design view, and after looking at the SQL
language in the query, I can see that it was created from another query, not
the various tables directly. Here is the language taken from the SQL view
of the query we are attempting to export to Excel (I hope this is what you
were referring to):
SELECT [qryReceipts&Parts].BWTagID, tblRMA.CustomerID,
tblCustomerInformation.CustomerName, [qryReceipts&Parts].CustomerTagNo,
[qryReceipts&Parts].RMAID, [qryReceipts&Parts].PartNumberID,
[qryReceipts&Parts].PartNumAndRev, [qryReceipts&Parts].PartDescription,
[qryReceipts&Parts].CodeGroup, [qryReceipts&Parts].CodeDescription,
[qryReceipts&Parts].AcctType, [qryReceipts&Parts].Suffix,
[qryReceipts&Parts].AcctSuffix, [qryReceipts&Parts].NewDateCode,
[qryReceipts&Parts].InserviceDate, [qryReceipts&Parts].FailureDate,
[qryReceipts&Parts].InvalidDates, [qryReceipts&Parts].BWFixDateCode,
[qryReceipts&Parts].BWResp, [qryReceipts&Parts].[PartNum&Suffix],
[qryReceipts&Parts].Mileage, [qryReceipts&Parts].DaysToFailFromMfg,
[qryReceipts&Parts].MonthsToFailFromMfg,
[qryReceipts&Parts].DaysUntilFailureInservice,
[qryReceipts&Parts].MonthsToFailureInservice,
[qryReceipts&Parts].PlantReturnQuantity,
[qryReceipts&Parts].SpecificComplaint, [qryReceipts&Parts].DateReceived,
[qryReceipts&Parts].InputBy, [qryReceipts&Parts].BWTagID,
[qryReceipts&Parts].TestPass, [qryReceipts&Parts].TestFail,
[qryReceipts&Parts].FailureClassificationCode,
[qryReceipts&Parts].FailureSubCode, [qryReceipts&Parts].Result,
[qryReceipts&Parts].Comment, [qryReceipts&Parts].AnalysisDate,
[qryReceipts&Parts].AnalysisBy, [qryReceipts&Parts].AnalysisComplete,
[qryReceipts&Parts].Symptom
FROM ([qryReceipts&Parts] LEFT JOIN tblRMA ON [qryReceipts&Parts].RMAID =
tblRMA.RMAID) LEFT JOIN tblCustomerInformation ON tblRMA.CustomerID =
tblCustomerInformation.CustomerID;
Thanks again! - jb
[QUOTE="Ken Snell"]
Yes, please post the info when you can. I'll monitor this thread.
--
Ken Snell
<MS ACCESS MVP>
The data we are exporting is actually a query made up of several tables.
All the date fields in the tables are a data type of Date/Time with the
format set as Short Date. We tried both the Office Links and just
copy/paste to bring the information into Excel. We get the same results.
If you still need the SQL language for the query, it will be just a couple
days for me to get that (I am not at the same location as the database!).
Thanks in advance for any help!!
JB
Your problem is the reverse of what I usually see....text fields
changing
to
dates.
Please post more info such as the SQL of the query, info about the
table's
fields (format, data content), and how you're doing the export.
--
Ken Snell
<MS ACCESS MVP>
We have a query which includes several date fields. When[/QUOTE] exported
to[QUOTE]
Excel
2002, the date fields change to text, and only through several steps
can
be
changed back to date fields for use in pivot tables and other
analysis.
Is
there an easier way to ensure that all date fields come through as
legitimate date fields and stay that way? We tried turning off the
Error
Checking options in Excel before bringing the data in, but this does
not
help.
[/QUOTE]
[/QUOTE]
[/QUOTE]
