Please help - text export has stopped working

A

Alec M1BNK

Hi Guys

I need some help. My export of a query as a comma delimited text file worked
fine until i modified the query yesterday. Now I get "Run-time Error 3011,
Microsoft Jet database engine could not find the object
<filename>......................."

Why has chaging the query caused this error. If I put the old query back it
works fine? How do I fix it. I burned the midnight oil (and then some) but to
no avail.

Database is a single entity, Access 2000 file format running on Access 2003,
not split yet because I am still commissioning it.

The code which calls the query

DoCmd.TransferText acExportDelim, "samout_filespec", "q_SamUpload",
[Forms]![f_FlatFileOut]![filename2], False

The old query

SELECT t_MainData.ProductionSubsidiaryCode AS [Company Code],
t_MainData.ASC_Code AS [Repair Centre Code], t_MainData.Amk_ID AS [Claim
Number], t_MainData.RepairType AS [Service type], t_MainData.DefectType, ""
AS Authorisation_No, t_Customers.Name AS [Consumer Name],
t_Customers.Address1, t_Customers.Address2, t_Customers.TownCity AS Address3,
t_Customers.PostCode, t_Customers.Telephone1 AS [Consumer Tel],
t_Customers.Fax, t_MainData.RunningModel AS [Model name],
t_MainData.ProductSerialNo AS [Serial number], t_MainData.CRT_LCD_SerialNo AS
[CRT Serial No], t_MainData.CRT_LCD_Maker AS [CRT Manufacturer Name],
t_MainData.ConditionCode1 AS [IRIS Condition Code], t_MainData.DefectCode1 AS
[IRIS Defect Code], t_MainData.SymptomCode1 AS [IRIS Symptomn Code],
t_MainData.RepairCode1 AS [IRIS Repair Code], "" AS IRIS_Flag, "" AS
IRIS_Section, "" AS Accessory, Year([PurchaseDate]) & Right(("0" &
Month([PurchaseDate])),2) & Right(("0" & Day([PurchaseDate])),2) AS [Purchase
Date], Year([RequestedDate]) & Right(("0" & Month([RequestedDate])),2) &
Right(("0" & Day([RequestedDate])),2) AS [SVC Request Date],
Year([UnitDespatchDate]) & Right(("0" & Month([UnitDespatchDate])),2) &
Right(("0" & Day([UnitDespatchDate])),2) AS [SVC Complete Date],
Year([FG_Date]) & Right(("0" & Month([FG_Date])),2) & Right(("0" &
Day([FG_Date])),2) AS [Delivery date], t_MainData.Dealer_Name AS [Dealer
name/Code], "" AS Distance, "" AS [Delivery Cost], "" AS [Parts Amount], ""
AS [Other Cost], "" AS [Other Amount Description],
Left$([DetailSymptomDesc],50) AS [Defect Description],
t_MainData.DetailRepairDesc AS [Repair Description], t_MainData.Remark, "" AS
invoicedate, "" AS invoicenumber, t_MainData.TransactionNo AS [Contact Centre
Call Number], "O" AS CallType, "" AS PrintedPages, t_MainData.Parts1Code1 AS
[Part Number], t_MainData.Location1No1 AS Location, IIf((Len([Part
Number])>0),1,"") AS [Used Quantity], "" AS [Parts Amount 1], "" AS [Document
Number 1], t_MainData.Parts1Serial1 AS [Part Serial Number 1],
t_MainData.Parts2Code1 AS [Part Number 2], t_MainData.Location2No1 AS
[Location 2], IIf((Len([Part Number 2])>0),1,"") AS [Used Quantity 2], "" AS
[Parts Amount 2], "" AS [Document Number 2], t_MainData.Parts1Serial2 AS
[Part Serial Number 2]
FROM t_Customers INNER JOIN t_MainData ON t_Customers.CustomerNo =
t_MainData.CustomerNo
WHERE (((t_MainData.DefectType)<>"BS") AND ((t_MainData.Uploaded)=No) AND
((Len([FG_date]))>6) AND ((t_MainData.Manufacturer)="Samsung"));


The new query

