Can anyone simplify this SQL

[SteveNapper]

SELECT Job.ObjectID, Job.Design, Job.DesignTime, Job.Cost, Job.CostTime, Job.Purchase, Job.[Purchase Time], Job.Plan, Job.PlanTime, Job.UnloadRawMaterials, Job.UnloadRawMaterialsTime, Job.MarkOut, Job.MarkOutTime, Job.CutOut, Job.CutOutTime, Job.Plane, Job.PlaneTime, Job.CutToLength, Job.CutToLengthTime, Job.Joint, Job.JointTime, Job.DryAssemble, Job.DryAssembleTime, Job.Marquetry, Job.MarquetryTime, Job.PressVeneerOntoPly, Job.PressTime, Job.GlueFrame, Job.GlueTime, Job.CleanUp, Job.CleanTime, Job.Sand, Job.SandTime, Job.SprayTime, Job.Assemble, Job.AssembleTime, Job.Check, Job.CheckTime, Job.Wrap, Job.WrapTime, Job.Load, Job.LoadTime, Job.Transport, Job.TransportTime, Job.Unload, Job.UnloadTime, Job.Return, Job.ReturnTime, Job.MaterialsID, Job.Quantity
FROM Job
WHERE (((Job.ObjectID)=(IIf(IsNull([Forms]!

![Object]),[ObjectID],[Forms]!

![Object]))) AND ((Job.Design)=(IIf(IsNull([Forms]!

![Design]),[Design],[Forms]!

![Design]))) AND ((Job.Cost)=(IIf(IsNull([Forms]!

![Cost]),[Cost],[Forms]!

![Cost]))) AND ((Job.Purchase)=(IIf(IsNull([Forms]!

![Purchase]),[Purchase],[Forms]!

![Purchase]))) AND ((Job.Plan)=(IIf(IsNull([Forms]!

![Plan]),[Plan],[Forms]!

![Plan]))) AND ((Job.UnloadRawMaterials)=(IIf(IsNull([Forms]!

![UnLoadRawMaterials]),[design],[Forms]!

![UnLoadRawMaterials]))) AND ((Job.MarkOut)=(IIf(IsNull([Forms]!

![MarkOut]),[MarkOut],[Forms]!

![MarkOut]))) AND ((Job.CutOut)=(IIf(IsNull([Forms]!

![CutOut]),[CutOut],[Forms]!

![CutOut]))) AND ((Job.Plane)=(IIf(IsNull([Forms]!

![Plane]),[Plane],[Forms]!

![Plane]))) AND ((Job.CutToLength)=(IIf(IsNull([Forms]!

![CutToLength]),[CutToLength],[Forms]!

![CutToLength]))) AND ((Job.Joint)=(IIf(IsNull([Forms]!

![Joint]),[Joint],[Forms]!

![Joint]))) AND ((Job.DryAssemble)=(IIf(IsNull([Forms]!

![DryAssemble]),[design],[Forms]!

![DryAssemble]))) AND ((Job.Marquetry)=(IIf(IsNull([Forms]!

![Marquetry]),[Marquetry],[Forms]!

![Marquetry]))) AND ((Job.PressVeneerOntoPly)=(IIf(IsNull([Forms]!

![PressVeneer]),[PressVeneerOntoPly],[Forms]!

![PressVeneer]))) AND ((Job.GlueFrame)=(IIf(IsNull([Forms]!

![GlueFrame]),[GlueFrame],[Forms]!

![GlueFrame]))) AND ((Job.CleanUp)=(IIf(IsNull([Forms]!

![CleanUp]),[CleanUp],[Forms]!

![CleanUp]))) AND ((Job.Sand)=(IIf(IsNull([Forms]!

![Sand]),[Sand],[Forms]!

![Sand]))) AND ((Job.Spray)=(IIf(IsNull([Forms]!

![Spray]),[Spray],[Forms]!

![Spray]))));

For some unkown reason, it's crashing the PC when I try to query it through the Quotes Form. If I simply run the query and type in the ID No's it works!!!
Thanks

 

[raskew]

Only for $$. Come on, give us a break!

 

[tunsarod]

If ObjectID is a string and not a number then try using the Like operator instead of equals:

WHERE (((Job.ObjectID) Like (IIf(IsNull([Forms]!

![Object]),[ObjectID],[Forms]!

![Object])))

Also you have an error in this line I think.

AND ((Job.DryAssemble)=(IIf(IsNull([Forms]!

![DryAssemble]),[design],[Forms]!

![DryAssemble])))

Rod

 

[Rolliee]

If it works as a query the move to the design mode and view is as SQL. Do a cut and past the date in whatever kettle you are cooking in at the time.


raskew is right. Slavery was outlawed with the Civil War.

be thankful for what you have.

rollie@bwsys.net

 

[gol4]

If I might make one more suggestion. It appears you are trying to build the where criteria allowing for no selection
in a forms fields. I would scrap the where clause and build it on the fly using VBA then run the sql.

 

[SteveNapper]

Thanks for the help tunsarod and gol4.

The answer I wanted, and worked out for myself over 3 days is:

WHERE (((Job.ObjectID)=Nz([Forms]![JoBQueryID]![Object],[ObjectID])) AND, etc, etc.

Basically replace the (IIf(IsNull with Nz

WHERE (((Job.ObjectID)=(IIf(IsNull([Forms]!

![Object]),[ObjectID],[Forms]!

![Object]))) AND

The query was locking the computer, etc. because Access only allows 20 fields in a query as I have found out by building the query and form, step by step.

Do any of you know how to increase this or is it a built in feature?

Time wasters need not apply