Statement Help

[lmossolle]

Could someone assist with the following? I am getting errors!

Sum(IIf([Award Date]) >= ([Date Rcvd]), ([Award Date])-([Date Rcvd])) AS [#
Days to Award]

Thanks,

 

[Klatuu]

Sum(IIf([Award Date] >= [Date Rcvd], DateDiff("d",[Date Rcvd], [Award Date])
AS [# Days to Award],0))

There were some syntax problems and some best practice issues.
First you should never have spaces or special characters in names. They
should contain only numbers, letters, and the underscore character. Althouhg
wrapping a poorly formed name in brackets usually avoids problems, I have
seen cases where even that doesn't work.

Also it is better to use the DateDiff function or DateAdd functions to do
date aritimetic.

 

[tedmi]

The IIF functions requires three arguments, your code has only 2. Look it up
in help.

 

[lmossolle]

That worked great, could you assist with this?

Sum(AwardDate) AS AwardDays,

Sum(IIf([Award Date] >= [Date Rcvd], DateDiff("d",[Date Rcvd], [Award Date])
AS [# Days to Award],0))

There were some syntax problems and some best practice issues.
First you should never have spaces or special characters in names. They
should contain only numbers, letters, and the underscore character. Althouhg
wrapping a poorly formed name in brackets usually avoids problems, I have
seen cases where even that doesn't work.

Also it is better to use the DateDiff function or DateAdd functions to do
date aritimetic.
--
Dave Hargis, Microsoft Access MVP

Could someone assist with the following? I am getting errors!

Sum(IIf([Award Date]) >= ([Date Rcvd]), ([Award Date])-([Date Rcvd])) AS [#
Days to Award]

Thanks,

 

[lmossolle]

Having errors on last sum statement please assist!!!!

SELECT
Sum(IIf(Assigned="Carla",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(AwardDate) AS AwardDays,
FROM SCD
WHERE (((SCD.Assigned)="Carla"));

Sum(IIf([Award Date] >= [Date Rcvd], DateDiff("d",[Date Rcvd], [Award Date])
AS [# Days to Award],0))

There were some syntax problems and some best practice issues.
First you should never have spaces or special characters in names. They
should contain only numbers, letters, and the underscore character. Althouhg
wrapping a poorly formed name in brackets usually avoids problems, I have
seen cases where even that doesn't work.

Also it is better to use the DateDiff function or DateAdd functions to do
date aritimetic.
--
Dave Hargis, Microsoft Access MVP

Could someone assist with the following? I am getting errors!

Sum(IIf([Award Date]) >= ([Date Rcvd]), ([Award Date])-([Date Rcvd])) AS [#
Days to Award]

Thanks,

 

[Klatuu]

You can't sum a date
--
Dave Hargis, Microsoft Access MVP

Having errors on last sum statement please assist!!!!

SELECT
Sum(IIf(Assigned="Carla",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(AwardDate) AS AwardDays,
FROM SCD
WHERE (((SCD.Assigned)="Carla"));

Sum(IIf([Award Date] >= [Date Rcvd], DateDiff("d",[Date Rcvd], [Award Date])
AS [# Days to Award],0))

There were some syntax problems and some best practice issues.
First you should never have spaces or special characters in names. They
should contain only numbers, letters, and the underscore character. Althouhg
wrapping a poorly formed name in brackets usually avoids problems, I have
seen cases where even that doesn't work.

Also it is better to use the DateDiff function or DateAdd functions to do
date aritimetic.
--
Dave Hargis, Microsoft Access MVP

Could someone assist with the following? I am getting errors!

Sum(IIf([Award Date]) >= ([Date Rcvd]), ([Award Date])-([Date Rcvd])) AS [#
Days to Award]

Thanks,

 

[Klatuu]

It requires 3 in VBA, but only 2 in SQL

 

[Marshall Barton]

Having errors on last sum statement please assist!!!!

SELECT
Sum(IIf(Assigned="Carla",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(AwardDate) AS AwardDays,
FROM SCD
WHERE (((SCD.Assigned)="Carla"));

You have an extra comma on that line.

Summing a Date value is kind of meaningless. Did you mean
to sum the number of days between that field and some other
field ?