SELECT t_MainData.ProductionSubsidiaryCode AS [Company Code],
t_MainData.ASC_Code AS [Repair Centre Code], t_MainData.Amk_ID AS [Claim
Number], t_MainData.RepairType AS [Service type], t_MainData.DefectType, ""
AS Authorisation_No, t_Customers.Name AS [Consumer Name],
t_Customers.Address1, t_Customers.Address2, t_Customers.TownCity AS Address3,
t_Customers.PostCode, t_Customers.Telephone1 AS [Consumer Tel],
t_Customers.Fax, t_MainData.RunningModel AS [Model name],
t_MainData.ProductSerialNo AS [Serial number], t_MainData.CRT_LCD_SerialNo AS
[CRT Serial No], t_MainData.CRT_LCD_Maker AS [CRT Manufacturer Name],
t_MainData.ConditionCode1 AS [IRIS Condition Code], t_MainData.DefectCode1 AS
[IRIS Defect Code], t_MainData.SymptomCode1 AS [IRIS Symptomn Code],
t_MainData.RepairCode1 AS [IRIS Repair Code], "" AS IRIS_Flag, "" AS
IRIS_Section, "" AS Accessory, Year([PurchaseDate]) & Right(("0" &
Month([PurchaseDate])),2) & Right(("0" & Day([PurchaseDate])),2) AS [Purchase
Date], Year([RequestedDate]) & Right(("0" & Month([RequestedDate])),2) &
Right(("0" & Day([RequestedDate])),2) AS [SVC Request Date],
Year([UnitDespatchDate]) & Right(("0" & Month([UnitDespatchDate])),2) &
Right(("0" & Day([UnitDespatchDate])),2) AS [SVC Complete Date],
Year([FG_Date]) & Right(("0" & Month([FG_Date])),2) & Right(("0" &
Day([FG_Date])),2) AS [Delivery date], t_MainData.Dealer_Name AS [Dealer
name/Code], "" AS Distance, "" AS [Delivery Cost], "" AS [Parts Amount], ""
AS [Other Cost], "" AS [Other Amount Description],
IIf(Len([DetailSymptomDesc])>49,Left$([DetailSymptomDesc],49),[DetailSymptomDesc])
AS [Defect Description],
IIf(Len([DetailRepairDesc])>49,Left$([DetailRepairDesc],49),[DetailRepairDesc])
AS [Repair Description], IIf(Len([Remark])>64,Left$([Remark],64),[Remark]) AS
Remarks, "" AS invoicedate, "" AS invoicenumber, t_MainData.TransactionNo AS
[Contact Centre Call Number], "O" AS CallType, "" AS PrintedPages,
t_MainData.Parts1Code1 AS [Part Number], t_MainData.Location1No1 AS Location,
IIf((Len([Part Number])>0),1,"") AS [Used Quantity], "" AS [Parts Amount 1],
"" AS [Document Number 1], t_MainData.Parts1Serial1 AS [Part Serial Number
1], t_MainData.Parts2Code1 AS [Part Number 2], t_MainData.Location2No1 AS
[Location 2], IIf((Len([Part Number 2])>0),1,"") AS [Used Quantity 2], "" AS
[Parts Amount 2], "" AS [Document Number 2], t_MainData.Parts1Serial2 AS
[Part Serial Number 2]
FROM t_Customers INNER JOIN t_MainData ON t_Customers.CustomerNo =
t_MainData.CustomerNo
WHERE (((t_MainData.DefectType)<>"BS") AND ((t_MainData.Uploaded)=No) AND
((Len([FG_date]))>6) AND ((t_MainData.Manufacturer)="SMG"));


In the middle of the query i needed to do some formatting so I changed this
extract

Left$([DetailSymptomDesc],50) AS [Defect Description],
t_MainData.DetailRepairDesc AS [Repair Description], t_MainData.Remark

to

IIf(Len([DetailSymptomDesc])>49,Left$([DetailSymptomDesc],49),[DetailSymptomDesc])
AS [Defect Description],
IIf(Len([DetailRepairDesc])>49,Left$([DetailRepairDesc],49),[DetailRepairDesc])
AS [Repair Description], IIf(Len([Remark])>64,Left$([Remark],64),[Remark]) AS
Remarks


If I change this extract back then it works again - this is a real
headbuster for me, I'm a bit of a newbie at the database game. I hope one of
you guys can save me on this one, as my boss is really busting my head about
not being able to make the customer upload yesterday

Thanks

Alec
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top