Could someone please help me figure out why I have a syntax error. Thanks.

(Syntax error (missing operator) in query expression ‘DataSource3.publisher = DataSource1.publisher LEFT OUTER JOIN (SELECT publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT……….)

SELECT DataSource1.publisher, IIf(IsNull([CountProjectMGMT]),"na", Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT)) AS AvgProjectMGMT,
IIf(IsNull([CountDesign]),"na", Val(DataSource1.SumDesign / DataSource3.CountDesign)) AS AvgDesign
FROM (SELECT publisher, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumProjectMGMT, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumDesign FROM evaluations GROUP BY publisher) AS DataSource1 LEFT OUTER JOIN
(SELECT publisher, COUNT(ps_design) AS CountDesign
FROM evaluations AS Table_1_2
WHERE (ps_design <> "na")
GROUP BY publisher) AS DataSource3 ON DataSource3.publisher = DataSource1.publisher LEFT OUTER JOIN
(SELECT publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT FROM evaluations AS Table_1_1
WHERE (ps_projectmgmt <> "na") GROUP BY publisher) AS DataSource2 ON DataSource2.publisher = DataSource1.publisher;

SELECT DataSource1.publisher,
IIf(IsNull([CountProjectMGMT]),"na", Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT)) AS AvgProjectMGMT,
IIf(IsNull([CountDesign]),"na", Val(DataSource1.SumDesign / DataSource3.CountDesign)) AS AvgDesign
FROM ((SELECT publisher,
SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumProjectMGMT,
SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumDesign
FROM evaluations
GROUP BY publisher) AS DataSource1
LEFT OUTER JOIN (SELECT publisher,
COUNT(ps_design) AS CountDesign
FROM evaluations AS Table_1_2
WHERE (ps_design <> "na")
GROUP BY publisher) AS DataSource3
ON DataSource3.publisher = DataSource1.publisher)
LEFT OUTER JOIN (SELECT publisher,
COUNT(ps_projectmgmt) AS CountProjectMGMT
FROM evaluations AS Table_1_1
WHERE (ps_projectmgmt <> "na")
GROUP BY publisher) AS DataSource2
ON DataSource2.publisher = DataSource1.publisher;

Thank you, that removed the syntax error. I have one other question. I am trying to add the following to the SQL statement but it's not working it's giving me a wrong output.

The dateSent field is in my evaluations table. Could we check it somehow in the following block of code:

