Change of field name causes application to crash

[Guest]

On a report section footer, I have text boxes that each calculate a single
value as follows:

InventoryArrived
=Val(nz(DSum("[ArriveNet]","[LookupInventoryCombinedNet]","[CommodityID] = "
& [CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

InventoryShipped
=Val(nz(DSum("[ShipNet]","[LookupInventoryCombinedNet]","[CommodityID] = " &
[CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

LookupInventoryCombinedNet is the name of a query. ArriveNet and ShipNet are
two fields in that query. Both are calculated approximately the same in the
query: ArriveNet = ArriveIn - ArriveOut; ShipNet = ShipIn - ShipOut.

The anomaly is that the "ShipNet" in the InventoryShipped text box causes
Access to crash when running the report. If I change its name to "ShipNets",
"ABCDEFGH", or anything else in both the query and the calculated control
source above, it works.

Now, what is so special about the field name "ShipNet"? I have
ascertained/tried the following:

-It also works if I leave it as the same name but remove the "and Active"
filter at the end of the DSum.
-"ShipNet" does not appear twice in the query.
-The query runs fine by itself.
-I have compacted/repaired many times.
-I have removed this field from the query, saved the query, then recreated
the field.
-The crash occurs also if I create another query that Sums this field, but
again ONLY if I use ShipNet as the field name.
-I have copied the SQL, deleted the query, and recreated the query using the
same SQL.

The only hint I have to offer is that this query is at the top of a chain of
queries, each built on the one before:

Query6 (this one), whose source is Query5, whose source is Query4, a union
query of six parallel Query3's, each of which is based on Query2, whose
source is Query1.

However, I can run these at any level run just fine and plenty fast enough,
and all other fields work just fine except ShipNet.

 

[Allen Browne]

Brian, there's a lot of stuff going on here, but hopefully some of these
suggestions will help.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact

2. Presumably Active is a Yes/No field? Just to be sure Access understands
this, bracket it. Won't hurt to bracket the other 2 phrases in the Criteria
argument of DSum() as well, i.e.:
("[CommodityID] = " & [CommodityID] & ") And ([UnitID] = " & [UnitID] & ")
And ([Active])"

3. As you know, a yes/no field is 2-state only in JET, i.e. it cannot store
a Null. But if the yes/no field comes from a table that is on the outer side
of a join, a yes/no field in a query can be Null. I've actually seen this
crash Access (shut down by Windows). Any chance the table that Active is
drawn from is on the outer side of a join? If so, you could probably solve
the problem with:
SELECT Nz([Table1].[Active], False) AS IsActive, ...

4. Presumably CommodityID and UnitID are both Number fields, and there is no
chance of them being Null (e.g. result of outer join).

5. Might need some explicit typecasting in the UNION or other stacked
queries, though the expressions you have suggest you probably understand how
to do that.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

On a report section footer, I have text boxes that each calculate a single
value as follows:

InventoryArrived
=Val(nz(DSum("[ArriveNet]","[LookupInventoryCombinedNet]","[CommodityID] =
"
& [CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

InventoryShipped
=Val(nz(DSum("[ShipNet]","[LookupInventoryCombinedNet]","[CommodityID] = "
&
[CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

LookupInventoryCombinedNet is the name of a query. ArriveNet and ShipNet
are
two fields in that query. Both are calculated approximately the same in
the
query: ArriveNet = ArriveIn - ArriveOut; ShipNet = ShipIn - ShipOut.

The anomaly is that the "ShipNet" in the InventoryShipped text box causes
Access to crash when running the report. If I change its name to
"ShipNets",
"ABCDEFGH", or anything else in both the query and the calculated control
source above, it works.

Now, what is so special about the field name "ShipNet"? I have
ascertained/tried the following:

-It also works if I leave it as the same name but remove the "and Active"
filter at the end of the DSum.
-"ShipNet" does not appear twice in the query.
-The query runs fine by itself.
-I have compacted/repaired many times.
-I have removed this field from the query, saved the query, then recreated
the field.
-The crash occurs also if I create another query that Sums this field, but
again ONLY if I use ShipNet as the field name.
-I have copied the SQL, deleted the query, and recreated the query using
the
same SQL.

The only hint I have to offer is that this query is at the top of a chain
of
queries, each built on the one before:

Query6 (this one), whose source is Query5, whose source is Query4, a union
query of six parallel Query3's, each of which is based on Query2, whose
source is Query1.

However, I can run these at any level run just fine and plenty fast
enough,
and all other fields work just fine except ShipNet.

 

[Guest]

Brian, I didn't answer before because the potential answer
was too complex, but now that I see Allen has given a proper
answer, let me add that

In Access 2000, any error can cause Access to crash, Access
just doesn't catch errors reliably. I haven't spent enough time
in A2K2 or A2K3 to know how much this is still true.

Also look out for any other control or object called ShipNet.
You can get errors if there are other Access objects with the
same name.

(david)

 

[Guest]

This is Access 2003. There are, indeed, other objects with the same name. In
fact, on one of the forms (not related to the report in question, but using
the same underlying query), there is a control called ShipNet. However, this
is also true of several other controls on that form, and there is no failure.
See also my notes in response to Allen.

Brian, I didn't answer before because the potential answer
was too complex, but now that I see Allen has given a proper
answer, let me add that

In Access 2000, any error can cause Access to crash, Access
just doesn't catch errors reliably. I haven't spent enough time
in A2K2 or A2K3 to know how much this is still true.

Also look out for any other control or object called ShipNet.
You can get errors if there are other Access objects with the
same name.

(david)

On a report section footer, I have text boxes that each calculate a single
value as follows:

InventoryArrived
=Val(nz(DSum("[ArriveNet]","[LookupInventoryCombinedNet]","[CommodityID] = "
& [CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

InventoryShipped
=Val(nz(DSum("[ShipNet]","[LookupInventoryCombinedNet]","[CommodityID] = " &
[CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

LookupInventoryCombinedNet is the name of a query. ArriveNet and ShipNet are
two fields in that query. Both are calculated approximately the same in the
query: ArriveNet = ArriveIn - ArriveOut; ShipNet = ShipIn - ShipOut.

The anomaly is that the "ShipNet" in the InventoryShipped text box causes
Access to crash when running the report. If I change its name to "ShipNets",
"ABCDEFGH", or anything else in both the query and the calculated control
source above, it works.

Now, what is so special about the field name "ShipNet"? I have
ascertained/tried the following:

-It also works if I leave it as the same name but remove the "and Active"
filter at the end of the DSum.
-"ShipNet" does not appear twice in the query.
-The query runs fine by itself.
-I have compacted/repaired many times.
-I have removed this field from the query, saved the query, then recreated
the field.
-The crash occurs also if I create another query that Sums this field, but
again ONLY if I use ShipNet as the field name.
-I have copied the SQL, deleted the query, and recreated the query using the
same SQL.

The only hint I have to offer is that this query is at the top of a chain of
queries, each built on the one before:

Query6 (this one), whose source is Query5, whose source is Query4, a union
query of six parallel Query3's, each of which is based on Query2, whose
source is Query1.

However, I can run these at any level run just fine and plenty fast enough,
and all other fields work just fine except ShipNet.

 

[Guest]

Thanks, Allen. This thing is driving me crazy today.

#1, 2, & 4: Already the way you suggest
#3 I have duplicated the problem with Active left out
#5 Haven't specifically tried this, but simply changing the name of the
field resolves the problem (a solution that I won't use, at least not yet,
because I really don't understand why); furthermore, parallel fields (e.g.
ArriveNet, coming through the same sequence of queries to generate the total)
in the same report do not cause the crash.

After creating a new DB & importing everything (to isolate potential
corruption issues), I created a simple Sum query against the underlying
query. Note that this works consistently:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ArriveNet)
AS ArriveNet, Sum(LookupInventoryCombinedNet.ShipNet) AS ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

but this generates a "Query is too complex" error (it is actually simpler
because I just removed the ArriveNet field from the query):

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet) AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

and this crashes (allowed AS to default to SumOfShipNet instead of
specifying ABC)

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet) AS
SumOfShipNet
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

This results in "Query too complex" when run from within the query builder
but crashes if the query is saved and then double-clicked from the database
window:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet) AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

Brian, there's a lot of stuff going on here, but hopefully some of these
suggestions will help.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact

2. Presumably Active is a Yes/No field? Just to be sure Access understands
this, bracket it. Won't hurt to bracket the other 2 phrases in the Criteria
argument of DSum() as well, i.e.:
("[CommodityID] = " & [CommodityID] & ") And ([UnitID] = " & [UnitID] & ")
And ([Active])"

3. As you know, a yes/no field is 2-state only in JET, i.e. it cannot store
a Null. But if the yes/no field comes from a table that is on the outer side
of a join, a yes/no field in a query can be Null. I've actually seen this
crash Access (shut down by Windows). Any chance the table that Active is
drawn from is on the outer side of a join? If so, you could probably solve
the problem with:
SELECT Nz([Table1].[Active], False) AS IsActive, ...

4. Presumably CommodityID and UnitID are both Number fields, and there is no
chance of them being Null (e.g. result of outer join).

5. Might need some explicit typecasting in the UNION or other stacked
queries, though the expressions you have suggest you probably understand how
to do that.

HTH.

 

[Guest]

Just a follow-up note. Take a look at my response to David concerning
same-name objects.

Thanks.

Brian, there's a lot of stuff going on here, but hopefully some of these
suggestions will help.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact

2. Presumably Active is a Yes/No field? Just to be sure Access understands
this, bracket it. Won't hurt to bracket the other 2 phrases in the Criteria
argument of DSum() as well, i.e.:
("[CommodityID] = " & [CommodityID] & ") And ([UnitID] = " & [UnitID] & ")
And ([Active])"

3. As you know, a yes/no field is 2-state only in JET, i.e. it cannot store
a Null. But if the yes/no field comes from a table that is on the outer side
of a join, a yes/no field in a query can be Null. I've actually seen this
crash Access (shut down by Windows). Any chance the table that Active is
drawn from is on the outer side of a join? If so, you could probably solve
the problem with:
SELECT Nz([Table1].[Active], False) AS IsActive, ...

4. Presumably CommodityID and UnitID are both Number fields, and there is no
chance of them being Null (e.g. result of outer join).

5. Might need some explicit typecasting in the UNION or other stacked
queries, though the expressions you have suggest you probably understand how
to do that.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

On a report section footer, I have text boxes that each calculate a single
value as follows:

InventoryArrived
=Val(nz(DSum("[ArriveNet]","[LookupInventoryCombinedNet]","[CommodityID] =
"
& [CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

InventoryShipped
=Val(nz(DSum("[ShipNet]","[LookupInventoryCombinedNet]","[CommodityID] = "
&
[CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

LookupInventoryCombinedNet is the name of a query. ArriveNet and ShipNet
are
two fields in that query. Both are calculated approximately the same in
the
query: ArriveNet = ArriveIn - ArriveOut; ShipNet = ShipIn - ShipOut.

The anomaly is that the "ShipNet" in the InventoryShipped text box causes
Access to crash when running the report. If I change its name to
"ShipNets",
"ABCDEFGH", or anything else in both the query and the calculated control
source above, it works.

Now, what is so special about the field name "ShipNet"? I have
ascertained/tried the following:

-It also works if I leave it as the same name but remove the "and Active"
filter at the end of the DSum.
-"ShipNet" does not appear twice in the query.
-The query runs fine by itself.
-I have compacted/repaired many times.
-I have removed this field from the query, saved the query, then recreated
the field.
-The crash occurs also if I create another query that Sums this field, but
again ONLY if I use ShipNet as the field name.
-I have copied the SQL, deleted the query, and recreated the query using
the
same SQL.

The only hint I have to offer is that this query is at the top of a chain
of
queries, each built on the one before:

Query6 (this one), whose source is Query5, whose source is Query4, a union
query of six parallel Query3's, each of which is based on Query2, whose
source is Query1.

However, I can run these at any level run just fine and plenty fast
enough,
and all other fields work just fine except ShipNet.

 

[Allen Browne]

Hi Brian

You've obviously done some good digging to understand what JET is choking on
here. I'm assuming that LookupInventoryCombinedNet is the UNION query you
referred to earlier, which is based on other queries. What we are seeing
therefore is the tip of the iceberg, with lots of other stuff going on in
the lower level queries.

It's fairly easy to crash or confuse JET. It often crashes if I use a
subquery in a lower level query, and then try to use that field in a higher
level query (e.g. in a JOIN.) Null in the yes/no field also causes crashes.
The query-to-complex message is often caused by type mismatches, which can
occur with UNION queries, undeclared parameters, incorrect implicit
typecasts, and so on.

The fact that the query works when you ArriveNet field is included suggests
that summing this field also causes JET to take a different execution path,
which ends up handling ShipNet correctly as well. The default name
(SumOfShipNet) might hint at the same issue, or at a name confusion. I'm not
aware of the specifics of what david@epsomdotcomdotau was referring to, but
his comments are always very good value, so there may be an issue there.

Importing the data into a new mdb (with Name AutoCorrupt turned off
presumably) was a good move.

I'm guessing that the source of the issue is the way the data is generated
by the lower level queries. Typecasting there, and avoiding nulls in the
yes/no results could make a difference when you try to use those results in
your higher level query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. This thing is driving me crazy today.

#1, 2, & 4: Already the way you suggest
#3 I have duplicated the problem with Active left out
#5 Haven't specifically tried this, but simply changing the name of the
field resolves the problem (a solution that I won't use, at least not yet,
because I really don't understand why); furthermore, parallel fields (e.g.
ArriveNet, coming through the same sequence of queries to generate the
total)
in the same report do not cause the crash.

After creating a new DB & importing everything (to isolate potential
corruption issues), I created a simple Sum query against the underlying
query. Note that this works consistently:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ArriveNet)
AS ArriveNet, Sum(LookupInventoryCombinedNet.ShipNet) AS ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

but this generates a "Query is too complex" error (it is actually simpler
because I just removed the ArriveNet field from the query):

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

and this crashes (allowed AS to default to SumOfShipNet instead of
specifying ABC)

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
AS
SumOfShipNet
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

This results in "Query too complex" when run from within the query builder
but crashes if the query is saved and then double-clicked from the
database
window:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

Brian, there's a lot of stuff going on here, but hopefully some of these
suggestions will help.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact

2. Presumably Active is a Yes/No field? Just to be sure Access
understands
this, bracket it. Won't hurt to bracket the other 2 phrases in the
Criteria
argument of DSum() as well, i.e.:
("[CommodityID] = " & [CommodityID] & ") And ([UnitID] = " & [UnitID] &
")
And ([Active])"

3. As you know, a yes/no field is 2-state only in JET, i.e. it cannot
store
a Null. But if the yes/no field comes from a table that is on the outer
side
of a join, a yes/no field in a query can be Null. I've actually seen this
crash Access (shut down by Windows). Any chance the table that Active is
drawn from is on the outer side of a join? If so, you could probably
solve
the problem with:
SELECT Nz([Table1].[Active], False) AS IsActive, ...

4. Presumably CommodityID and UnitID are both Number fields, and there is
no
chance of them being Null (e.g. result of outer join).

5. Might need some explicit typecasting in the UNION or other stacked
queries, though the expressions you have suggest you probably understand
how
to do that.

 

[Guest]

Hi, Allen.

Well, this all kind of got me searching through all the underlying queries
in detail, and I seem to have found a way to resolve this. Perhaps you can
just explain to me why this happens. Maybe I'm way out on a limb here, but I
have a six-way union query separated from the query in question by an
intervening query. Here is the six-way union query (see my notes below it):

SELECT LookupInventoryArriveIn.*
FROM LookupInventoryArriveIn;

UNION SELECT LookupInventoryArriveOut.*
FROM LookupInventoryArriveOut;

UNION SELECT LookupInventoryAdjustIn.*
FROM LookupInventoryAdjustIn;

UNION SELECT LookupInventoryAdjustOut.*
FROM LookupInventoryAdjustOut;

UNION SELECT LookupInventoryShipIn.*
FROM LookupInventoryShipIn;

UNION SELECT LookupInventoryShipOut.*
FROM LookupInventoryShipOut;

Based on some reading-up I did on database efficiency that seemed to
indicate that UNION ALL was preferable to just UNION, I originally had all
the UNION statements as UNION ALL. Removing the ALL seems to have resolved
the immediate problem, but:

1. Why?
2. What am I potentially doing to my application by removing the ALL?

Hi Brian

You've obviously done some good digging to understand what JET is choking on
here. I'm assuming that LookupInventoryCombinedNet is the UNION query you
referred to earlier, which is based on other queries. What we are seeing
therefore is the tip of the iceberg, with lots of other stuff going on in
the lower level queries.

It's fairly easy to crash or confuse JET. It often crashes if I use a
subquery in a lower level query, and then try to use that field in a higher
level query (e.g. in a JOIN.) Null in the yes/no field also causes crashes.
The query-to-complex message is often caused by type mismatches, which can
occur with UNION queries, undeclared parameters, incorrect implicit
typecasts, and so on.

The fact that the query works when you ArriveNet field is included suggests
that summing this field also causes JET to take a different execution path,
which ends up handling ShipNet correctly as well. The default name
(SumOfShipNet) might hint at the same issue, or at a name confusion. I'm not
aware of the specifics of what david@epsomdotcomdotau was referring to, but
his comments are always very good value, so there may be an issue there.

Importing the data into a new mdb (with Name AutoCorrupt turned off
presumably) was a good move.

I'm guessing that the source of the issue is the way the data is generated
by the lower level queries. Typecasting there, and avoiding nulls in the
yes/no results could make a difference when you try to use those results in
your higher level query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. This thing is driving me crazy today.

#1, 2, & 4: Already the way you suggest
#3 I have duplicated the problem with Active left out
#5 Haven't specifically tried this, but simply changing the name of the
field resolves the problem (a solution that I won't use, at least not yet,
because I really don't understand why); furthermore, parallel fields (e.g.
ArriveNet, coming through the same sequence of queries to generate the
total)
in the same report do not cause the crash.

After creating a new DB & importing everything (to isolate potential
corruption issues), I created a simple Sum query against the underlying
query. Note that this works consistently:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ArriveNet)
AS ArriveNet, Sum(LookupInventoryCombinedNet.ShipNet) AS ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

but this generates a "Query is too complex" error (it is actually simpler
because I just removed the ArriveNet field from the query):

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

and this crashes (allowed AS to default to SumOfShipNet instead of
specifying ABC)

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
AS
SumOfShipNet
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

This results in "Query too complex" when run from within the query builder
but crashes if the query is saved and then double-clicked from the
database
window:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID, Sum(LookupInventoryCombinedNet.ShipNet)
AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

Brian, there's a lot of stuff going on here, but hopefully some of these
suggestions will help.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact

2. Presumably Active is a Yes/No field? Just to be sure Access
understands
this, bracket it. Won't hurt to bracket the other 2 phrases in the
Criteria
argument of DSum() as well, i.e.:
("[CommodityID] = " & [CommodityID] & ") And ([UnitID] = " & [UnitID] &
")
And ([Active])"

3. As you know, a yes/no field is 2-state only in JET, i.e. it cannot
store
a Null. But if the yes/no field comes from a table that is on the outer
side
of a join, a yes/no field in a query can be Null. I've actually seen this
crash Access (shut down by Windows). Any chance the table that Active is
drawn from is on the outer side of a join? If so, you could probably
solve
the problem with:
SELECT Nz([Table1].[Active], False) AS IsActive, ...

4. Presumably CommodityID and UnitID are both Number fields, and there is
no
chance of them being Null (e.g. result of outer join).

5. Might need some explicit typecasting in the UNION or other stacked
queries, though the expressions you have suggest you probably understand
how
to do that.

 

[Allen Browne]

UNION de-duplicates. UNION ALL returns all records.
It follows that UNION ALL is simpler/more efficient to execute, so that's
why it's generally recommended.

The fact that the UNION works where UNION ALL does not suggests that the
problem is addressed by forcing a comparsion between the values (i.e. the
comparsion that is used for de-duplicating.)

Again, the 6 lower level queries are involved here, so there is a good
chance of JET confusing the data types, esp. if your queries really do
contain the wildcard or any calculated fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, this all kind of got me searching through all the underlying queries
in detail, and I seem to have found a way to resolve this. Perhaps you can
just explain to me why this happens. Maybe I'm way out on a limb here, but
I
have a six-way union query separated from the query in question by an
intervening query. Here is the six-way union query (see my notes below
it):

SELECT LookupInventoryArriveIn.*
FROM LookupInventoryArriveIn;

UNION SELECT LookupInventoryArriveOut.*
FROM LookupInventoryArriveOut;

UNION SELECT LookupInventoryAdjustIn.*
FROM LookupInventoryAdjustIn;

UNION SELECT LookupInventoryAdjustOut.*
FROM LookupInventoryAdjustOut;

UNION SELECT LookupInventoryShipIn.*
FROM LookupInventoryShipIn;

UNION SELECT LookupInventoryShipOut.*
FROM LookupInventoryShipOut;

Based on some reading-up I did on database efficiency that seemed to
indicate that UNION ALL was preferable to just UNION, I originally had all
the UNION statements as UNION ALL. Removing the ALL seems to have resolved
the immediate problem, but:

1. Why?
2. What am I potentially doing to my application by removing the ALL?

Hi Brian

You've obviously done some good digging to understand what JET is choking
on
here. I'm assuming that LookupInventoryCombinedNet is the UNION query you
referred to earlier, which is based on other queries. What we are seeing
therefore is the tip of the iceberg, with lots of other stuff going on in
the lower level queries.

It's fairly easy to crash or confuse JET. It often crashes if I use a
subquery in a lower level query, and then try to use that field in a
higher
level query (e.g. in a JOIN.) Null in the yes/no field also causes
crashes.
The query-to-complex message is often caused by type mismatches, which
can
occur with UNION queries, undeclared parameters, incorrect implicit
typecasts, and so on.

The fact that the query works when you ArriveNet field is included
suggests
that summing this field also causes JET to take a different execution
path,
which ends up handling ShipNet correctly as well. The default name
(SumOfShipNet) might hint at the same issue, or at a name confusion. I'm
not
aware of the specifics of what david@epsomdotcomdotau was referring to,
but
his comments are always very good value, so there may be an issue there.

Importing the data into a new mdb (with Name AutoCorrupt turned off
presumably) was a good move.

I'm guessing that the source of the issue is the way the data is
generated
by the lower level queries. Typecasting there, and avoiding nulls in the
yes/no results could make a difference when you try to use those results
in
your higher level query.

Thanks, Allen. This thing is driving me crazy today.

#1, 2, & 4: Already the way you suggest
#3 I have duplicated the problem with Active left out
#5 Haven't specifically tried this, but simply changing the name of the
field resolves the problem (a solution that I won't use, at least not
yet,
because I really don't understand why); furthermore, parallel fields
(e.g.
ArriveNet, coming through the same sequence of queries to generate the
total)
in the same report do not cause the crash.

After creating a new DB & importing everything (to isolate potential
corruption issues), I created a simple Sum query against the underlying
query. Note that this works consistently:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ArriveNet)
AS ArriveNet, Sum(LookupInventoryCombinedNet.ShipNet) AS ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

but this generates a "Query is too complex" error (it is actually
simpler
because I just removed the ArriveNet field from the query):

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ShipNet)
AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

and this crashes (allowed AS to default to SumOfShipNet instead of
specifying ABC)

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ShipNet)
AS
SumOfShipNet
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

This results in "Query too complex" when run from within the query
builder
but crashes if the query is saved and then double-clicked from the
database
window:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ShipNet)
AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

:

Brian, there's a lot of stuff going on here, but hopefully some of
these
suggestions will help.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact

2. Presumably Active is a Yes/No field? Just to be sure Access
understands
this, bracket it. Won't hurt to bracket the other 2 phrases in the
Criteria
argument of DSum() as well, i.e.:
("[CommodityID] = " & [CommodityID] & ") And ([UnitID] = " & [UnitID]
&
")
And ([Active])"

3. As you know, a yes/no field is 2-state only in JET, i.e. it cannot
store
a Null. But if the yes/no field comes from a table that is on the
outer
side
of a join, a yes/no field in a query can be Null. I've actually seen
this
crash Access (shut down by Windows). Any chance the table that Active
is
drawn from is on the outer side of a join? If so, you could probably
solve
the problem with:
SELECT Nz([Table1].[Active], False) AS IsActive, ...

4. Presumably CommodityID and UnitID are both Number fields, and there
is
no
chance of them being Null (e.g. result of outer join).

5. Might need some explicit typecasting in the UNION or other stacked
queries, though the expressions you have suggest you probably
understand
how to do that.

 

[Guest]

I'm really only concerned about controls or object which
are part of the report. Sometimes when you have a control
or public variable on the report with a name clash with
a field, you get an error, shown by #name when the problem
is a text control. When you get an error, sometimes Access
crashes.

I am looking to see if there is problem with the Report, rather
than with the query. So, do you have a problem with the
DSUM when it is calculated outside the report? Do you
have a problem with the DSUM if it is calculated in a report
format event, and the value then written to a label caption?
Do you have a problem if you replace DSUM with a User
Defined Function in a standard code module?

This is more of a problem with reports than with forms.
Access is dynamically rewriting and running that DSUM
for every record, and if there is a name clash at runtime,
that might be a source of confusion for Jet.


(david)

This is Access 2003. There are, indeed, other objects with the same name. In
fact, on one of the forms (not related to the report in question, but using
the same underlying query), there is a control called ShipNet. However, this
is also true of several other controls on that form, and there is no failure.
See also my notes in response to Allen.

Brian, I didn't answer before because the potential answer
was too complex, but now that I see Allen has given a proper
answer, let me add that

In Access 2000, any error can cause Access to crash, Access
just doesn't catch errors reliably. I haven't spent enough time
in A2K2 or A2K3 to know how much this is still true.

Also look out for any other control or object called ShipNet.
You can get errors if there are other Access objects with the
same name.

(david)

On a report section footer, I have text boxes that each calculate a single
value as follows:

InventoryArrived

=Val(nz(DSum("[ArriveNet]","[LookupInventoryCombinedNet]","[CommodityID] =
"

& [CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

InventoryShipped
=Val(nz(DSum("[ShipNet]","[LookupInventoryCombinedNet]","[CommodityID]

= "
&

[CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

LookupInventoryCombinedNet is the name of a query. ArriveNet and

ShipNet
are

two fields in that query. Both are calculated approximately the same

in
the

query: ArriveNet = ArriveIn - ArriveOut; ShipNet = ShipIn - ShipOut.

The anomaly is that the "ShipNet" in the InventoryShipped text box causes
Access to crash when running the report. If I change its name to "ShipNets",
"ABCDEFGH", or anything else in both the query and the calculated control
source above, it works.

Now, what is so special about the field name "ShipNet"? I have
ascertained/tried the following:

-It also works if I leave it as the same name but remove the "and Active"
filter at the end of the DSum.
-"ShipNet" does not appear twice in the query.
-The query runs fine by itself.
-I have compacted/repaired many times.
-I have removed this field from the query, saved the query, then recreated
the field.
-The crash occurs also if I create another query that Sums this field, but
again ONLY if I use ShipNet as the field name.
-I have copied the SQL, deleted the query, and recreated the query

using
the

same SQL.

The only hint I have to offer is that this query is at the top of a

chain
of

queries, each built on the one before:

Query6 (this one), whose source is Query5, whose source is Query4, a union
query of six parallel Query3's, each of which is based on Query2, whose
source is Query1.

However, I can run these at any level run just fine and plenty fast enough,
and all other fields work just fine except ShipNet.

 

[Guest]

Actually, I can create a simple Sum query and get the same crash. Look at my
reply to Allen, though - the problem is resolved when I change an underlying
UNION ALL query to simply UNION. In this case, there is no chance of
duplicates in the queries underlying the union query (because they already
group on the appropriate fields), so UNION will return the correct results. I
think I have lots more to learn about union queries and why this would happen.

Thanks.

I'm really only concerned about controls or object which
are part of the report. Sometimes when you have a control
or public variable on the report with a name clash with
a field, you get an error, shown by #name when the problem
is a text control. When you get an error, sometimes Access
crashes.

I am looking to see if there is problem with the Report, rather
than with the query. So, do you have a problem with the
DSUM when it is calculated outside the report? Do you
have a problem with the DSUM if it is calculated in a report
format event, and the value then written to a label caption?
Do you have a problem if you replace DSUM with a User
Defined Function in a standard code module?

This is more of a problem with reports than with forms.
Access is dynamically rewriting and running that DSUM
for every record, and if there is a name clash at runtime,
that might be a source of confusion for Jet.


(david)

This is Access 2003. There are, indeed, other objects with the same name. In
fact, on one of the forms (not related to the report in question, but using
the same underlying query), there is a control called ShipNet. However, this
is also true of several other controls on that form, and there is no failure.
See also my notes in response to Allen.

Brian, I didn't answer before because the potential answer
was too complex, but now that I see Allen has given a proper
answer, let me add that

In Access 2000, any error can cause Access to crash, Access
just doesn't catch errors reliably. I haven't spent enough time
in A2K2 or A2K3 to know how much this is still true.

Also look out for any other control or object called ShipNet.
You can get errors if there are other Access objects with the
same name.

(david)

On a report section footer, I have text boxes that each calculate a single
value as follows:

InventoryArrived
=Val(nz(DSum("[ArriveNet]","[LookupInventoryCombinedNet]","[CommodityID] =
"
& [CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

InventoryShipped
=Val(nz(DSum("[ShipNet]","[LookupInventoryCombinedNet]","[CommodityID] = "
&
[CommodityID] & " and [UnitID] = " & [UnitID] & " and [Active]"),0))

LookupInventoryCombinedNet is the name of a query. ArriveNet and ShipNet
are
two fields in that query. Both are calculated approximately the same in
the
query: ArriveNet = ArriveIn - ArriveOut; ShipNet = ShipIn - ShipOut.

The anomaly is that the "ShipNet" in the InventoryShipped text box causes
Access to crash when running the report. If I change its name to
"ShipNets",
"ABCDEFGH", or anything else in both the query and the calculated control
source above, it works.

Now, what is so special about the field name "ShipNet"? I have
ascertained/tried the following:

-It also works if I leave it as the same name but remove the "and Active"
filter at the end of the DSum.
-"ShipNet" does not appear twice in the query.
-The query runs fine by itself.
-I have compacted/repaired many times.
-I have removed this field from the query, saved the query, then recreated
the field.
-The crash occurs also if I create another query that Sums this field, but
again ONLY if I use ShipNet as the field name.
-I have copied the SQL, deleted the query, and recreated the query using
the
same SQL.

The only hint I have to offer is that this query is at the top of a chain
of
queries, each built on the one before:

Query6 (this one), whose source is Query5, whose source is Query4, a union
query of six parallel Query3's, each of which is based on Query2, whose
source is Query1.

However, I can run these at any level run just fine and plenty fast
enough,
and all other fields work just fine except ShipNet.

 

[Guest]

Thanks, Allen.

In this case, each of the underlying queries is guaranteed to not have
duplicates anyway becuase they already group on the appropriate fields, so
UNION ALL is unnecessary.

This application was really the first where I used union queries in any
significant fashion as I began migrating away from domain aggregates to make
things run more efficiently. In the report in question, this approach changed
the time it took to run the report from 2 minutes to 6 seconds!) Live and
learn, I guess...

Thanks again for all the help.

UNION de-duplicates. UNION ALL returns all records.
It follows that UNION ALL is simpler/more efficient to execute, so that's
why it's generally recommended.

The fact that the UNION works where UNION ALL does not suggests that the
problem is addressed by forcing a comparsion between the values (i.e. the
comparsion that is used for de-duplicating.)

Again, the 6 lower level queries are involved here, so there is a good
chance of JET confusing the data types, esp. if your queries really do
contain the wildcard or any calculated fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well, this all kind of got me searching through all the underlying queries
in detail, and I seem to have found a way to resolve this. Perhaps you can
just explain to me why this happens. Maybe I'm way out on a limb here, but
I
have a six-way union query separated from the query in question by an
intervening query. Here is the six-way union query (see my notes below
it):

SELECT LookupInventoryArriveIn.*
FROM LookupInventoryArriveIn;

UNION SELECT LookupInventoryArriveOut.*
FROM LookupInventoryArriveOut;

UNION SELECT LookupInventoryAdjustIn.*
FROM LookupInventoryAdjustIn;

UNION SELECT LookupInventoryAdjustOut.*
FROM LookupInventoryAdjustOut;

UNION SELECT LookupInventoryShipIn.*
FROM LookupInventoryShipIn;

UNION SELECT LookupInventoryShipOut.*
FROM LookupInventoryShipOut;

Based on some reading-up I did on database efficiency that seemed to
indicate that UNION ALL was preferable to just UNION, I originally had all
the UNION statements as UNION ALL. Removing the ALL seems to have resolved
the immediate problem, but:

1. Why?
2. What am I potentially doing to my application by removing the ALL?

Hi Brian

You've obviously done some good digging to understand what JET is choking
on
here. I'm assuming that LookupInventoryCombinedNet is the UNION query you
referred to earlier, which is based on other queries. What we are seeing
therefore is the tip of the iceberg, with lots of other stuff going on in
the lower level queries.

It's fairly easy to crash or confuse JET. It often crashes if I use a
subquery in a lower level query, and then try to use that field in a
higher
level query (e.g. in a JOIN.) Null in the yes/no field also causes
crashes.
The query-to-complex message is often caused by type mismatches, which
can
occur with UNION queries, undeclared parameters, incorrect implicit
typecasts, and so on.

The fact that the query works when you ArriveNet field is included
suggests
that summing this field also causes JET to take a different execution
path,
which ends up handling ShipNet correctly as well. The default name
(SumOfShipNet) might hint at the same issue, or at a name confusion. I'm
not
aware of the specifics of what david@epsomdotcomdotau was referring to,
but
his comments are always very good value, so there may be an issue there.

Importing the data into a new mdb (with Name AutoCorrupt turned off
presumably) was a good move.

I'm guessing that the source of the issue is the way the data is
generated
by the lower level queries. Typecasting there, and avoiding nulls in the
yes/no results could make a difference when you try to use those results
in
your higher level query.

Thanks, Allen. This thing is driving me crazy today.

#1, 2, & 4: Already the way you suggest
#3 I have duplicated the problem with Active left out
#5 Haven't specifically tried this, but simply changing the name of the
field resolves the problem (a solution that I won't use, at least not
yet,
because I really don't understand why); furthermore, parallel fields
(e.g.
ArriveNet, coming through the same sequence of queries to generate the
total)
in the same report do not cause the crash.

After creating a new DB & importing everything (to isolate potential
corruption issues), I created a simple Sum query against the underlying
query. Note that this works consistently:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ArriveNet)
AS ArriveNet, Sum(LookupInventoryCombinedNet.ShipNet) AS ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

but this generates a "Query is too complex" error (it is actually
simpler
because I just removed the ArriveNet field from the query):

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ShipNet)
AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

and this crashes (allowed AS to default to SumOfShipNet instead of
specifying ABC)

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ShipNet)
AS
SumOfShipNet
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

This results in "Query too complex" when run from within the query
builder
but crashes if the query is saved and then double-clicked from the
database
window:

SELECT LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID,
Sum(LookupInventoryCombinedNet.ShipNet)
AS
ABC
FROM LookupInventoryCombinedNet
GROUP BY LookupInventoryCombinedNet.CommodityID,
LookupInventoryCombinedNet.UnitID;

:

Brian, there's a lot of stuff going on here, but hopefully some of
these
suggestions will help.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact

2. Presumably Active is a Yes/No field? Just to be sure Access
understands
this, bracket it. Won't hurt to bracket the other 2 phrases in the
Criteria
argument of DSum() as well, i.e.:
("[CommodityID] = " & [CommodityID] & ") And ([UnitID] = " & [UnitID]
&
")
And ([Active])"

3. As you know, a yes/no field is 2-state only in JET, i.e. it cannot
store
a Null. But if the yes/no field comes from a table that is on the
outer
side
of a join, a yes/no field in a query can be Null. I've actually seen
this
crash Access (shut down by Windows). Any chance the table that Active
is
drawn from is on the outer side of a join? If so, you could probably
solve
the problem with:
SELECT Nz([Table1].[Active], False) AS IsActive, ...

4. Presumably CommodityID and UnitID are both Number fields, and there
is
no
chance of them being Null (e.g. result of outer join).

5. Might need some explicit typecasting in the UNION or other stacked
queries, though the expressions you have suggest you probably
understand
how to do that.