Statement Help

  • Thread starter Thread starter lmossolle
  • Start date Start date
L

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,
 
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.
 
That worked great, could you assist with this?

Sum(AwardDate) AS AwardDays,

Klatuu said:
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


lmossolle said:
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,
Click to expand...
Click to expand...
 
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"));

Klatuu said:
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


lmossolle said:
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,
Click to expand...
Click to expand...
 
You can't sum a date
--
Dave Hargis, Microsoft Access MVP


lmossolle said:
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"));

Klatuu said:
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


lmossolle said:
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,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top