R
Ron
Hello everyone,
I have this SQL code in a Select Query that basically
looks to see if a received date has been entered in its
appropriate field. If there is no date in that received
field then it determines if the job is
due "today", "tomorrow" or if its "past due". If a date
has been entered then it's marked as "received".
The problem I'm running into is when I try to add another
level field programming to the SQL statement. Access
tells me that the expression is too complex. Can this
statement be simplified, and if so, how can I add more
fields to the mix?
Any help (in simple programming terms - I'm not an
advanced coder) would be greatly appreciated.
Thanks - Ron
See code below.
-----------------------------
SELECT [Job Tracking].[Traffic Person], [Job Tracking].
[Status of Job], [Job Tracking].Job, [Job
Tracking].Version, [Job Tracking].Product, [Job
Tracking].Source, [Job Tracking].[Ca1 Due], [Job Tracking].
[CA1 Received], [Job Tracking].[Ca2 Due], [Job Tracking].
[CA2 Received], [Job Tracking].[Ca3 Due], [Job Tracking].
[CA3 Received], [Job Tracking].[Ca4 Due], [Job Tracking].
[CA4 Received], [Job Tracking].[Start Date], [Job
Tracking].Category, [Job Tracking].Creative, [Job
Tracking].Media
FROM [Job Tracking] WHERE
(((IIf([CA1 Due]=Date() And [CA1 Received] Is Null,"Due
Today",
IIf([CA2 Due]=Date() And [CA2 Received] Is Null,"Due
Today",
IIf([CA3 Due]=Date() And [CA3 Received] Is Null,"Due
Today",
IIf([CA4 Due]=Date() And [CA4 Received] Is Null,"Due
Today",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due] Between Date()+1 And Date()+2 And [CA1
Received] Is Null,"Due Tomorrow",
IIf([CA2 Due] Between Date()+1 And Date()+2 And [CA2
Received] Is Null,"Due Tomorrow",
IIf([CA3 Due] Between Date()+1 And Date()+2 And [CA3
Received] Is Null,"Due Tomorrow",
IIf([CA4 Due] Between Date()+1 And Date()+2 And [CA4
Received] Is Null,"Due Tomorrow",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due]<Date() And [CA1 Received] Is Null,"Past Due",
IIf([CA2 Due]<Date() And [CA2 Received] Is Null,"Past Due",
IIf([CA3 Due]<Date() And [CA3 Received] Is Null,"Past Due",
IIf([CA4 Due]<Date() And [CA4 Received] Is Null,"Past
Due","Received"))))))))))))))<>"received") AND (([Job
Tracking].[Cancelled Job])<>Yes))
ORDER BY [Job Tracking].[Traffic Person], [Job
Tracking].Job, [Job Tracking].Version;
I have this SQL code in a Select Query that basically
looks to see if a received date has been entered in its
appropriate field. If there is no date in that received
field then it determines if the job is
due "today", "tomorrow" or if its "past due". If a date
has been entered then it's marked as "received".
The problem I'm running into is when I try to add another
level field programming to the SQL statement. Access
tells me that the expression is too complex. Can this
statement be simplified, and if so, how can I add more
fields to the mix?
Any help (in simple programming terms - I'm not an
advanced coder) would be greatly appreciated.
Thanks - Ron
See code below.
-----------------------------
SELECT [Job Tracking].[Traffic Person], [Job Tracking].
[Status of Job], [Job Tracking].Job, [Job
Tracking].Version, [Job Tracking].Product, [Job
Tracking].Source, [Job Tracking].[Ca1 Due], [Job Tracking].
[CA1 Received], [Job Tracking].[Ca2 Due], [Job Tracking].
[CA2 Received], [Job Tracking].[Ca3 Due], [Job Tracking].
[CA3 Received], [Job Tracking].[Ca4 Due], [Job Tracking].
[CA4 Received], [Job Tracking].[Start Date], [Job
Tracking].Category, [Job Tracking].Creative, [Job
Tracking].Media
FROM [Job Tracking] WHERE
(((IIf([CA1 Due]=Date() And [CA1 Received] Is Null,"Due
Today",
IIf([CA2 Due]=Date() And [CA2 Received] Is Null,"Due
Today",
IIf([CA3 Due]=Date() And [CA3 Received] Is Null,"Due
Today",
IIf([CA4 Due]=Date() And [CA4 Received] Is Null,"Due
Today",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due] Between Date()+1 And Date()+2 And [CA1
Received] Is Null,"Due Tomorrow",
IIf([CA2 Due] Between Date()+1 And Date()+2 And [CA2
Received] Is Null,"Due Tomorrow",
IIf([CA3 Due] Between Date()+1 And Date()+2 And [CA3
Received] Is Null,"Due Tomorrow",
IIf([CA4 Due] Between Date()+1 And Date()+2 And [CA4
Received] Is Null,"Due Tomorrow",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due]<Date() And [CA1 Received] Is Null,"Past Due",
IIf([CA2 Due]<Date() And [CA2 Received] Is Null,"Past Due",
IIf([CA3 Due]<Date() And [CA3 Received] Is Null,"Past Due",
IIf([CA4 Due]<Date() And [CA4 Received] Is Null,"Past
Due","Received"))))))))))))))<>"received") AND (([Job
Tracking].[Cancelled Job])<>Yes))
ORDER BY [Job Tracking].[Traffic Person], [Job
Tracking].Job, [Job Tracking].Version;