I am getting the error mentioned on the subject line when running this query on the front end. I am providing the query to make it easy. Can anyone help me on this issue!!
Regards

sinapra

__________________
sinapra

sorry guys forgot to post the query : here it is

'SELECT People.People, People.Name,
Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#8/20/2004#,1,0))) AS Created,
Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#8/20/2004#,IIf(IsNull([ProjectStatusReport]![Accomplishments])
Or IsNull([ProjectStatusReport]![Plans]),0,1),0))) AS Complete, People.email FROM (((Register INNER JOIN SR_Status
ON Register.SR_Status = SR_Status.SR_Status) LEFT JOIN ProjectStatusReport
ON Register.Register = ProjectStatusReport.Project) INNER JOIN StakeHolders
ON Register.Register = StakeHolders.Register) INNER JOIN People
ON StakeHolders.People = People.People WHERE (((SR_Status.Status)='Active') AND ((StakeHolders.Status)=Yes))
GROUP BY People.People, People.Name, People.email
HAVING (((Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#8/20/2004#,1,0))))>0)
AND ((Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#8/20/2004#,IIf(IsNull([ProjectStatusReport]![Accomplishments])
Or IsNull([ProjectStatusReport]![Plans]),0,1),0))))>0)) ORDER BY People.People'

thank u

sinapra

Not sure what database you use.(access/sql server)

Replace all ! with .

Using DOT here - People.People, but wondering why you are using ! in the next line - [ProjectStatusReport]![Week];)

_________________________
- Vijay G
Strive for Perfection

Hi Vijay

I had the same thing in my mind, but the script was written by the support guys in my team long back. I may not be able to change this but is there anything that could explain me the significance of '!'.

Thank you and Regards

sinapra

forgot to mention, we are using SQL server

thanks again and regards

sinapra

hmmm, as it says "INCORRECT SYNTAX NEAR !", that is more than enough to explain. I really wonder, why part of it was used with DOT and remaining with !.

In SQL server you cannot use ! as for referencing objects.

. is used for object referencing

Eg: TableName.ColumnName is how you can reference in sql.
If I am right, ! is VB code that is used to do the same as DOT does.

So you got to change all !s to DOTs

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks Vijay,
it helps
cheers

sinapra

Looks like you are using an Access Project file??? If that is the case, [ProjectStatusReport]![Week] most likely references an open report called ProjectStatusReport and a control on that report called Week.

Do you have a report named ProjectStatusReport?
Is that report open when the query is run?

hi U.N.C.L.E.
ProjectStatusReport is not a report, its a table name and week is the field name of date type. I would need to clarify this from my support and get back to you so that I can explain the usage of '!' in the query.
Regards

sinapra