The specified field could refer to more than one table error with curious but inadequate fix

I have a query that is being created in code. When the query is
executed I get the error: "The specified field 'Acct' could refer to
more than one table listed in the FROM clause".

As you can see below, the 'Acct' field is fully qualified in the
subselect. What is interesting is if I cut and re-paste the same exact
query in the SQL view of design mode for the query object, it works
fine. It even works fine if all I do is remove the semi-colon at the
end.

Here is the query:
INSERT INTO DelinquencyData ( FirstInCollections, Acct, PrimaryID,
HostDatabaseID, AcctGrp, AcctType, ProdType, LastName, FirstName,
PersonalID, BirthDate, Address, City, State, PostalCode, Country,
Phone1, Phone2, DelqAmnt, DelqDate, DaysDelq, ChargeOffAmnt,
ChargeOffDate, DaysSinceChargeOff, CurrBalance, PayoffAmnt,
PayoffGoodUntil, NextPayoffAmnt, DelqStatus, LastPaymDate,
LastPaymAmnt, PaymTotal, RecoveryFlag, OpenDate, OpenAmnt, ClosedDate,
CreditLimit, PointScore, Branch, Officer, SpecialDesc, Times15DaysDelq,
Times30DaysDelq, Times60DaysDelq, Times90DaysDelq, Times120DaysDelq,
Times150DaysDelq, Times180DaysDelq, CredBurFlag, Dealer, InterestRate,
PerDiem, GLAcct, RegPmtAmnt, OrigNumPmts, NSFCount, LastNSFDate,
LastNSFCheck, LastNSFAmnt, LastPaymNSF, InterestDate, InterestDue,
MaturityDate, OverdraftProtection, ATMFlag, TextFld1, TextFld2,
TextFld3, TextFld4, TextFld7, TextFld8, TextFld9, TextFld10, TextFld12,
TextFld13, TextFld14, DateFld7, DateFld11 )
SELECT Now() AS FirstInCollections, ImportData.Acct,
ImportData.PrimaryID, ImportData.HostDatabaseID, ImportData.AcctGrp,
ImportData.AcctType, ImportData.ProdType, ImportData.LastName,
ImportData.FirstName, ImportData.PersonalID, ImportData.BirthDate,
replace(nz(ImportData.Address),'|',Chr$(13) & Chr$(10)) AS
DelqDataAddress, ImportData.City, ImportData.State,
ImportData.PostalCode, ImportData.Country, ImportData.Phone1,
ImportData.Phone2, ImportData.DelqAmnt, ImportData.DelqDate,
ImportData.DaysDelq, ImportData.ChargeOffAmnt,
ImportData.ChargeOffDate, ImportData.DaysSinceChargeOff,
ImportData.CurrBalance, ImportData.PayoffAmnt,
ImportData.PayoffGoodUntil, ImportData.NextPayoffAmnt,
ImportData.DelqStatus, ImportData.LastPaymDate,
ImportData.LastPaymAmnt, ImportData.PaymTotal, ImportData.RecoveryFlag,
ImportData.OpenDate, ImportData.OpenAmnt, ImportData.ClosedDate,
ImportData.CreditLimit, ImportData.PointScore, ImportData.Branch,
ImportData.Officer, ImportData.SpecialDesc, ImportData.Times15DaysDelq,
ImportData.Times30DaysDelq, ImportData.Times60DaysDelq,
ImportData.Times90DaysDelq, ImportData.Times120DaysDelq,
ImportData.Times150DaysDelq, ImportData.Times180DaysDelq,
ImportData.CredBurFlag, ImportData.Dealer, ImportData.InterestRate,
ImportData.PerDiem, ImportData.GLAcct, ImportData.RegPmtAmnt,
ImportData.OrigNumPmts, ImportData.NSFCount, ImportData.LastNSFDate,
ImportData.LastNSFCheck, ImportData.LastNSFAmnt,
ImportData.LastPaymNSF, ImportData.InterestDate,
ImportData.InterestDue, ImportData.MaturityDate,
ImportData.OverdraftProtection, ImportData.ATMFlag,
ImportData.TextFld1, ImportData.TextFld2, ImportData.TextFld3,
ImportData.TextFld4, ImportData.TextFld7, ImportData.TextFld8,
ImportData.TextFld9, ImportData.TextFld10, ImportData.TextFld12,
ImportData.TextFld13, ImportData.TextFld14, ImportData.DateFld7,
ImportData.DateFld11
FROM ImportData LEFT JOIN DelinquencyData ON ImportData.Acct =
DelinquencyData.Acct
WHERE (((DelinquencyData.Acct) Is Null));

BTW, instead of the outer join, I originally had the where clause say
"where importdata.acct not in (select acct from delinquencydata)". I
got no errors on that query but it performed horribly.

Any ideas? I need to be able to generate this query in code without
having to go back and edit the query manually.

Thanks.
Kathy

How are you creating your query in code? Is it something like:

dim strSQL as string

strSQL = "INSERT INTO DelinquencyData ..."
strSQL = strSQL & "DelqDataAddress, ImportData.City, ...."
strSQL = strSQL & "WHERE (((DelinquencyData.Acct) Is Null))"
CurrentDb.Execute(strSQL)

Perhaps one or more of the individual strings you are concatenating
together to create your SQL are too long. I believe VBA has a
limitation of 1023 characters per line...

Also, if this query works in VBA without the semicolon at the end,
leave it off. The SQL view of the query window seems to be the only
thing that needs it. Neither the RunSQL method of the DoCmd object nor
the Execute method of the Database object require it.

HTH,
Bruce

Yes, I am creating the query with multiple concatenations using string
variables. I actually don't put the semi-colon in, Access adds that
(as well as the extra unneeded parentheses in the where clause) when
the query is created via CreateQueryDef.

Kathy

Check the _code_ that generates the SQL, _very_ carefully. Don't look
at what you see in the Design grid, as the Access query Editor will
"adjust" your query for you when you open it.

If possible, cut and paste the SQL string into the debug window and
have it print it out in the debug editor. Look at the results.

I'd bet that in the _code_ you use to write this query, you are not
explicitly setting the acct field to what it needs to be. (The editor
however, is fixing it for you when you open it up the Query editor.)



On 25 Oct 2005 14:01:19 -0700, "Kathy" <kathy@regardingbooks.com>
wrote:[color=blue]
>Yes, I am creating the query with multiple concatenations using string
>variables. I actually don't put the semi-colon in, Access adds that
>(as well as the extra unneeded parentheses in the where clause) when
>the query is created via CreateQueryDef.[/color]


--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Wow, that was it !!!
I knew Access was adding all the parens, but I didn't think about the
table name.

Thanks !!