<ack!!!> simplify to resolve Query too complex <ack!!!>

[jlute]

Yesterday I posted a request to simplify an IIf and Doug Steele (any
relation to Marion L. Steele of Amherst, OH fame?) helped to go from
this:

UNEstNtWt: IIf([tblFGUOM].[UOMType]="WT",Round([UNNtWt]*0.05+[UNNtWt],
6),IIf([tblFGUOM].[UOMType]="VOL",Round([UNNtWt],6)))

to this:

UNEstNtWt: Switch([tblFGUOM].[UOMType]="WT",Round([UNNtWt]*0.05+
[UNNtWt],6),[tblFGUOM]­.[UOMType]="VOL",Round([UNNtWt],6))

While this is more simple in my mind - Access doesn't think so. The
reason I posted this problem is because I've got a VERY complex query
and this "little" seemingly benign IIf statement has taken things into
the Query Too Complex Zone.

I did some more query design and whittled things down to this:
UNEstNtWt: Switch([UOMType]="WT",Round([UNNtWt]*0.05+[UNNtWt],6),
[UOMType]="VOL",Round([UNNtWt],6))

Still too complex. I even tried this:

UNEstNtWt: IIf([UOMType]="WT",Round([UNNtWt]*0.05+[UNNtWt],6) Or
[UOMType]="VOL",Round([UNNtWt],6))

I tried removing 1 joined subquery and 2 joined tables as well as
about 20 other fields - compacted and repaired - I then got the
"System resource exceeded" deal.

!!!!!

The query has about 72 fields. When I leave them ALL in and everything
else as is and then change the IIf to this:
UNEstNtWt: Round([UNNtWt]*0.05+[UNNtWt],6)
then everything is fine!

I absolutely NEED to distinguish when a UOMType is WT or VOL and then
calculate accordingly but this makes things too complex and it doesn't
make sense considering my test of removing the stuff as noted above.

WHAT THE HECK!!!???

The "solution" to a Query too complex is to REMOVE fields, tables,
etc. I did that to no end! WHY is this one little IIf creating such
havoc?

Any help is greatly appreciated. This is without doubt the most
aggravating problem I've ever had.

THANKS!

 

[jlute]

I just stripped away everything about the query except what's involved
with the problem here and the query ran (wow!) _BUT_ the calculation
returned "-1" for EVERY record with a UOMType "VOL" value! It
calculated properly for UOMType "WT" values!

Here is the query's SQL:
SELECT qryPKProfilesAssociationsPKWTsFGsULDims.txtProfileID,
tblProfiles.Version,
qryPKProfilesAssociationsPKWTsFGsULDims.UnitCount,
qryPKProfilesAssociationsPKWTsFGsULDims.SubUnitCount,
qryPKProfilesAssociationsPKWTsFGsULDims.UnitSize,
qryPKProfilesAssociationsPKWTsFGsULDims.UnitUOM,
qryPKProfilesAssociationsPKWTsFGsULDims.UOMType,
qryPKProfilesAssociationsPKWTsFGsULDims.UnitSizeCt,
qryPKProfilesAssociationsPKWTsFGsULDims.UnitSizeCtc,
qryPKProfilesAssociationsPKWTsFGsULDims.UnitSizec,
qryPKProfilesAssociationsPKWTsFGsULDims.Densitylbgal,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPRODWtlb AS
SUBUNPRODWt,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPRODWtlbTotal AS
SUBUNPRODWtTotal,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPRODWtlb AS PRODWt,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPPRODWtlbTotal AS
PRODWtTotal,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPKWtlbTotal AS
SUBUNPKWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWtlb
AS UNPKWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWtlbTotal
AS UNPKWtTotal, Round([UnitSizec]*[lbConvFactor],6) AS UNNtWtConvlb,
Round([UnitSizec]*[galConvFactor],6) AS UNNtWtConvgal,
IIf(IsNull([SubUnitCount]),[UnitCount]*[PRODWt],
[UnitCount]*[SUBUNPRODWtTotal]) AS UNPRODNtWt, Nz([UNNtWtConvlb],
[UNNtWtConvgal]*[Densitylbgal]) AS UNNtWtNz, Nz([UNPRODNtWt],
[UNNtWtNz]) AS UNNtWtNzc, Round([UNNtWtNzc],6) AS UNNtWt,
IIf([UOMType]="WT",Round([UNNtWt]*0.05+[UNNtWt],6) Or
[UOMType]="VOL",Round([UNNtWt],6)) AS UNEstNtWt
FROM (qryPKProfilesAssociationsPKWTsFGsULDims INNER JOIN tblProfiles
ON qryPKProfilesAssociationsPKWTsFGsULDims.txtProfileID =
tblProfiles.txtProfileID) LEFT JOIN qryPKProfilesAssociationsPKWTsFGs
ON qryPKProfilesAssociationsPKWTsFGsULDims.txtProfileID =
qryPKProfilesAssociationsPKWTsFGs.txtProfileID;

Any ideas why "-1" would result for WT values???

THANKS!!!

 

[jlute]

My apologies. I meant to say:

"-1" for EVERY record with a UOMType "WT" value! It
calculated properly for UOMType "VOL" values!

 

[jlute]

OK. I corrected the statement to Doug's suggestion:
UNEstNtWt: Switch([tblFGUOM].[UOMType]="WT",Round([UNNtWt]*0.05+
[UNNtWt],6),[tblFGUOM]­.[UOMType]="VOL",Round([UNNtWt],6))

This is behaving. Obviously, my "or" setup was faulty.

Now I'm back to square one! Query too complex / System resource
exceeded. Simplify to this and all is good:
UNEstNtWt: Round([UNNtWt]*0.05+[UNNtWt],6)

Sorry for all of this craziness. I'm trying to work it out but not
doing so well. I'll leave it here and wait for any replies.