CDB wrote:
1. A bug in ADO?
2. The queries are not identical.
3. There is a difference in behaviour between ADO and the Query Wizard.
There -are- differences: eg the query builder packs extra smarts, resolving
matters that will not be resolved with straight SQL. Parameters, for
example.
It sure would be enlightening to see the SQL...
OK.
The application selects Fedex shipments from a table of purchase
transactions and puts them into Fedex address book format.
There are two queries. A select query selects the records and sets a
flag if there is a separate Ship To address. An append query takes that
as input, does some reformatting, and adds the records to an output
table. (The append query calls a user-written function to validate phone
numbers. This has nothing to do with the problem, however, since the
results don't change if the function is removed.)
FWIW, the only records that are dropped from the append when it is
executed from code are the ones that have "USA" in the Country field. So
the difference between the manual and automated query processing may be
in the handling of 'Like "US*"'.
Select query - qrySelectApexFedex
SELECT [Order Number], [Customer Name], [First Name], [Last Name],
Title, [Company Name], [Address 1], [Address 2], City, State, Zip,
Phone, Country, Email, [Ship To], [Ship Title], [Ship First Name], [Ship
Last Name], [Ship Company], [Ship Address 1], [Ship Address 2], [Ship
City], [Ship State], [Ship Zip], [Ship Country], [Ship Phone],
IIf(Len([Ship To])>0,True,False) AS TestShipTo
FROM [Apex Fedex]
WHERE (((Country) Like "US*" Or (Country)="United States" Or
(Country)="Canada" Or (Country)="CA") AND (([Ship To]) Is Null) AND
((([State])="HI" Or ([State])="AK" Or ([State])="PR" Or
([State])="VI")=False)) OR ((([Ship Country]) Like "US*" Or ([Ship
Country])="United States" Or ([Ship Country])="Canada" Or ([Ship
Country])="CA") AND ((([Ship State])="HI" Or ([Ship State])="AK" Or
([Ship State])="PR" Or ([Ship State])="VI")=False));
Append query - qryAppendFedex
INSERT INTO tblApexFedex ( Nickname, FullName, FirstName, LastName,
Title, Company, AddressOne, AddressTwo, City, State, Zip, PhoneNumber,
CountryCode, EmailAddress, ReferenceDescription )
SELECT qrySelectApexFedex.[Customer Name], IIf([TestShipTo]=True,[Ship
To],[Customer Name]) AS FullName, IIf([TestShipTo]=True,[Ship First
Name],[First Name]) AS FirstName, IIf([TestShipTo]=True,[Ship Last
Name],[Last Name]) AS LastName, IIf([TestShipTo]=True,[Ship
Title],[Title]) AS FindTitle, IIf([TestShipTo]=True,[Ship
Company],[Company Name]) AS CompanyName, IIf([TestShipTo]=True,[Ship
Address 1],[Address 1]) AS Address1, IIf([TestShipTo]=True,[Ship Address
2],[Address 2]) AS Address2, IIf([TestShipTo]=True,[Ship City],[City])
AS FindCity, IIf([TestShipTo]=True,[Ship State],[State]) AS FindState,
IIf([TestShipTo]=True,[Ship Zip],[Zip]) AS FindZip,
GetPhone([Phone],[Ship Phone]) AS PhoneIn, IIf(UCase([Country]) Like
"CA*","CA","US") AS CountryCode, qrySelectApexFedex.Email,
qrySelectApexFedex.[Order Number]
FROM qrySelectApexFedex;
