Query is too complex

[Guest]

I have a CLEANUP Form that includes an input control for YearlyCleanupYear.
When the user clicks on YearlyCleanup button, it prompts the user to enter
the YearlyCleanupYear (if it is null) before running the query below.
YearlyCleanupYear control is formatted fixed data type; Date Worked in
WorkOrd table is formatted as a Short Date; thus the need for
CLng(DatePart("yyyy",[Date Worked]))=[Forms]![CLEANUP
Form]![YearlyCleanupYear] in WHERE clause. If CLng is removed, the records
will not be inserted into HistAll table due to data type conversion mismatch.
If CLng is included, get error that query is too complex.

BTW, if the WHERE clause only includes the CLng(DatePart("yyyy",[Date
Worked]))=[Forms]![CLEANUP Form]![YearlyCleanupYear] condition, it works...
But, I need to further filter based on the other two conditions in order to
have correct data inserted into HistALL table.

Any ideas? Please refer to query below. Thanks in advance for any help on
this. C.


INSERT INTO HistALL ( [Asset Number], [WO Number], [Problem Number], [Clock
Number], [Actual Time], [WO Type], [Problem Description], [Submitted By],
[Fault Code], [Cycle Number], Comments, [Asset Description], [Estimated
Time], [Major Assembly], Component, [Action Done], [What Was Done], [Total
Hrs Down], [Machine Down], [WO Date], [Date Down], [Date Worked], [Date Up],
[Downtime Comments] )
SELECT DISTINCT WorkOrd.[Asset Number], WorkOrd.[WO Number],
WorkOrd.[Problem Number], WorkOrd.[Clock Number], WorkOrd.[Actual Time],
WorkOrd.[WO Type], WorkOrd.[Problem Description], WorkOrd.[Submitted By],
WorkOrd.[Fault Code], WorkOrd.[Cycle Number], WorkOrd.Comments,
WorkOrd.[Asset Description], WorkOrd.[Estimated Time], WorkOrd.[Major
Assembly], WorkOrd.[Component Code], WorkOrd.[Action Done], WorkOrd.[What Was
Done], WorkOrd.[Total Hrs Down], WorkOrd.[Machine Down], WorkOrd.[WO Date],
WorkOrd.[Date Down], WorkOrd.[Date Worked], WorkOrd.[Date Up],
WorkOrd.[Downtime Comments]
FROM WorkOrd
WHERE CLng(DatePart("yyyy",[Date Worked]))=[Forms]![CLEANUP
Form]![YearlyCleanupYear] AND WorkOrd.[WO Status]="COMPLETE" AND
WorkOrd.[History Update] IS NULL;

 

[Ken Snell [MVP]]

This error may occur if [DateWorked] can contain a Null value; CLng does not
work with Null argument. If you can use a default value for Date Worked, use
the Nz function to do that (in example below, I'm using the current date):

CLng(DatePart("yyyy",Nz([Date Worked], Date())))

 

[Guest]

Ken, Thank You so much - that worked GREAT!

Chris

This error may occur if [DateWorked] can contain a Null value; CLng does not
work with Null argument. If you can use a default value for Date Worked, use
the Nz function to do that (in example below, I'm using the current date):

CLng(DatePart("yyyy",Nz([Date Worked], Date())))

--

Ken Snell
<MS ACCESS MVP>

I have a CLEANUP Form that includes an input control for YearlyCleanupYear.
When the user clicks on YearlyCleanup button, it prompts the user to enter
the YearlyCleanupYear (if it is null) before running the query below.
YearlyCleanupYear control is formatted fixed data type; Date Worked in
WorkOrd table is formatted as a Short Date; thus the need for
CLng(DatePart("yyyy",[Date Worked]))=[Forms]![CLEANUP
Form]![YearlyCleanupYear] in WHERE clause. If CLng is removed, the records
will not be inserted into HistAll table due to data type conversion
mismatch.
If CLng is included, get error that query is too complex.

BTW, if the WHERE clause only includes the CLng(DatePart("yyyy",[Date
Worked]))=[Forms]![CLEANUP Form]![YearlyCleanupYear] condition, it
works...
But, I need to further filter based on the other two conditions in order
to
have correct data inserted into HistALL table.

Any ideas? Please refer to query below. Thanks in advance for any help on
this. C.


INSERT INTO HistALL ( [Asset Number], [WO Number], [Problem Number],
[Clock
Number], [Actual Time], [WO Type], [Problem Description], [Submitted By],
[Fault Code], [Cycle Number], Comments, [Asset Description], [Estimated
Time], [Major Assembly], Component, [Action Done], [What Was Done], [Total
Hrs Down], [Machine Down], [WO Date], [Date Down], [Date Worked], [Date
Up],
[Downtime Comments] )
SELECT DISTINCT WorkOrd.[Asset Number], WorkOrd.[WO Number],
WorkOrd.[Problem Number], WorkOrd.[Clock Number], WorkOrd.[Actual Time],
WorkOrd.[WO Type], WorkOrd.[Problem Description], WorkOrd.[Submitted By],
WorkOrd.[Fault Code], WorkOrd.[Cycle Number], WorkOrd.Comments,
WorkOrd.[Asset Description], WorkOrd.[Estimated Time], WorkOrd.[Major
Assembly], WorkOrd.[Component Code], WorkOrd.[Action Done], WorkOrd.[What
Was
Done], WorkOrd.[Total Hrs Down], WorkOrd.[Machine Down], WorkOrd.[WO
Date],
WorkOrd.[Date Down], WorkOrd.[Date Worked], WorkOrd.[Date Up],
WorkOrd.[Downtime Comments]
FROM WorkOrd
WHERE CLng(DatePart("yyyy",[Date Worked]))=[Forms]![CLEANUP
Form]![YearlyCleanupYear] AND WorkOrd.[WO Status]="COMPLETE" AND
WorkOrd.[History Update] IS NULL;