Iif function with two fields to look at

  • Thread starter Thread starter Frustrated
  • Start date Start date
F

Frustrated

I have query with a [completed stage] field and a [due date] field. I am
trying to work on a Status field which will return a result. The query is
that if the completed stage is null and the due date is after today's date,
then the status is overdue. I have the following

Status: IIf(IsNull([completed stage]) And [next stage
date]<Date(),"overdue","pending")

When I try to run the query, I get the "You tried to execute a uery that
does not include the specific " Status line" as part of an aggregate function.

What am I doing wrong.
 
Thanks in advance, this is the SQL Statement:

SELECT [Slot Booking].Week, [Slot Booking].[Week Starting], [Slot
Booking].[CBH Booking Number], [Slot Booking].[Company Name], [Slot
Booking].[Number of Slots], [slot booking.week starting]-46 AS [Next Stage
Date], [Slot Booking].[Cancelled Cont], [Slot Booking].Cancelled, [Yearly
Grains Booking].[CBH Booking Number], Sum([Yearly Grains Booking].[No of
Containers]) AS [SumOfNo of Containers], Sum([Yearly Grains
Booking].[Completed Containers]) AS [SumOfCompleted Containers],
First([Yearly Grains Booking].[Completed Stage]) AS [FirstOfCompleted Stage],
Count([Yearly Grains Booking].[St 1 Cancelled]) AS [CountOfSt 1 Cancelled],
Sum([Yearly Grains Booking].Tonnes) AS SumOfTonnes, [Slot Booking].[Slot
Comments], IIf(IsNull([completed stage]) And [next stage
date]<Date(),"overdue","pending") AS Status
FROM [Slot Booking] LEFT JOIN [Yearly Grains Booking] ON [Slot Booking].[CBH
Booking Number] = [Yearly Grains Booking].[CBH Booking Number]
GROUP BY [Slot Booking].Week, [Slot Booking].[Week Starting], [Slot
Booking].[CBH Booking Number], [Slot Booking].[Company Name], [Slot
Booking].[Number of Slots], [Slot Booking].[Cancelled Cont], [Slot
Booking].Cancelled, [Yearly Grains Booking].[CBH Booking Number], [Slot
Booking].[Slot Comments];


tina said:
suggest you paste the query's complete SQL statement into a post, so we can
see it.

hth


Frustrated said:
I have query with a [completed stage] field and a [due date] field. I am
trying to work on a Status field which will return a result. The query is
that if the completed stage is null and the due date is after today's date,
then the status is overdue. I have the following

Status: IIf(IsNull([completed stage]) And [next stage
date]<Date(),"overdue","pending")

When I try to run the query, I get the "You tried to execute a uery that
does not include the specific " Status line" as part of an aggregate function.

What am I doing wrong.
Click to expand...
Click to expand...
 
i noticed a few errors

SELECT [Slot Booking].[Week], [Slot Booking].[Week Starting], [Slot
Booking].[CBH Booking Number], [Slot Booking].[Company Name], [Slot
Booking].[Number of Slots], dateadd("d", -46 [slot booking].[week
starting]) AS [Next Stage
Date], [Slot Booking].[Cancelled Cont], [Slot Booking].Cancelled,
[Yearly
Grains Booking].[CBH Booking Number], Sum([Yearly Grains Booking].[No
of
Containers]) AS [SumOfNo of Containers], Sum([Yearly Grains
Booking].[Completed Containers]) AS [SumOfCompleted Containers],
First([Yearly Grains Booking].[Completed Stage]) AS [FirstOfCompleted
Stage],
Count([Yearly Grains Booking].[St 1 Cancelled]) AS [CountOfSt 1
Cancelled],
Sum([Yearly Grains Booking].Tonnes) AS SumOfTonnes, [Slot Booking].
[Slot
Comments], IIf(IsNull([completed stage]) And [next stage
date]<Date(),"overdue","pending") AS Status
FROM [Slot Booking] LEFT JOIN [Yearly Grains Booking] ON [Slot
Booking].[CBH
Booking Number] = [Yearly Grains Booking].[CBH Booking Number]
GROUP BY [Slot Booking].[Week], [Slot Booking].[Week Starting], [Slot
Booking].[CBH Booking Number], [Slot Booking].[Company Name], [Slot
Booking].[Number of Slots], [Slot Booking].[Cancelled Cont], [Slot
Booking].[Cancelled], [Yearly Grains Booking].[CBH Booking Number],
[Slot
Booking].[Slot Comments];

you typed soemthign in and tryed to -46 from it rather than using
access to do it so you had

[slot booking.week starting]-46 AS [Next Stage Date]

so i changed it to

dateadd("d", -46 [slot booking].[week starting]) AS [Next Stage Date]

so try that wuery

Regards
Kelvan
 