(SELECT publisher,
COUNT(ps_design) AS CountDesign
FROM evaluations AS Table_1_2
WHERE (ps_design <> "na")
GROUP BY publisher) AS DataSource3
ON DataSource3.publisher = DataSource1.publisher)
LEFT OUTER JOIN (SELECT publisher,
COUNT(ps_projectmgmt) AS CountProjectMGMT
FROM evaluations AS Table_1_1
WHERE (ps_projectmgmt <> "na")
GROUP BY publisher

I tried something like this but the output is not right

WHERE (ps_design <> "na" AND dateSent>=#01/01/2011#)

I really don't understand what result you want. But to help you a little bit further:

When a field can contain either a numeric value, NULL of "na", You can use the following IIF function to change NULL and "na" in 0, so you can calculate with it:

Thanks Hans, it's really difficult to explain the problem I am having. For some reason when I add the dateSent field some output for AvgProjectMGMT and AvgDesign are weird numbers like 170 and 19. But it should be the average like 5 or 4.5 or ...) When I
remove the dateSent field from my query everything works great. I just don;t understand why the dateSent field would mess things up. Could you please take look again? Thanks.

SELECT DataSource1.publisher, IIf(IsNull([CountProjectMGMT]),'n/a', ROUND(Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT),1)) AS AvgProjectMGMT, IIf(IsNull([CountDesign]),'n/a', ROUND(Val(DataSource1.SumDesign/DataSource3.CountDesign),1)) AS AvgDesign FROM ((SELECT publisher, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]='na',0, Val([ps_projectmgmt]))) AS SumProjectMGMT, SUM(IIf(IsNull([ps_design]) OR [ps_design]='na',0, Val([ps_design]))) AS SumDesign FROM evaluations GROUP BY publisher) AS DataSource1 LEFT OUTER JOIN (SELECT publisher, COUNT(ps_design) AS CountDesign FROM evaluations AS Table_1_2 WHERE ((ps_design <> 'na') AND (pub='1') AND (dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#)) GROUP BY publisher) AS DataSource3 ON DataSource3.publisher = DataSource1.publisher) LEFT OUTER JOIN (SELECT publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT FROM evaluations AS Table_1_1 WHERE ((ps_projectmgmt <> 'na') AND (pub='1') AND (dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#)) GROUP BY publisher) AS DataSource2 ON DataSource2.publisher = DataSource1.publisher";

## lm111

Member

89 Points

126 Posts

## statement has a syntax error

Oct 03, 2011 01:36 PM|lm111|LINK

Could someone please help me figure out why I have a syntax error. Thanks.

(Syntax error (missing operator) in query expression ‘DataSource3.publisher = DataSource1.publisher LEFT OUTER JOIN (SELECT publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT……….)

## hans_v

All-Star

38912 Points

7121 Posts

## Re: statement has a syntax error

Oct 03, 2011 01:55 PM|hans_v|LINK

Try:

SELECT DataSource1.publisher,

IIf(IsNull([CountProjectMGMT]),"na", Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT)) AS AvgProjectMGMT,

IIf(IsNull([CountDesign]),"na", Val(DataSource1.SumDesign / DataSource3.CountDesign)) AS AvgDesign

FROM

((SELECT publisher,SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumProjectMGMT,

SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumDesign

FROM evaluations

GROUP BY publisher) AS DataSource1

LEFT OUTER JOIN (SELECT publisher,

COUNT(ps_design) AS CountDesign

FROM evaluations AS Table_1_2

WHERE (ps_design <> "na")

GROUP BY publisher) AS DataSource3

ON DataSource3.publisher = DataSource1.publisher

)LEFT OUTER JOIN (SELECT publisher,

COUNT(ps_projectmgmt) AS CountProjectMGMT

FROM evaluations AS Table_1_1

WHERE (ps_projectmgmt <> "na")

GROUP BY publisher) AS DataSource2

ON DataSource2.publisher = DataSource1.publisher;

## lm111

Member

89 Points

126 Posts

## Re: statement has a syntax error

Oct 04, 2011 04:09 PM|lm111|LINK

Thank you, that removed the syntax error. I have one other question. I am trying to add the following to the SQL statement but it's not working it's giving me a wrong output.

WHERE dateSent>=#01/01/2011#

Any idea where I can add the piece above?

Thanks

## hans_v

All-Star

38912 Points

7121 Posts

## Re: statement has a syntax error

Oct 04, 2011 09:12 PM|hans_v|LINK

In which table(s) do you want to check the dateSent field?

## lm111

Member

89 Points

126 Posts

## Re: statement has a syntax error

Oct 05, 2011 01:22 PM|lm111|LINK

The dateSent field is in my evaluations table. Could we check it somehow in the following block of code:

(SELECT publisher,

COUNT(ps_design) AS CountDesign

FROM evaluations AS Table_1_2

WHERE (ps_design <> "na")

GROUP BY publisher) AS DataSource3

ON DataSource3.publisher = DataSource1.publisher

)LEFT OUTER JOIN (SELECT publisher,

COUNT(ps_projectmgmt) AS CountProjectMGMT

FROM evaluations AS Table_1_1

WHERE (ps_projectmgmt <> "na")

GROUP BY publisher

I tried something like this but the output is not right

WHERE (ps_design <> "na" AND dateSent>=#01/01/2011#)

Thanks

## hans_v

All-Star

38912 Points

7121 Posts

## Re: statement has a syntax error

Oct 05, 2011 01:38 PM|hans_v|LINK

This is correct syntax

The only way to solve this is that you try to explain in words which result you want. Show also some records and the result you want

## lm111

Member

89 Points

126 Posts

## Re: statement has a syntax error

Oct 05, 2011 01:44 PM|lm111|LINK

Hello,

Here is the link to my other thread that explains it better.

http://forums.asp.net/t/1723766.aspx/1?Display+correct+data+in+GridView

The answer from 'mezzanine74' works fine I just need to figure out how to add the dateSent field.

Thank you so much.

## hans_v

All-Star

38912 Points

7121 Posts

## Re: statement has a syntax error

Oct 05, 2011 01:54 PM|hans_v|LINK

I really don't understand what result you want. But to help you a little bit further:

When a field can contain either a numeric value, NULL of "na", You can use the following IIF function to change NULL and "na" in 0, so you can calculate with it:

IIF(IsNumeric([ps_projectmgmt]), [ps_projectmgmt], 0)

## lm111

Member

89 Points

126 Posts

## Re: statement has a syntax error

Oct 05, 2011 03:22 PM|lm111|LINK

Thanks Hans, it's really difficult to explain the problem I am having. For some reason when I add the dateSent field some output for AvgProjectMGMT and AvgDesign are weird numbers like 170 and 19. But it should be the average like 5 or 4.5 or ...) When I remove the dateSent field from my query everything works great. I just don;t understand why the dateSent field would mess things up. Could you please take look again? Thanks.