By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Access 2K query returns different results when run in code than whenrun manually

P: n/a
When run manually, my query appends 14 records. When run from code using
CurrentProject.Connection.Execute, the same query appends 11 records.

Does anyone have an explanation of why this could be happening? I have
tried the ADO 2.1 and 2.7 libraries.

TIA

Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
You positive it's exactly the same query? Are you definitely running it
against the same database both times?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Bruce Dodds" <br********@comcast.net> wrote in message
news:MCPqc.78653$536.12973485@attbi_s03...
When run manually, my query appends 14 records. When run from code using
CurrentProject.Connection.Execute, the same query appends 11 records.

Does anyone have an explanation of why this could be happening? I have
tried the ADO 2.1 and 2.7 libraries.

TIA

Nov 12 '05 #2

P: n/a
Douglas J. Steele wrote:
You positive it's exactly the same query? Are you definitely running it
against the same database both times?

Yes, I'm sure.

Nov 12 '05 #3

P: n/a
CDB
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...

Clive
"Bruce Dodds" <br********@comcast.net> wrote in message
news:qUTqc.81730$xw3.4570715@attbi_s04...
Douglas J. Steele wrote:
You positive it's exactly the same query? Are you definitely running it
against the same database both times?

Yes, I'm sure.

Nov 12 '05 #4

P: n/a
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;


Nov 12 '05 #5

P: n/a
Bruce Dodds <br********@comcast.net> wrote in
news:lV0rc.84617$xw3.4719094@attbi_s04:
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*"'.


Is the back end a Jet MDB? Or a server database?

If the former, why the hell are you bothering with ADO?

If the latter, why are you using Jet's wildcard operator instead of
"%"?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #6

P: n/a
David W. Fenton wrote:
Bruce Dodds <br********@comcast.net> wrote in
news:lV0rc.84617$xw3.4719094@attbi_s04:

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*"'.

Is the back end a Jet MDB? Or a server database?

If the former, why the hell are you bothering with ADO?

If the latter, why are you using Jet's wildcard operator instead of
"%"?


The back end is Jet. I'm using ADO because I generally use DAO, and want
to get more experience with ADO. This is a pretty trivial application.

Getting rid of "Like" did fix the problem.

Nov 12 '05 #7

P: n/a
Bruce Dodds <br********@comcast.net> wrote in
news:Kr5rc.84001$536.13973775@attbi_s03:
David W. Fenton wrote:
Bruce Dodds <br********@comcast.net> wrote in
news:lV0rc.84617$xw3.4719094@attbi_s04:
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*"'.
Is the back end a Jet MDB? Or a server database?

If the former, why the hell are you bothering with ADO?

If the latter, why are you using Jet's wildcard operator instead
of "%"?


The back end is Jet. I'm using ADO because I generally use DAO,
and want to get more experience with ADO. . . .


Why do you want to get more experience with a technology that is
just as much of a dead end as DAO?
Getting rid of "Like" did fix the problem.


But it then wouldn't be the same query.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #8

P: n/a
David W. Fenton wrote:
Bruce Dodds <br********@comcast.net> wrote in
news:Kr5rc.84001$536.13973775@attbi_s03:

David W. Fenton wrote:

Bruce Dodds <br********@comcast.net> wrote in
news:lV0rc.84617$xw3.4719094@attbi_s04:
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*"'.

Is the back end a Jet MDB? Or a server database?

If the former, why the hell are you bothering with ADO?

If the latter, why are you using Jet's wildcard operator instead
of "%"?
The back end is Jet. I'm using ADO because I generally use DAO,
and want to get more experience with ADO. . . .

Why do you want to get more experience with a technology that is
just as much of a dead end as DAO?


I thought I might run into ADO while working with systems developed
elsewhere, or use it myself on A2K applications.
Getting rid of "Like" did fix the problem.

But it then wouldn't be the same query.


That's fine with me. It was useful to find out that ADO may have a
problem with "Like", though that is the native Jet wildcard.

Thanks for the feedback, though it came with a dash of vinegar.

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.