Thanks, this is it;
SELECT [Slot Booking].Week, [Slot Booking].[Week Starting], IIf([yearly
grains booking.cbh booking number] Is Null,"overdue") AS Status, [Slot
Booking].[CBH Booking Number], [Slot Booking].[Company Name], [Slot
Booking].[Number of Slots], [slot booking.week starting]-46 AS [Next Stage
Date], [Slot Booking].[Cancelled Cont], [Slot Booking].Cancelled, [Slot
Booking].[Cancelled Other], [Yearly Grains Booking].[CBH Booking Number],
Sum([Yearly Grains Booking].[No of Containers]) AS [SumOfNo of Containers],
Sum([Yearly Grains Booking].[Completed Containers]) AS [SumOfCompleted
Containers], Last([Yearly Grains Booking].[Completed Stage]) AS
[LastOfCompleted Stage], Count([Yearly Grains Booking].[St 1 Cancelled]) AS
[CountOfSt 1 Cancelled], Sum([Yearly Grains Booking].Tonnes) AS SumOfTonnes,
[Slot Booking].[Slot Comments], Sum([Yearly Grains Booking].Invoiced) AS
SumOfInvoiced, IIf([yearly grains booking.completed stage] Is Null And [next
stage date]<Date(),"Overdue") AS Stat
FROM [Slot Booking] LEFT JOIN [Yearly Grains Booking] ON [Slot Booking].[CBH
Booking Number] = [Yearly Grains Booking].[CBH Booking Number]
GROUP BY [Slot Booking].Week, [Slot Booking].[Week Starting], [Slot
Booking].[CBH Booking Number], [Slot Booking].[Company Name], [Slot
Booking].[Number of Slots], [Slot Booking].[Cancelled Cont], [Slot
Booking].Cancelled, [Slot Booking].[Cancelled Other], [Yearly Grains
Booking].[CBH Booking Number], [Slot Booking].[Slot Comments]
HAVING ((([Slot Booking].Week) Between [Select Week] And [Second Week]) AND
(([Slot Booking].[Company Name]) Like [Select COMPANY Name or * for all]));


tina said:
suggest you paste the query's complete SQL statement into a post, so we can
see it.

hth


Frustrated said:
I have query with a [completed stage] field and a [due date] field. I am
trying to work on a Status field which will return a result. The query is
that if the completed stage is null and the due date is after today's date,
then the status is overdue. I have the following

Status: IIf(IsNull([completed stage]) And [next stage
date]<Date(),"overdue","pending")

When I try to run the query, I get the "You tried to execute a uery that
does not include the specific " Status line" as part of an aggregate function.

What am I doing wrong.
Click to expand...
Click to expand...
 
okay. i'm guessing that your IIf() expression is set as Expression in the
Total row, in the query Design grid. try changing it to Group By, and see if
that will a) run, and b) get the result you want.

hth


Frustrated said:
Thanks, this is it;
SELECT [Slot Booking].Week, [Slot Booking].[Week Starting], IIf([yearly
grains booking.cbh booking number] Is Null,"overdue") AS Status, [Slot
Booking].[CBH Booking Number], [Slot Booking].[Company Name], [Slot
Booking].[Number of Slots], [slot booking.week starting]-46 AS [Next Stage
Date], [Slot Booking].[Cancelled Cont], [Slot Booking].Cancelled, [Slot
Booking].[Cancelled Other], [Yearly Grains Booking].[CBH Booking Number],
Sum([Yearly Grains Booking].[No of Containers]) AS [SumOfNo of Containers],
Sum([Yearly Grains Booking].[Completed Containers]) AS [SumOfCompleted
Containers], Last([Yearly Grains Booking].[Completed Stage]) AS
[LastOfCompleted Stage], Count([Yearly Grains Booking].[St 1 Cancelled]) AS
[CountOfSt 1 Cancelled], Sum([Yearly Grains Booking].Tonnes) AS SumOfTonnes,
[Slot Booking].[Slot Comments], Sum([Yearly Grains Booking].Invoiced) AS
SumOfInvoiced, IIf([yearly grains booking.completed stage] Is Null And [next
stage date]<Date(),"Overdue") AS Stat
FROM [Slot Booking] LEFT JOIN [Yearly Grains Booking] ON [Slot Booking].[CBH
Booking Number] = [Yearly Grains Booking].[CBH Booking Number]
GROUP BY [Slot Booking].Week, [Slot Booking].[Week Starting], [Slot
Booking].[CBH Booking Number], [Slot Booking].[Company Name], [Slot
Booking].[Number of Slots], [Slot Booking].[Cancelled Cont], [Slot
Booking].Cancelled, [Slot Booking].[Cancelled Other], [Yearly Grains
Booking].[CBH Booking Number], [Slot Booking].[Slot Comments]
HAVING ((([Slot Booking].Week) Between [Select Week] And [Second Week]) AND
(([Slot Booking].[Company Name]) Like [Select COMPANY Name or * for all]));


tina said:
suggest you paste the query's complete SQL statement into a post, so we can
see it.

hth


Frustrated said:
I have query with a [completed stage] field and a [due date] field. I am
trying to work on a Status field which will return a result. The query is
that if the completed stage is null and the due date is after today's date,
then the status is overdue. I have the following

Status: IIf(IsNull([completed stage]) And [next stage
date]<Date(),"overdue","pending")

When I try to run the query, I get the "You tried to execute a uery that
does not include the specific " Status line" as part of an aggregate function.

What am I doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top