MS Access Query

[Mac1206]

Could someone please let me know if my query is correct...I keep getting a syntax error?

iif(Sum([Assignment_test]![CountOfPlanNumber]+[Assignment_test]![InvComplete])=0, "Complete", "Incomplete"))) AS Expr2
FROM Assignment_test;

 

[xenou]

Is this form code? It looks like a mishmash of raw sql + textbox elements. As raw SQL it's missing a "Select" and for form control its probably not right either. What are you trying to do?

 

[Mac1206]

Is this form code? It looks like a mishmash of raw sql + textbox elements. As raw SQL it's missing a "Select" and for form control its probably not right either. What are you trying to do?

SELECT Assignment_test.Assignment, Assignment_test.BillGroup, Assignment_test.BillGroupID, Assignment_test.CountOfPlanNumber, Assignment_test.InvComplete, IIf(Sum([Assignment_test]![CountOfPlanNumber]+[Assignment_test]![InvComplete])=0,"Complete","Incomplete") AS Complete_Incomplete
FROM Assignment_test;

If [Assignment_test]![CountOfPlanNumber]+[Assignment_test]![InvComplete])=0 then I want Complete in the field and if not then Incomplete for the field row....

 

[xenou]

Possibly,

SELECT 
	Assignment, 
	BillGroup, 
	BillGroupID, 
	CountOfPlanNumber, 
	InvComplete, 
	"Complete" AS Complete_Incomplete
FROM Assignment_test
WHERE
	Nz(CountOfPlanNumber,0) + Nz(InvComplete,0) = 0

UNION ALL

SELECT 
	Assignment, 
	BillGroup, 
	BillGroupID, 
	CountOfPlanNumber, 
	InvComplete, 
	"InComplete" AS Complete_Incomplete
FROM Assignment_test
WHERE
	Nz(CountOfPlanNumber,0) + Nz(InvComplete,0) <> 0

Also possible (with the return of IIF):

SELECT 
	Assignment, 
	BillGroup, 
	BillGroupID, 
	CountOfPlanNumber, 
	InvComplete, 
	IIF(Nz(CountOfPlanNumber,0) + Nz(InvComplete,0) = 0, "Complete", "Incomplete") AS Complete_Incomplete
FROM Assignment_test

 

[Micron]

A syntax error is a very specific type of error. Is that the exact error, or is it about not having a field as part of an aggregate function?
You are trying to use an aggregate function (SUM) as a calculated field, but don't have any type of Totals query key words. I would expect this should look more like

SELECT Assignment_test.Assignment, Assignment_test.BillGroup, Assignment_test.BillGroupID, Assignment_test.CountOfPlanNumber,
Assignment_test.InvComplete, IIf(Sum([Assignment_test]![CountOfPlanNumber]+[Assignment_test]![InvComplete])=0,"Complete","Incomplete") 
AS Complete_Incomplete FROM Assignment_test
GROUP BY Assignment_test.Assignment, Assignment_test.BillGroup, Assignment_test.BillGroupID, Assignment_test.CountOfPlanNumber,
Assignment_test.InvComplete;

Not that what I posted is exact, or will provide the desired results. When you build a Totals query, you often get more similar records than what you want, so the Union query might be a better option. I only wanted to point out why I thought the query wouldn't work, and that it seems to me the error was not a "syntax" error.

 

[Mac1206]

A syntax error is a very specific type of error. Is that the exact error, or is it about not having a field as part of an aggregate function?
You are trying to use an aggregate function (SUM) as a calculated field, but don't have any type of Totals query key words. I would expect this should look more like

SELECT Assignment_test.Assignment, Assignment_test.BillGroup, Assignment_test.BillGroupID, Assignment_test.CountOfPlanNumber,
Assignment_test.InvComplete, IIf(Sum([Assignment_test]![CountOfPlanNumber]+[Assignment_test]![InvComplete])=0,"Complete","Incomplete") 
AS Complete_Incomplete FROM Assignment_test
GROUP BY Assignment_test.Assignment, Assignment_test.BillGroup, Assignment_test.BillGroupID, Assignment_test.CountOfPlanNumber,
Assignment_test.InvComplete;

Not that what I posted is exact, or will provide the desired results. When you build a Totals query, you often get more similar records than what you want, so the Union query might be a better option. I only wanted to point out why I thought the query wouldn't work, and that it seems to me the error was not a "syntax" error.

That worked perfectly...Thanks so much for your support....OK

 

[Micron]

You're welcome.