Query to complex?

  • Thread starter Thread starter jo
  • Start date Start date
J

jo

Hi I am getting a "Query to complex" when I have added another Expression to
my query. Actually the query accepts this and opens, but when I open the form
that has been produced from this query thats when I get Query to Complex..
Does any one know a reason for this and how it can be solved?
The expression is this:
TickBalancedAngle: IIf([2BalancedAngle]<=[PercentAngle],"Passed","Failed")
 
Post the complete SQL of your query. Display the query in SQL view, select
click Ctrl-A in the query text, copy and paste into a message to this NG.
-TedMi
 
Are both [2BalancedAngle] and [PercentAngle] numeric? Is there any
possibility that either field stores a null?
 
It long!! This query works out a lot of sums Good Luck!:
SELECT [Enter Talyform Data].CalculationID, [Enter Talyform Data].Type,
[Enter Talyform Data].[Date:], Date() AS TodaysDate,
IIf([TodaysDate]-[Date:]>10,1,0) AS ExpireDate, [Enter Talyform
Data].Channel, [Enter Talyform Data].[Inspection By], [Enter Talyform
Data].RollerDia, IIf([PGI]=Yes,[Bi/2]+[RollerDia],[RollerPos]) AS RP, [Enter
Talyform Data].RollerPos, [Enter Talyform Data].ri1,
IIf([PGI]=Yes,[RP]-[x-datum1],[x-datum1]) AS xd1, [Enter Talyform
Data].[x-datum1], [Enter Talyform Data].Vri1, [Enter Talyform Data].RaDi1,
[Enter Talyform Data].ri2, IIf([PGI]=Yes,[x-datum2]-[RP],[x-datum2]) AS xd2,
[Enter Talyform Data].[x-datum2], [Enter Talyform Data].Vri2, [Enter Talyform
Data].RaDi2, [RP]+[Tolerances]![OffSet]-[RollerDia]-[Tolerances]![Bi/2] AS
Actpos1, [xd1]+[Tolerances]![OffSet]-[ActPos1] AS x1, [x1]/([Enter Talyform
Data]![ri1]+[Tolerances]![DSN/2]) AS Angle1,
Atn([Angle1]/Sqr(-[Angle1]*[Angle1]+1)) AS [@Sin],
[Tolerances]![Angle]*0.0174532951 AS Radians, ([@sin]-[Radians])*1000 AS
Alpha1, [xd2]-[Tolerances]![OffSet]+[ActPos1] AS x2, [x2]/([Enter Talyform
Data]![ri2]+[Tolerances]![DSN/2]) AS Angle2,
Atn([Angle2]/Sqr(-[Angle2]*[Angle2]+1)) AS [@Sin2], ([@Sin2]-[Radians])*1000
AS Alpha2, [Enter Talyform Data]![ri1]-[Tolerances]![NomRad] AS RaDTri1,
[Enter Talyform Data]![ri2]-[Tolerances]![NomRad] AS RaDTri2, [Enter Talyform
Data].[2RollerDia], IIf([PGI]=Yes,[Bi/2]+[2RollerDia],[2RollerPos]) AS 2RP,
[Enter Talyform Data].[2RollerPos], [Enter Talyform Data].[2ri1],
IIf([PGI]=Yes,[2RP]-[2x-datum1],[2x-datum1]) AS 2xd1, [Enter Talyform
Data].[2x-datum1], [Enter Talyform Data].[2Vri1], [Enter Talyform
Data].[2RaDi1], [Enter Talyform Data].[2ri2],
IIf([PGI]=Yes,[2x-datum2]-[2RP],[2x-datum2]) AS 2xd2, [Enter Talyform
Data].[2x-datum2], [Enter Talyform Data].[2Vri2], [Enter Talyform
Data].[2RaDi2], [2RP]+[Tolerances]![OffSet]-[2RollerDia]-[Tolerances]![Bi/2]
AS 2ActPos, [2xd1]+[Tolerances]![OffSet]-[2ActPos] AS 2x1,
[2x1]/([2ri1]+[Tolerances]![DSN/2]) AS 2Angle1,
Atn([2Angle1]/Sqr(-[2Angle1]*[2Angle1]+1)) AS [2@Sin],
[Tolerances]![Angle]*0.0174532951 AS 2Radians, ([2@Sin]-[2Radians])*1000 AS
2Alpha1, ([2@Sin2]-[2Radians])*1000 AS 2Alpha2,
[2xd2]-[Tolerances]![OffSet]+[2ActPos] AS 2x2,
[2x2]/([2ri2]+[Tolerances]![DSN/2]) AS 2Angle2,
Atn([2Angle2]/Sqr(-[2Angle2]*[2Angle2]+1)) AS [2@Sin2],
[2ri1]-[Tolerances]![NomRad] AS 2RaDTri1, [2ri2]-[Tolerances]![NomRad] AS
2RaDTri2, [2Alpha1]+[2Alpha2] AS 22Alpha, IIf([StandardRoom]=Yes And
[2Vri1]=0,"Passed",IIf([2Vri1]=0,"Failed",IIf([Grinding]=Yes And
[2Vri1]<=6,"Passed",IIf([2Vri1]<=[Tolerances]![Vri],"PASSED","FAILED")))) AS
Tick2Vri1, IIf([StandardRoom]=Yes And
[Vri2]=0,"Passed",IIf([2Vri2]=0,"Failed",IIf([Grinding]=Yes And
[2Vri2]<=6,"Passed",IIf([2Vri2]<=[Tolerances]![Vri],"PASSED","FAILED")))) AS
Tick2Vri2, IIf([Grinding]=Yes And [2RaDi1]=0,"Passed",IIf([Honed]=Yes And
[2RaDi1]=0,"Failed",IIf([2RaDi1]<=[Tolerances]![RaDi],"PASSED","FAILED"))) AS
Tick2RaDi1, IIf([Grinding]=Yes And [2RaDi2]=0,"Passed",IIf([Honed]=Yes And
[2RaDi2]=0,"Failed",IIf([2RaDi2]<=[Tolerances]![RaDi],"PASSED","FAILED"))) AS
Tick2RaDi2, IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2RollerDia]=0,"Failed",IIf([2RaDTri1]<=[Tolerances]![Ri
MaxTol] And [2RaDTri1]>=[Tolerances]![Ri MinTol],"PASSED","FAILED"))) AS
Tick2RaDTri1, IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2RollerDia]=0,"Failed",IIf([2RaDTri2]<=[Tolerances]![Ri
MaxTol] And [2RaDTri2]>=[Tolerances]![Ri MinTol],"PASSED","FAILED"))) AS
Tick2RaDTri2, IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2Alpha1]=0,"Failed",IIf([2Alpha1]<=[Tolerances]![MaxAlpha]
And [2Alpha1]>=[Tolerances]![MinAlpha],"PASSED","FAILED"))) AS Tick2Angle1,
IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2RollerDia]=0,"Failed",IIf([2Alpha2]<=[Tolerances]![MaxAlpha]
And [2Alpha2]>=[Tolerances]![MinAlpha],"PASSED","FAILED"))) AS Tick2Angle2,
IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2RollerDia]=0,"Failed",IIf([22Alpha]>=[Tolerances]![MinAlpha]
And [22Alpha]<=[Tolerances]![2Alpha],"PASSED","FAILED"))) AS Tick22Alpha,
Tolerances.[DSN/2], [Alpha1]+[Alpha2] AS 2Alpha, IIf([StandardRoom]=Yes And
[Vri1]=0,"Passed",IIf([Vri1]=0," ",IIf([Grinding]=Yes And
[Vri1]<=6,"Passed",IIf([Vri1]<=[Tolerances]![Vri],"PASSED","FAILED")))) AS
TickVri1, IIf([StandardRoom]=Yes And [Vri2]=0,"Passed",IIf([Vri2]=0,"
",IIf([Grinding]=Yes And [Vri2]<=6,"Passed",IIf([Enter Talyform
Data]![Vri2]<=[Tolerances]![Vri],"PASSED","FAILED")))) AS TickVri2,
IIf([Grinding]=Yes And [RaDi1]=0,"Passed",IIf([Honed]=Yes And
[RaDi1]=0,"Failed",IIf([Enter Talyform
Data]![RaDi1]<=[Tolerances]![RaDi],"PASSED","FAILED"))) AS TickRaDi1,
IIf([Grinding]=Yes And [RaDi2]=0,"Passed",IIf([Honed]=Yes And
[RaDi2]=0,"Failed",IIf([Enter Talyform
Data]![RaDi2]<=[Tolerances]![RaDi],"PASSED","FAILED"))) AS TickRaDi2,
IIf([RaDTri1]<=[Tolerances]![Ri MaxTol] And [RaDTri1]>=[Tolerances]![Ri
MinTol],"PASSED","FAILED") AS TickRaDTri1, IIf([RaDTri2]<=[Tolerances]![Ri
MaxTol] And [RaDTri2]>=[Tolerances]![Ri MinTol],"PASSED","FAILED") AS
TickRaDTri2, IIf([Alpha1]<=[Tolerances]![MaxAlpha] And
[Alpha1]>=[Tolerances]![MinAlpha],"PASSED","FAILED") AS TickAngle1,
IIf([Alpha2]<=[Tolerances]![MaxAlpha] And
[Alpha2]>=[Tolerances]![MinAlpha],"PASSED","FAILED") AS TickAngle2,
IIf([2Alpha]>=[Tolerances]![MinAlpha] And
[2Alpha]<=[Tolerances]![2Alpha],"PASSED","FAILED") AS Tick2Alpha,
Tolerances.[Ri MaxTol], Tolerances.[Ri MinTol], Tolerances.MaxAlpha,
Tolerances.MinAlpha, Tolerances.Vri, Tolerances.RaDi,
IIf([TickVri1]="passed",IIf([TickVri2]="passed",IIf([TickRaDi1]="passed",IIf([TickRaDi2]="passed",IIf([TickRaDTri1]="passed",IIf([TickRaDTri2]="passed",IIf([TickAngle1]="passed",IIf([TickAngle2]="passed",IIf([Tick2Alpha]="passed","Approved","Not
Approved"))))))))) AS Ring1a,
IIf([TickVDi]="passed",IIf([TickV3Di]="passed",IIf([TickTSLL]="passed",IIf([TickTSLM]="passed",IIf([TickTSLM]="passed",IIf([TickTSLH]="passed",IIf([TickTSLMax]="passed","Approved","Not
Approved"))))))) AS Ring1b, IIf([Ring1a]="Approved" And
[Ring1b]="Approved","Approved","Not Approved") AS Ring1,
IIf([Tick2Vri1]="passed",IIf([Tick2Vri2]="passed",IIf([Tick2RaDi1]="passed",IIf([Tick2RaDi2]="passed",IIf([Tick2RaDTri1]="passed",IIf([Tick2RaDTri2]="passed",IIf([Tick2Angle1]="passed",IIf([Tick2Angle2]="passed",IIf([Tick22Alpha]="passed","Approved","Not
Approved"))))))))) AS Ring2a,
IIf([Tick2VDi]="passed",IIf([Tick2V3Di]="passed",IIf([Tick2TSLL]="passed",IIf([Tick2TSLM]="passed",IIf([Tick2TSLM]="passed",IIf([Tick2TSLH]="passed",IIf([Tick2TSLMax]="passed","Approved","Not
Approved"))))))) AS Ring2b, IIf([StandardRoom]=Yes,"
",IIf([2RollerDia]=0,"2nd Ring Data Required",IIf([Ring2a]="Approved" And
[Ring2b]="Approved","Approved","Not Approved"))) AS Ring2,
IIf([StandardRoom]=Yes," ",IIf([Ring1]="Not Approved","Not
approved",IIf([Ring2]="Not approved","Not Approved",IIf([Ring2]="2nd Ring
Data required","Not Approved ","Approved")))) AS Ring2c,
IIf([Ring2c]="Approved","Routine Approved","Not Approved") AS RingAppr,
IIf([First Off]=Yes,IIf([ExpireDate]=0,IIf([RingAppr]="Not Approved","Inform
Line Leader/Setter"," "))) AS InfSet, [Enter Talyform
Data].[Station/Machine], [Enter Talyform Data].StandardRoom, [Enter Talyform
Data].Grinding, [Enter Talyform Data].Honed, [Enter Talyform Data].[First
Off], [Enter Talyform Data].Setting, [Enter Talyform Data].[Routine Check],
[Enter Talyform Data].VDi, [Enter Talyform Data].V3Di, [Enter Talyform
Data].[TSL L], [Enter Talyform Data].[TSL M], [Enter Talyform Data].[TSL H],
[Enter Talyform Data].[TSL max], [Enter Talyform Data].[2VDi], [Enter
Talyform Data].[2V3Di], [Enter Talyform Data].[2TSL L], [Enter Talyform
Data].[2TSL M], [Enter Talyform Data].[2TSL H], [Enter Talyform Data].[2TSL
max], Tolerances.VDi, Tolerances.V3Di, Tolerances.[TSL L], Tolerances.[TSL
H], Tolerances.[TSL M], Tolerances.[TSL Max], IIf([StandardRoom]=Yes And
[Enter Talyform Data.VDi]=0,"Passed",IIf([Enter Talyform
Data.VDi]=0,"Failed",IIf([Enter Talyform
Data.VDi]<=[Tolerances]![VDi],"PASSED","FAILED"))) AS TickVDi,
IIf([StandardRoom]=Yes And [Enter Talyform Data.V3Di]=0,"Passed",IIf([Enter
Talyform Data.V3Di]=0,"Failed",IIf([Enter Talyform
Data.V3Di]<=[Tolerances]![V3Di],"PASSED","FAILED"))) AS TickV3Di,
IIf([StandardRoom]=Yes And [Enter Talyform Data.TSL L]=0,"Passed",IIf([Enter
Talyform Data.TSL L]=0,"Failed",IIf([Enter Talyform Data.TSL
L]<=[Tolerances.TSL L],"PASSED","FAILED"))) AS TickTSLL, IIf([Grinding]=Yes
And [2RaDi1]=0,"Passed",IIf([Enter Talyform Data.TSL M]=0,"Failed",IIf([Enter
Talyform Data.TSL M]<=[Tolerances.TSL M],"PASSED","FAILED"))) AS TickTSLM,
IIf([Grinding]=Yes And [2RaDi1]=0,"Passed",IIf([Enter Talyform Data.TSL
H]=0,"Failed",IIf([Enter Talyform Data.TSL H]<=[Tolerances.TSL
H],"PASSED","FAILED"))) AS TickTSLH, IIf([Grinding]=Yes And
[2RaDi1]=0,"Passed",IIf([Enter Talyform Data.TSL max]=0,"Failed",IIf([Enter
Talyform Data.TSL max]<=[Tolerances.TSL max],"PASSED","FAILED"))) AS
TickTSLmax, IIf([StandardRoom]=Yes And [Enter Talyform
Data.2VDi]=0,"Passed",IIf([2VDi]=0,"Failed",IIf([Enter Talyform
Data.2VDi]<=[Tolerances]![VDi],"PASSED","FAILED"))) AS Tick2VDi,
IIf([StandardRoom]=Yes And [Enter Talyform
Data.2V3Di]=0,"Passed",IIf([2V3Di]=0,"Failed",IIf([Enter Talyform
Data.2V3Di]<=[Tolerances]![V3Di],"PASSED","FAILED"))) AS Tick2V3Di,
IIf([StandardRoom]=Yes And [Enter Talyform Data.2TSL L]=0,"Passed",IIf([2TSL
L]=0,"Failed",IIf([Enter Talyform Data.2TSL L]<=[Tolerances.TSL
L],"PASSED","FAILED"))) AS Tick2TSLL, IIf([Grinding]=Yes And
[2RaDi1]=0,"Passed",IIf([2TSL M]=0,"Failed",IIf([Enter Talyform Data.2TSL
M]<=[Tolerances.TSL M],"PASSED","FAILED"))) AS Tick2TSLM, IIf([Grinding]=Yes
And [2RaDi1]=0,"Passed",IIf([2TSL H]=0,"Failed",IIf([Enter Talyform Data.2TSL
H]<=[Tolerances.TSL H],"PASSED","FAILED"))) AS Tick2TSLH, IIf([Grinding]=Yes
And [2RaDi1]=0,"Passed",IIf([2TSL max]=0,"Failed",IIf([Enter Talyform
Data.2TSL max]<=[Tolerances.TSL max],"PASSED","FAILED"))) AS Tick2TSLmax,
[Enter Talyform Data].OK, Tolerances.Type, [Enter Talyform Data].PGI,
IIf([RollerDia] And [2RollerDia]>0,"Passed","Failed") AS 2ringchk,
IIf([ExpireDate]=0 And [RingAppr]="Routine Approved" And [First
Off]=Yes,"FIRST OFF APPROVAL") AS Stamp, [Enter Talyform Data].DiamondRollNo,
[Enter Talyform Data].ManufacturersDiamondNo, [Tolerances]![MaxAlpha]*0.25 AS
PercentAngle, Abs([Alpha1]-[Alpha2]) AS BalancedAngle,
IIf([BalancedAngle]<=[PercentAngle],"Passed","Failed") AS TickBalancedAngle,
Abs([2Alpha1]-[2Alpha2]) AS BalancedAngle2,
IIf([BalancedAngle2]<=[PercentAngle],"Passed","Failed") AS TickBalancedAngle2
FROM [Enter Talyform Data] INNER JOIN Tolerances ON [Enter Talyform
Data].Type = Tolerances.Type;


TedMi said:
Post the complete SQL of your query. Display the query in SQL view, select
click Ctrl-A in the query text, copy and paste into a message to this NG.
-TedMi

jo said:
Hi I am getting a "Query to complex" when I have added another Expression
to
my query. Actually the query accepts this and opens, but when I open the
form
that has been produced from this query thats when I get Query to Complex..
Does any one know a reason for this and how it can be solved?
The expression is this:
TickBalancedAngle: IIf([2BalancedAngle]<=[PercentAngle],"Passed","Failed")
Click to expand...


.
Click to expand...
 
Hi, yes they are both numeric PercentAngle is a sum that works out 25% of
another field and 2BalancedAngle is the result of another field.

Duane Hookom said:
Are both [2BalancedAngle] and [PercentAngle] numeric? Is there any
possibility that either field stores a null?

--
Duane Hookom
Microsoft Access MVP


jo said:
Hi I am getting a "Query to complex" when I have added another Expression to
my query. Actually the query accepts this and opens, but when I open the form
that has been produced from this query thats when I get Query to Complex..
Does any one know a reason for this and how it can be solved?
The expression is this:
TickBalancedAngle: IIf([2BalancedAngle]<=[PercentAngle],"Passed","Failed")
Click to expand...
Click to expand...
 
That query is crazy. I try to never use an alias in another calculation in a
query. For instance, you create RP and then use it in another expression.

I would probably simplify the query by possibly first using a query to
create the RP and other columns. Then create another query that uses RP as a
field. I would also look at the datasheet of the first query to make sure
that if RP is supposed to be numeric that it is displayed right-aligned.

I would not create aliases with names beginning with symbols like "@".

Any time I see ")))))))" it gives me the shivers. You have way too much to
maintain in expressions in queries. I would create some basic business
calculation functions so they can be simplified and commented.


--
Duane Hookom
Microsoft Access MVP


jo said:
It long!! This query works out a lot of sums Good Luck!:
SELECT [Enter Talyform Data].CalculationID, [Enter Talyform Data].Type,
[Enter Talyform Data].[Date:], Date() AS TodaysDate,
IIf([TodaysDate]-[Date:]>10,1,0) AS ExpireDate, [Enter Talyform
Data].Channel, [Enter Talyform Data].[Inspection By], [Enter Talyform
Data].RollerDia, IIf([PGI]=Yes,[Bi/2]+[RollerDia],[RollerPos]) AS RP, [Enter
Talyform Data].RollerPos, [Enter Talyform Data].ri1,
IIf([PGI]=Yes,[RP]-[x-datum1],[x-datum1]) AS xd1, [Enter Talyform
Data].[x-datum1], [Enter Talyform Data].Vri1, [Enter Talyform Data].RaDi1,
[Enter Talyform Data].ri2, IIf([PGI]=Yes,[x-datum2]-[RP],[x-datum2]) AS xd2,
[Enter Talyform Data].[x-datum2], [Enter Talyform Data].Vri2, [Enter Talyform
Data].RaDi2, [RP]+[Tolerances]![OffSet]-[RollerDia]-[Tolerances]![Bi/2] AS
Actpos1, [xd1]+[Tolerances]![OffSet]-[ActPos1] AS x1, [x1]/([Enter Talyform
Data]![ri1]+[Tolerances]![DSN/2]) AS Angle1,
Atn([Angle1]/Sqr(-[Angle1]*[Angle1]+1)) AS [@Sin],
[Tolerances]![Angle]*0.0174532951 AS Radians, ([@sin]-[Radians])*1000 AS
Alpha1, [xd2]-[Tolerances]![OffSet]+[ActPos1] AS x2, [x2]/([Enter Talyform
Data]![ri2]+[Tolerances]![DSN/2]) AS Angle2,
Atn([Angle2]/Sqr(-[Angle2]*[Angle2]+1)) AS [@Sin2], ([@Sin2]-[Radians])*1000
AS Alpha2, [Enter Talyform Data]![ri1]-[Tolerances]![NomRad] AS RaDTri1,
[Enter Talyform Data]![ri2]-[Tolerances]![NomRad] AS RaDTri2, [Enter Talyform
Data].[2RollerDia], IIf([PGI]=Yes,[Bi/2]+[2RollerDia],[2RollerPos]) AS 2RP,
[Enter Talyform Data].[2RollerPos], [Enter Talyform Data].[2ri1],
IIf([PGI]=Yes,[2RP]-[2x-datum1],[2x-datum1]) AS 2xd1, [Enter Talyform
Data].[2x-datum1], [Enter Talyform Data].[2Vri1], [Enter Talyform
Data].[2RaDi1], [Enter Talyform Data].[2ri2],
IIf([PGI]=Yes,[2x-datum2]-[2RP],[2x-datum2]) AS 2xd2, [Enter Talyform
Data].[2x-datum2], [Enter Talyform Data].[2Vri2], [Enter Talyform
Data].[2RaDi2], [2RP]+[Tolerances]![OffSet]-[2RollerDia]-[Tolerances]![Bi/2]
AS 2ActPos, [2xd1]+[Tolerances]![OffSet]-[2ActPos] AS 2x1,
[2x1]/([2ri1]+[Tolerances]![DSN/2]) AS 2Angle1,
Atn([2Angle1]/Sqr(-[2Angle1]*[2Angle1]+1)) AS [2@Sin],
[Tolerances]![Angle]*0.0174532951 AS 2Radians, ([2@Sin]-[2Radians])*1000 AS
2Alpha1, ([2@Sin2]-[2Radians])*1000 AS 2Alpha2,
[2xd2]-[Tolerances]![OffSet]+[2ActPos] AS 2x2,
[2x2]/([2ri2]+[Tolerances]![DSN/2]) AS 2Angle2,
Atn([2Angle2]/Sqr(-[2Angle2]*[2Angle2]+1)) AS [2@Sin2],
[2ri1]-[Tolerances]![NomRad] AS 2RaDTri1, [2ri2]-[Tolerances]![NomRad] AS
2RaDTri2, [2Alpha1]+[2Alpha2] AS 22Alpha, IIf([StandardRoom]=Yes And
[2Vri1]=0,"Passed",IIf([2Vri1]=0,"Failed",IIf([Grinding]=Yes And
[2Vri1]<=6,"Passed",IIf([2Vri1]<=[Tolerances]![Vri],"PASSED","FAILED")))) AS
Tick2Vri1, IIf([StandardRoom]=Yes And
[Vri2]=0,"Passed",IIf([2Vri2]=0,"Failed",IIf([Grinding]=Yes And
[2Vri2]<=6,"Passed",IIf([2Vri2]<=[Tolerances]![Vri],"PASSED","FAILED")))) AS
Tick2Vri2, IIf([Grinding]=Yes And [2RaDi1]=0,"Passed",IIf([Honed]=Yes And
[2RaDi1]=0,"Failed",IIf([2RaDi1]<=[Tolerances]![RaDi],"PASSED","FAILED"))) AS
Tick2RaDi1, IIf([Grinding]=Yes And [2RaDi2]=0,"Passed",IIf([Honed]=Yes And
[2RaDi2]=0,"Failed",IIf([2RaDi2]<=[Tolerances]![RaDi],"PASSED","FAILED"))) AS
Tick2RaDi2, IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2RollerDia]=0,"Failed",IIf([2RaDTri1]<=[Tolerances]![Ri
MaxTol] And [2RaDTri1]>=[Tolerances]![Ri MinTol],"PASSED","FAILED"))) AS
Tick2RaDTri1, IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2RollerDia]=0,"Failed",IIf([2RaDTri2]<=[Tolerances]![Ri
MaxTol] And [2RaDTri2]>=[Tolerances]![Ri MinTol],"PASSED","FAILED"))) AS
Tick2RaDTri2, IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2Alpha1]=0,"Failed",IIf([2Alpha1]<=[Tolerances]![MaxAlpha]
And [2Alpha1]>=[Tolerances]![MinAlpha],"PASSED","FAILED"))) AS Tick2Angle1,
IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2RollerDia]=0,"Failed",IIf([2Alpha2]<=[Tolerances]![MaxAlpha]
And [2Alpha2]>=[Tolerances]![MinAlpha],"PASSED","FAILED"))) AS Tick2Angle2,
IIf([StandardRoom]=Yes And
[2RollerDia]=0,"Passed",IIf([2RollerDia]=0,"Failed",IIf([22Alpha]>=[Tolerances]![MinAlpha]
And [22Alpha]<=[Tolerances]![2Alpha],"PASSED","FAILED"))) AS Tick22Alpha,
Tolerances.[DSN/2], [Alpha1]+[Alpha2] AS 2Alpha, IIf([StandardRoom]=Yes And
[Vri1]=0,"Passed",IIf([Vri1]=0," ",IIf([Grinding]=Yes And
[Vri1]<=6,"Passed",IIf([Vri1]<=[Tolerances]![Vri],"PASSED","FAILED")))) AS
TickVri1, IIf([StandardRoom]=Yes And [Vri2]=0,"Passed",IIf([Vri2]=0,"
",IIf([Grinding]=Yes And [Vri2]<=6,"Passed",IIf([Enter Talyform
Data]![Vri2]<=[Tolerances]![Vri],"PASSED","FAILED")))) AS TickVri2,
IIf([Grinding]=Yes And [RaDi1]=0,"Passed",IIf([Honed]=Yes And
[RaDi1]=0,"Failed",IIf([Enter Talyform
Data]![RaDi1]<=[Tolerances]![RaDi],"PASSED","FAILED"))) AS TickRaDi1,
IIf([Grinding]=Yes And [RaDi2]=0,"Passed",IIf([Honed]=Yes And
[RaDi2]=0,"Failed",IIf([Enter Talyform
Data]![RaDi2]<=[Tolerances]![RaDi],"PASSED","FAILED"))) AS TickRaDi2,
IIf([RaDTri1]<=[Tolerances]![Ri MaxTol] And [RaDTri1]>=[Tolerances]![Ri
MinTol],"PASSED","FAILED") AS TickRaDTri1, IIf([RaDTri2]<=[Tolerances]![Ri
MaxTol] And [RaDTri2]>=[Tolerances]![Ri MinTol],"PASSED","FAILED") AS
TickRaDTri2, IIf([Alpha1]<=[Tolerances]![MaxAlpha] And
[Alpha1]>=[Tolerances]![MinAlpha],"PASSED","FAILED") AS TickAngle1,
IIf([Alpha2]<=[Tolerances]![MaxAlpha] And
[Alpha2]>=[Tolerances]![MinAlpha],"PASSED","FAILED") AS TickAngle2,
IIf([2Alpha]>=[Tolerances]![MinAlpha] And
[2Alpha]<=[Tolerances]![2Alpha],"PASSED","FAILED") AS Tick2Alpha,
Tolerances.[Ri MaxTol], Tolerances.[Ri MinTol], Tolerances.MaxAlpha,
Tolerances.MinAlpha, Tolerances.Vri, Tolerances.RaDi,
IIf([TickVri1]="passed",IIf([TickVri2]="passed",IIf([TickRaDi1]="passed",IIf([TickRaDi2]="passed",IIf([TickRaDTri1]="passed",IIf([TickRaDTri2]="passed",IIf([TickAngle1]="passed",IIf([TickAngle2]="passed",IIf([Tick2Alpha]="passed","Approved","Not
Approved"))))))))) AS Ring1a,
IIf([TickVDi]="passed",IIf([TickV3Di]="passed",IIf([TickTSLL]="passed",IIf([TickTSLM]="passed",IIf([TickTSLM]="passed",IIf([TickTSLH]="passed",IIf([TickTSLMax]="passed","Approved","Not
Approved"))))))) AS Ring1b, IIf([Ring1a]="Approved" And
[Ring1b]="Approved","Approved","Not Approved") AS Ring1,
IIf([Tick2Vri1]="passed",IIf([Tick2Vri2]="passed",IIf([Tick2RaDi1]="passed",IIf([Tick2RaDi2]="passed",IIf([Tick2RaDTri1]="passed",IIf([Tick2RaDTri2]="passed",IIf([Tick2Angle1]="passed",IIf([Tick2Angle2]="passed",IIf([Tick22Alpha]="passed","Approved","Not
Approved"))))))))) AS Ring2a,
IIf([Tick2VDi]="passed",IIf([Tick2V3Di]="passed",IIf([Tick2TSLL]="passed",IIf([Tick2TSLM]="passed",IIf([Tick2TSLM]="passed",IIf([Tick2TSLH]="passed",IIf([Tick2TSLMax]="passed","Approved","Not
Approved"))))))) AS Ring2b, IIf([StandardRoom]=Yes,"
",IIf([2RollerDia]=0,"2nd Ring Data Required",IIf([Ring2a]="Approved" And
[Ring2b]="Approved","Approved","Not Approved"))) AS Ring2,
IIf([StandardRoom]=Yes," ",IIf([Ring1]="Not Approved","Not
approved",IIf([Ring2]="Not approved","Not Approved",IIf([Ring2]="2nd Ring
Data required","Not Approved ","Approved")))) AS Ring2c,
IIf([Ring2c]="Approved","Routine Approved","Not Approved") AS RingAppr,
IIf([First Off]=Yes,IIf([ExpireDate]=0,IIf([RingAppr]="Not Approved","Inform
Line Leader/Setter"," "))) AS InfSet, [Enter Talyform
Data].[Station/Machine], [Enter Talyform Data].StandardRoom, [Enter Talyform
Data].Grinding, [Enter Talyform Data].Honed, [Enter Talyform Data].[First
Off], [Enter Talyform Data].Setting, [Enter Talyform Data].[Routine Check],
[Enter Talyform Data].VDi, [Enter Talyform Data].V3Di, [Enter Talyform
Data].[TSL L], [Enter Talyform Data].[TSL M], [Enter Talyform Data].[TSL H],
[Enter Talyform Data].[TSL max], [Enter Talyform Data].[2VDi], [Enter
Talyform Data].[2V3Di], [Enter Talyform Data].[2TSL L], [Enter Talyform
Data].[2TSL M], [Enter Talyform Data].[2TSL H], [Enter Talyform Data].[2TSL
max], Tolerances.VDi, Tolerances.V3Di, Tolerances.[TSL L], Tolerances.[TSL
H], Tolerances.[TSL M], Tolerances.[TSL Max], IIf([StandardRoom]=Yes And
[Enter Talyform Data.VDi]=0,"Passed",IIf([Enter Talyform
Data.VDi]=0,"Failed",IIf([Enter Talyform
Data.VDi]<=[Tolerances]![VDi],"PASSED","FAILED"))) AS TickVDi,
IIf([StandardRoom]=Yes And [Enter Talyform Data.V3Di]=0,"Passed",IIf([Enter
Talyform Data.V3Di]=0,"Failed",IIf([Enter Talyform
Data.V3Di]<=[Tolerances]![V3Di],"PASSED","FAILED"))) AS TickV3Di,
IIf([StandardRoom]=Yes And [Enter Talyform Data.TSL L]=0,"Passed",IIf([Enter
Talyform Data.TSL L]=0,"Failed",IIf([Enter Talyform Data.TSL
L]<=[Tolerances.TSL L],"PASSED","FAILED"))) AS TickTSLL, IIf([Grinding]=Yes
And [2RaDi1]=0,"Passed",IIf([Enter Talyform Data.TSL M]=0,"Failed",IIf([Enter
Talyform Data.TSL M]<=[Tolerances.TSL M],"PASSED","FAILED"))) AS TickTSLM,
IIf([Grinding]=Yes And [2RaDi1]=0,"Passed",IIf([Enter Talyform Data.TSL
H]=0,"Failed",IIf([Enter Talyform Data.TSL H]<=[Tolerances.TSL
H],"PASSED","FAILED"))) AS TickTSLH, IIf([Grinding]=Yes And
[2RaDi1]=0,"Passed",IIf([Enter Talyform Data.TSL max]=0,"Failed",IIf([Enter
Talyform Data.TSL max]<=[Tolerances.TSL max],"PASSED","FAILED"))) AS
TickTSLmax, IIf([StandardRoom]=Yes And [Enter Talyform
Data.2VDi]=0,"Passed",IIf([2VDi]=0,"Failed",IIf([Enter Talyform
Data.2VDi]<=[Tolerances]![VDi],"PASSED","FAILED"))) AS Tick2VDi,
IIf([StandardRoom]=Yes And [Enter Talyform
Data.2V3Di]=0,"Passed",IIf([2V3Di]=0,"Failed",IIf([Enter Talyform
Data.2V3Di]<=[Tolerances]![V3Di],"PASSED","FAILED"))) AS Tick2V3Di,
IIf([StandardRoom]=Yes And [Enter Talyform Data.2TSL L]=0,"Passed",IIf([2TSL
L]=0,"Failed",IIf([Enter Talyform Data.2TSL L]<=[Tolerances.TSL
L],"PASSED","FAILED"))) AS Tick2TSLL, IIf([Grinding]=Yes And
[2RaDi1]=0,"Passed",IIf([2TSL M]=0,"Failed",IIf([Enter Talyform Data.2TSL
M]<=[Tolerances.TSL M],"PASSED","FAILED"))) AS Tick2TSLM, IIf([Grinding]=Yes
And [2RaDi1]=0,"Passed",IIf([2TSL H]=0,"Failed",IIf([Enter Talyform Data.2TSL
H]<=[Tolerances.TSL H],"PASSED","FAILED"))) AS Tick2TSLH, IIf([Grinding]=Yes
And [2RaDi1]=0,"Passed",IIf([2TSL max]=0,"Failed",IIf([Enter Talyform
Data.2TSL max]<=[Tolerances.TSL max],"PASSED","FAILED"))) AS Tick2TSLmax,
[Enter Talyform Data].OK, Tolerances.Type, [Enter Talyform Data].PGI,
IIf([RollerDia] And [2RollerDia]>0,"Passed","Failed") AS 2ringchk,
IIf([ExpireDate]=0 And [RingAppr]="Routine Approved" And [First
Off]=Yes,"FIRST OFF APPROVAL") AS Stamp, [Enter Talyform Data].DiamondRollNo,
[Enter Talyform Data].ManufacturersDiamondNo, [Tolerances]![MaxAlpha]*0.25 AS
PercentAngle, Abs([Alpha1]-[Alpha2]) AS BalancedAngle,
IIf([BalancedAngle]<=[PercentAngle],"Passed","Failed") AS TickBalancedAngle,
Abs([2Alpha1]-[2Alpha2]) AS BalancedAngle2,
IIf([BalancedAngle2]<=[PercentAngle],"Passed","Failed") AS TickBalancedAngle2
FROM [Enter Talyform Data] INNER JOIN Tolerances ON [Enter Talyform
Data].Type = Tolerances.Type;


TedMi said:
Post the complete SQL of your query. Display the query in SQL view, select
click Ctrl-A in the query text, copy and paste into a message to this NG.
-TedMi

jo said:
Hi I am getting a "Query to complex" when I have added another Expression
to
my query. Actually the query accepts this and opens, but when I open the
form
that has been produced from this query thats when I get Query to Complex..
Does any one know a reason for this and how it can be solved?
The expression is this:
TickBalancedAngle: IIf([2BalancedAngle]<=[PercentAngle],"Passed","Failed")
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top