Over 30 Days

[lmossolle]

I have a date field "daterecieved" i would like to query for items over 30
days old where a completed field is null. Please assist. I am trying to get
the number of items over 30 days old if the completed field is Null. The
daterecieved it the item I want to use to capture the 30 day.

I tried this and was unsuccessfull.
Sum(IIf(daterecieved < Date() - 30 AND completed Is Null)) AS daysoverdue

Thanks!!!

 

[Klatuu]

You don't need the Iif, and I am not sure you need the Sum, unless it is a
totals query:

WHERE [daterecieved] < DateAdd("d",-30),Date()) AND [completed] Is Null

 

[lmossolle]

This is the SQL Iam using the 4th from the bottom, as daysover due is not
working.

SELECT
Sum(IIf(Assigned="Amie",1,0)) AS [Cumulative Actions],
Sum(IIf(Status="returned",1,0)) AS [# Actions Returned],
Sum(IIf(Status="pending",1,0)) AS [# Actions Pending],
Sum(IIf(Status="solicited",1,0)) AS [# Actions Solicited],
Sum(IIf([SUP CON A&E UTL SVC]="MOD",1,0)) AS [# MOD Actions],
Sum(IIf(Status="awarded",1,0)) AS [# Actions Awarded],
Sum(IIf(CARFinal="n",1,0)) AS [# Overdue CARS],
Sum(IIf(AwardDate>=DateRcvd,AwardDate-DateRcvd)) AS daystoaward,
Sum(IIf(Complete="y",1,0)) AS Awarddays,
Sum(IIf(datercvd < Date() - 30 AND completed Is Null)) AS daysoverdue,
Sum(Nz([PR $ Value])) AS [$ Value]
FROM SCD
WHERE (((SCD.Assigned)="Amie"));

You don't need the Iif, and I am not sure you need the Sum, unless it is a
totals query:

WHERE [daterecieved] < DateAdd("d",-30),Date()) AND [completed] Is Null
--
Dave Hargis, Microsoft Access MVP

I have a date field "daterecieved" i would like to query for items over 30
days old where a completed field is null. Please assist. I am trying to get
the number of items over 30 days old if the completed field is Null. The
daterecieved it the item I want to use to capture the 30 day.

I tried this and was unsuccessfull.
Sum(IIf(daterecieved < Date() - 30 AND completed Is Null)) AS daysoverdue

Thanks!!!

 

[Klatuu]

You have the criteria, but not field is defined to Sum on.
Sum(IIf(datercvd < Date() - 30 AND completed Is Nul, What When True?, What
When False)) AS daysoverdue,

--
Dave Hargis, Microsoft Access MVP

This is the SQL Iam using the 4th from the bottom, as daysover due is not
working.

SELECT
Sum(IIf(Assigned="Amie",1,0)) AS [Cumulative Actions],
Sum(IIf(Status="returned",1,0)) AS [# Actions Returned],
Sum(IIf(Status="pending",1,0)) AS [# Actions Pending],
Sum(IIf(Status="solicited",1,0)) AS [# Actions Solicited],
Sum(IIf([SUP CON A&E UTL SVC]="MOD",1,0)) AS [# MOD Actions],
Sum(IIf(Status="awarded",1,0)) AS [# Actions Awarded],
Sum(IIf(CARFinal="n",1,0)) AS [# Overdue CARS],
Sum(IIf(AwardDate>=DateRcvd,AwardDate-DateRcvd)) AS daystoaward,
Sum(IIf(Complete="y",1,0)) AS Awarddays,
Sum(IIf(datercvd < Date() - 30 AND completed Is Null)) AS daysoverdue,
Sum(Nz([PR $ Value])) AS [$ Value]
FROM SCD
WHERE (((SCD.Assigned)="Amie"));

You don't need the Iif, and I am not sure you need the Sum, unless it is a
totals query:

WHERE [daterecieved] < DateAdd("d",-30),Date()) AND [completed] Is Null
--
Dave Hargis, Microsoft Access MVP

I have a date field "daterecieved" i would like to query for items over 30
days old where a completed field is null. Please assist. I am trying to get
the number of items over 30 days old if the completed field is Null. The
daterecieved it the item I want to use to capture the 30 day.

I tried this and was unsuccessfull.
Sum(IIf(daterecieved < Date() - 30 AND completed Is Null)) AS daysoverdue

Thanks!!!

 

[lmossolle]

Error says missining operator at What When True?, What When False))

You have the criteria, but not field is defined to Sum on.
Sum(IIf(datercvd < Date() - 30 AND completed Is Nul, What When True?, What
When False)) AS daysoverdue,

--
Dave Hargis, Microsoft Access MVP

This is the SQL Iam using the 4th from the bottom, as daysover due is not
working.

SELECT
Sum(IIf(Assigned="Amie",1,0)) AS [Cumulative Actions],
Sum(IIf(Status="returned",1,0)) AS [# Actions Returned],
Sum(IIf(Status="pending",1,0)) AS [# Actions Pending],
Sum(IIf(Status="solicited",1,0)) AS [# Actions Solicited],
Sum(IIf([SUP CON A&E UTL SVC]="MOD",1,0)) AS [# MOD Actions],
Sum(IIf(Status="awarded",1,0)) AS [# Actions Awarded],
Sum(IIf(CARFinal="n",1,0)) AS [# Overdue CARS],
Sum(IIf(AwardDate>=DateRcvd,AwardDate-DateRcvd)) AS daystoaward,
Sum(IIf(Complete="y",1,0)) AS Awarddays,
Sum(IIf(datercvd < Date() - 30 AND completed Is Null)) AS daysoverdue,
Sum(Nz([PR $ Value])) AS [$ Value]
FROM SCD
WHERE (((SCD.Assigned)="Amie"));

You don't need the Iif, and I am not sure you need the Sum, unless it is a
totals query:

WHERE [daterecieved] < DateAdd("d",-30),Date()) AND [completed] Is Null
--
Dave Hargis, Microsoft Access MVP


:

I have a date field "daterecieved" i would like to query for items over 30
days old where a completed field is null. Please assist. I am trying to get
the number of items over 30 days old if the completed field is Null. The
daterecieved it the item I want to use to capture the 30 day.

I tried this and was unsuccessfull.
Sum(IIf(daterecieved < Date() - 30 AND completed Is Null)) AS daysoverdue

Thanks!!!

 

[Klatuu]

What I posted is not meant to be actual values in your query. I was trying
to show what should go in those places. For example:
Sum(IIf(Complete="y",1,0)) AS Awarddays,

In this case, if Complete = "y", you are adding one to the some. If it is
not "y" you are not adding anything to the some. So what you need is to
replace What When True? with a field or value to sum and replace What
When False with what to sum when not true, probably a zero.

Sum(IIf(datercvd < Date() - 30 AND completed Is Nul, What When True?, What
When False)) AS daysoverdue,
--
Dave Hargis, Microsoft Access MVP

Error says missining operator at What When True?, What When False))

You have the criteria, but not field is defined to Sum on.
Sum(IIf(datercvd < Date() - 30 AND completed Is Nul, What When True?, What
When False)) AS daysoverdue,

--
Dave Hargis, Microsoft Access MVP

This is the SQL Iam using the 4th from the bottom, as daysover due is not
working.

SELECT
Sum(IIf(Assigned="Amie",1,0)) AS [Cumulative Actions],
Sum(IIf(Status="returned",1,0)) AS [# Actions Returned],
Sum(IIf(Status="pending",1,0)) AS [# Actions Pending],
Sum(IIf(Status="solicited",1,0)) AS [# Actions Solicited],
Sum(IIf([SUP CON A&E UTL SVC]="MOD",1,0)) AS [# MOD Actions],
Sum(IIf(Status="awarded",1,0)) AS [# Actions Awarded],
Sum(IIf(CARFinal="n",1,0)) AS [# Overdue CARS],
Sum(IIf(AwardDate>=DateRcvd,AwardDate-DateRcvd)) AS daystoaward,
Sum(IIf(Complete="y",1,0)) AS Awarddays,
Sum(IIf(datercvd < Date() - 30 AND completed Is Null)) AS daysoverdue,
Sum(Nz([PR $ Value])) AS [$ Value]
FROM SCD
WHERE (((SCD.Assigned)="Amie"));

:

You don't need the Iif, and I am not sure you need the Sum, unless it is a
totals query:

WHERE [daterecieved] < DateAdd("d",-30),Date()) AND [completed] Is Null
--
Dave Hargis, Microsoft Access MVP


:

I have a date field "daterecieved" i would like to query for items over 30
days old where a completed field is null. Please assist. I am trying to get
the number of items over 30 days old if the completed field is Null. The
daterecieved it the item I want to use to capture the 30 day.

I tried this and was unsuccessfull.
Sum(IIf(daterecieved < Date() - 30 AND completed Is Null)) AS daysoverdue

Thanks!!!

 

[John W. Vinson]

I have a date field "daterecieved" i would like to query for items over 30
days old where a completed field is null. Please assist. I am trying to get
the number of items over 30 days old if the completed field is Null. The
daterecieved it the item I want to use to capture the 30 day.

I tried this and was unsuccessfull.
Sum(IIf(daterecieved < Date() - 30 AND completed Is Null)) AS daysoverdue

Thanks!!!

The Sum() appears to be the problem here - your description doesn't mention
totalling anything! What's the context?

A query

SELECT Count(*) FROM YourTable
WHERE DateReceived < DateAdd("d", -30, Date()) AND Completed IS NULL

would get a count of the overdue records; a query

SELECT <whatever fields you want to see>, DateDiff("d", [DateReceived],
Date()) AS DaysOverdue
WHERE [DateReceived] < DateAdd("d", -30, Date()) AND Completed IS NULL;

will show you each item and how many days have passed since DateReceived.