Cannot import data with my update query

[Bmstenner]

I have an access database that imports data from TXT files, I have a module which does a case by case selection of these 21 txt files.

It will successfully import all cases but Case #9 which is PdoxTadv.txt, I believe that the problem is in my query.

Here is a working query, which will import data without a problem::


UPDATE CBM RIGHT JOIN TempImport ON CBM.ContractNumber = TempImport.Field1 SET CBM.ContractNumber = [TempImport].[Field1], CBM.ContractType = [TempImport].[Field2], CBM.Name = [TempImport].[Field3], CBM.LongName = [TempImport].[Field4], CBM.MailName = [TempImport].[Field5], CBM.Address1 = [TempImport].[Field6], CBM.Address2 = [TempImport].[Field7], CBM.Suburb = [TempImport].[Field8], CBM.Postcode = [TempImport].[Field9], CBM.Birthdate = [TempImport].[Field10], CBM.HomePhone = [TempImport].[Field11], CBM.BusinessPhone = [TempImport].[Field12], CBM.B2Name = [TempImport].[Field13], CBM.B2Address1 = [TempImport].[Field14], CBM.B2Address2 = [TempImport].[Field15], CBM.B2Suburb = [TempImport].[Field16], CBM.B2Postcode = [TempImport].[Field17], CBM.B2Birthdate = [TempImport].[Field18], CBM.B2HomePhone = [TempImport].[Field19], CBM.B2BusinessPhone = [TempImport].[Field20], CBM.ContractDate = [TempImport].[Field21], CBM.Category = [TempImport].[Field22], CBM.DealerCode = [TempImport].[Field23], CBM.FirstRepayDate = [TempImport].[Field24], CBM.AnnualRate = [TempImport].[Field25], CBM.PeriodOfLoan = [TempImport].[Field26], CBM.PurchasePrice = [TempImport].[Field27], CBM.Deposit = [TempImport].[Field28], CBM.EstablishmentFee = [TempImport].[Field29], CBM.ValuationFee = [TempImport].[Field30], CBM.AmountofCredit = [TempImport].[Field31], CBM.MinimumMonthlyPayment = [TempImport].[Field32], CBM.InsCompany = [TempImport].[Field33], CBM.InsuredFor = [TempImport].[Field34], CBM.InsuranceRenewalDate = [TempImport].[Field35], CBM.InsurancePolicy = [TempImport].[Field36], CBM.SolicitorBorrower = [TempImport].[Field37], CBM.SolicitorWestlawn = [TempImport].[Field38], CBM.Occupation = [TempImport].[Field39], CBM.Security1 = [TempImport].[Field40], CBM.Security2 = [TempImport].[Field41], CBM.Guarantor1Name = [TempImport].[Field42], CBM.Guarantor1Phone = [TempImport].[Field43], CBM.Guarantor1Add1 = [TempImport].[Field44], CBM.Guarantor1Add2 = [TempImport].[Field45], CBM.Guarantor1Suburb = [TempImport].[Field46], CBM.Guarantor1Postcode = [TempImport].[Field47], CBM.Guarantor2Name = [TempImport].[Field48], CBM.Guarantor2Phone = [TempImport].[Field49], CBM.Guarantor2Add1 = [TempImport].[Field50], CBM.Guarantor2Add2 = [TempImport].[Field51], CBM.Guarantor2Suburb = [TempImport].[Field52], CBM.Guarantor2Postcode = [TempImport].[Field53], CBM.DelinquencyFrequency = [TempImport].[Field54], CBM.Customer1 = [TempImport].[Field55], CBM.Customer2 = [TempImport].[Field56], CBM.Customer3 = [TempImport].[Field57];

>>>>>>>>>>>>

And now for the one thats NOT working:

UPDATE ADV RIGHT JOIN TempImport ON ADV.ContractNumber = TempImport.Field1 SET ADV.ContractNumber = [TempImport].[field1], ADV.Name = [TempImport].[field2], ADV.ExtendedName = [TempImport].[field3], ADV.MailingName = [TempImport].[field4], ADV.Address1 = [TempImport].[field5], ADV.Address2 = [TempImport].[field6], ADV.Suburb = [TempImport].[field7], ADV.Postcode = [TempImport].[field8], ADV.Mail = [TempImport].[field9], ADV.Dealer = [TempImport].[field10], ADV.Category = [TempImport].[field11], ADV.PhoneHome = [TempImport].[field12], ADV.PhoneMobile = [TempImport].[field13], ADV.PhoneFax = [TempImport].[field14], ADV.ContactName = [TempImport].[field15], ADV.ApprovedBy = [TempImport].[field16], ADV.ContractDate = [TempImport].[field17], ADV.ReviewDate = [TempImport].[field18], ADV.AdminFee = [TempImport].[field19], ADV.Limit = [TempImport].[field20], ADV.InterestRate = [TempImport].[field21], ADV.LoanTime = [TempImport].[field22], ADV.RepaymentAmount = [TempImport].[field23], ADV.RepaymentArrangement = [TempImport].[field24], ADV.FluctuatingReducing = [TempImport].[field25], ADV.RepayInterval = [TempImport].[field26], ADV.PayMethod = [TempImport].[field27], ADV.LoanDetails = [TempImport].[field28], ADV.Security = [TempImport].[field29], ADV.Valuation = [TempImport].[field30], ADV.ValuationDate = [TempImport].[field31], ADV.InsuranceCompany = [TempImport].[field32], ADV.InsuranceDate = [TempImport].[field33], ADV.InterestCharge = [TempImport].[field34], ADV.Balance = [TempImport].[field35], ADV.Customer1 = [TempImport].[field36], ADV.Customer2 = [TempImport].[field37], ADV.Customer3 = [TempImport].[field38];

>>>>>>>>>>>>>>>

I have looked at my Specfiles and they seem to be OK, I can manually import data by using "file, get external data > import" and put into the ADV table.

I would really appreciate any ideas, I have been trying to figure this one out for weeks.. without any luck.

Thanks for reading.

 

[Golom]

The only things that jump out are:

1. You are updating the match fields. For example
ON ADV.ContractNumber = TempImport.Field1
SET
ADV.ContractNumber = [TempImport].[field1],

given that those two fields must match already, this seems unnecessary.

2. The RIGHT JOIN could mean that there is no record identified in the ADV table (i.e. it is NULL.) Do you need an INNER JOIN here instead?

3. The other possibility is that your data contains something illegal like embedded quotes that's causing a problem.

What error is it reporting?

 

[Bmstenner]

Hi mate

Thanks for the reply, I ended up figuring out what it was yesterday.

When my module makes a call to the "Specfile" its saying "i want to import data into (e.g. tbl adv.contractnumber from tempimport.contractnumber, instead, its trying to get it from tbl adv.contractnumber from tempimport.field1, now as we both know (now anyway for me) that there is no such field as "Field1" in the query and also not in the Specfile!! can be tricky!!!

since there was 21 different cases, it was difficult to work out, so i used a new statement:

if i=9 then exit sub, so it would stop when trying to import the data into that table, i could then access the temp file, sort it out and make the correct joins etc!! _

thus the problem

Thanks for your reply, much appreciated,