ODBC Error in query (client/server SQLServer)

[ahjeck]

Hi, I have a problem with my query which works fine prior to upsizing to SQL Server. Now I have an Access Front-end with SQL Server (2005) back-end client/server setup. This problem has bugged me for over one full day and I can't find anything that seems to solve it. Any suggestions is appreciated.

The error that comes up is this:

Run-time error '3146':
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='. (#102)

The line that's producing the error is a DoCmd.RunSQL "SELECT tblPBC..." The full statement is below. I can't seem to see anything wrong w/ my syntax below....

SELECT tblPBC.ProgramSN, tblChild.Community, Count(tblPBC.ChildID) AS CountOfChildID 
INTO tmpProgramParticipationSource 
FROM tblChild LEFT JOIN tblPBC 
ON (tblChild.ChildID = tblPBC.ChildID) 
GROUP BY tblPBC.ProgramSN, tblChild.Community, (Date()-tblPBC.EnteredDate) <= 29 
HAVING (((Date()-tblPBC.EnteredDate)<=29)=True);

I have checked and nothing appears to be null. The statement works fine if I remove the GROUP BY (Date()-tblPBC.EnteredDate) <= 29 and HAVING (Date()-tblPBC.EnteredDate) <= 29 = True, but that's not what I want... Datatypes appear to be fine as well.

tblPBC.EnteredDate Datetime

 

[WayneRyan]

ahjeck,

I think that this:

HAVING (((Date()-tblPBC.EnteredDate)<=29)=True);

Should be:

HAVING Date() - tblPBC.EnteredDate <= 29;

Don't think you need or want the = True.

Wayne

 

[ahjeck]

Thanks for the reply WayneRyan,

I followed your suggestion by removing the =True from the HAVING portion, but the same error message persist.

I tried another approach by inserting this statement into SQL Management Studio and tried executing the query against the db and an error popped up stating:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '<'.

The SQL Statement I used were a little different because Date() was an unknown function to SQL server:

SELECT tblPBC.ProgramSN, tblChild.Community, Count(tblPBC.ChildID) AS CountOfChildID 
INTO tmpProgramParticipationSource 
FROM tblChild LEFT JOIN tblPBC 
ON (tblChild.ChildID = tblPBC.ChildID) 
GROUP BY tblPBC.ProgramSN, tblChild.Community, datediff(day,tblPBC.EnteredDate,getdate()) <= 29 
HAVING datediff(day,tblPBC.EnteredDate,getdate()) <= 29;

 

[WayneRyan]

ahjeck,

Remove the <= 29, you don't need it for grouping, the Having takes care of
the <= 29.

GROUP BY tblPBC.ProgramSN, tblChild.Community, datediff(day,tblPBC.EnteredDate,getdate()) <= 29

Wayne

 

[ahjeck]

Thanks for the reply again WayneRyan.

Ok I removed the <=29 from the GROUP By and it works now! Thanks a lot for your help! I learned a lot about how GROUP BY works now just from this problem alone.

 

[WayneRyan]

ahjeck,

Glad it works.

I should learn to read the whole post before responding.

Wayne