Iif function with two fields to look at

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.
 
T

tina

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

hth
 
F

Frustrated

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...
 
L

Lord Kelvan

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
 
F

Frustrated

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...
 
T

tina

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...
 
J

John Spencer

The query should look like the following

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.week starting]-46 AS [Next Stage Date]
, [Slot Booking].[Cancelled Cont]
, [Slot Booking].Cancelled
, [Yearly Grains Booking].[CBH Booking Number]
, [Slot Booking].[Slot Comments]
, IIf(IsNull([completed stage])
And [next stage date]<Date(),"overdue","pending")

One possible problem is the use of FIRST. FIRST returns the first record
encountered in the group - not necessarily the first record entered, or the
earliest record (if a date). It is best to figure that First (or last) will
essentially return more or less a random value from the group of records
determined by the grouping. If that is what you want, then no problem.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
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


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...
 
F

Frustrated

Thanks Guys but none of your suggestions work. I get missing ( syntax error
on the
[slot booking.week starting]-46 AS [Next Stage Date]

I have tried putting the parenthesis before slot and after 46, but get
another error message elsewhere.

Perhaps the problem is that I am joining two tables. The slot booking CBH
number is the Primary and the Yearly Bookings table - CBH number is the
foreign.

Without the IIf statement [status] field, the resulting query gives me
exactly the info I want, (including the [next stage date] but when I put the
[status] field in with the IIf statement, it is looking at the [completed
stage] which is in the yearly grains table and the [next stage date] is in
the slot booking table. Also this field [next stage date] in the query is an
expression.???

And yes I do need the first in the [completed stage] field. There are 4
stages and I am only interested in the first stage which is attached to the
slot booking. 2 to 4 is entered into the yearly grains table.

John Spencer said:
The query should look like the following

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.week starting]-46 AS [Next Stage Date]
, [Slot Booking].[Cancelled Cont]
, [Slot Booking].Cancelled
, [Yearly Grains Booking].[CBH Booking Number]
, [Slot Booking].[Slot Comments]
, IIf(IsNull([completed stage])
And [next stage date]<Date(),"overdue","pending")

One possible problem is the use of FIRST. FIRST returns the first record
encountered in the group - not necessarily the first record entered, or the
earliest record (if a date). It is best to figure that First (or last) will
essentially return more or less a random value from the group of records
determined by the grouping. If that is what you want, then no problem.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
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]));

:

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

hth


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...
 
J

John Spencer

Change the lines in the SELECT clause and the GROUP BY clause

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

GROUP BY Clause
, [slot booking].[week starting]-46


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks Guys but none of your suggestions work. I get missing ( syntax error
on the
[slot booking.week starting]-46 AS [Next Stage Date]

I have tried putting the parenthesis before slot and after 46, but get
another error message elsewhere.

Perhaps the problem is that I am joining two tables. The slot booking CBH
number is the Primary and the Yearly Bookings table - CBH number is the
foreign.

Without the IIf statement [status] field, the resulting query gives me
exactly the info I want, (including the [next stage date] but when I put the
[status] field in with the IIf statement, it is looking at the [completed
stage] which is in the yearly grains table and the [next stage date] is in
the slot booking table. Also this field [next stage date] in the query is an
expression.???

And yes I do need the first in the [completed stage] field. There are 4
stages and I am only interested in the first stage which is attached to the
slot booking. 2 to 4 is entered into the yearly grains table.

John Spencer said:
The query should look like the following

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.week starting]-46 AS [Next Stage Date]
, [Slot Booking].[Cancelled Cont]
, [Slot Booking].Cancelled
, [Yearly Grains Booking].[CBH Booking Number]
, [Slot Booking].[Slot Comments]
, IIf(IsNull([completed stage])
And [next stage date]<Date(),"overdue","pending")

One possible problem is the use of FIRST. FIRST returns the first record
encountered in the group - not necessarily the first record entered, or the
earliest record (if a date). It is best to figure that First (or last) will
essentially return more or less a random value from the group of records
determined by the grouping. If that is what you want, then no problem.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
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


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]));
:

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

hth


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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Project Status Date 1
Cobditional Formatting Rows based on dates in action plan. 0
determining if a field should be included 6
Complex Count Query 0
Statement no working 2
Blanks incorrect 2
iif function 2
IIf Statement problem 9

Top