Query Help

[lmossolle]

I am using office 2007. I had this query in Office 03 working, now I get a
data type mismatch in criteria expression. Could someone please assist?

Thanks,
Lee

SELECT
Sum(IIf(Assigned,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(Type="Mod",1,0)) AS [# MOD Actions],
Sum(IIf(Status="Awarded",1,0)) AS [# Actions Awarded],
Sum(IIf(CAR_Final="No",1,0)) AS [# Overdue CARS],
Sum(IIf(Award_Date>=Date_Rec,Award_Date-Date_Rec)) AS daystoaward,
Sum(IIf(Complete="Yes",1,0)) AS Awarddays,
Sum(IIf(Type="Mod",1,0)) AS MOD,
Sum(IIf(Type="Supplies",1,0)) AS Supplies,
Sum(IIf(Type="Construction",1,0)) AS Construction,
Sum(IIf(Type="Utilities",1,0)) AS Utlities,
Sum(IIf(Type="A&E",1,0)) AS [A and E],
Sum(IIf(Type="Services",1,0)) AS Services,
Sum(IIf(Date_Rec<Date()-30 And complete="No",1,0)) AS daysoverdue,
Sum(Nz([PR_$_Value])) AS [$ Value]
FROM

;

 

[Dorian]

We don't know how your fields are defined.
Make sure you only compare strings to strings and numbers to numbers and
make sure no field can contain a null unless you have coded for it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Dorian]

The othere thing you can try is to eliminate the lines one by one in order to
zero in on the offending statement.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John W. Vinson]

I am using office 2007. I had this query in Office 03 working, now I get a
data type mismatch in criteria expression. Could someone please assist?

Thanks,
Lee

SELECT
Sum(IIf(Assigned,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(Type="Mod",1,0)) AS [# MOD Actions],
Sum(IIf(Status="Awarded",1,0)) AS [# Actions Awarded],
Sum(IIf(CAR_Final="No",1,0)) AS [# Overdue CARS],
Sum(IIf(Award_Date>=Date_Rec,Award_Date-Date_Rec)) AS daystoaward,
Sum(IIf(Complete="Yes",1,0)) AS Awarddays,
Sum(IIf(Type="Mod",1,0)) AS MOD,
Sum(IIf(Type="Supplies",1,0)) AS Supplies,
Sum(IIf(Type="Construction",1,0)) AS Construction,
Sum(IIf(Type="Utilities",1,0)) AS Utlities,
Sum(IIf(Type="A&E",1,0)) AS [A and E],
Sum(IIf(Type="Services",1,0)) AS Services,
Sum(IIf(Date_Rec<Date()-30 And complete="No",1,0)) AS daysoverdue,
Sum(Nz([PR_$_Value])) AS [$ Value]
FROM

;

I'm GUESSING - again, *you aren't providing much information* - that your
Complete field or your CAR_Final field are in fact Yes/No fields. A Yes/No
field is stored as -1 for Yes/True/checkbox checked, 0 for No/False/unchecked.
As such a criterion using the text string "No" or "Yes" will fail (2003
kindly converts it for you), but a criterion of No or Yes (without the
quotes) will work.