Select statement syntax error

[Sevn]

I'm having trouble finding the syntax error in my statement.

Any help will be greatly appreciated.

Set Rs = CurrentDb.OpenRecordset ("SELECT [rptQry-CMvsCYTD-RepGrpCat].SRNum, [rptQry-CMvsCYTD-RepGrpCat].SalesRep FROM [rptQry-CMvsCYTD-RepGrpCat] GROUP BY [rptQry-CMvsCYTD-RepGrpCat].SRNum, [rptQry-CMvsCYTD-RepGrpCat].SalesRep HAVING ((([rptQry-CMvsCYTD-RepGrpCat].SRNum) Is Not Null And ([rptQry-CMvsCYTD-RepGrpCat].SRNum) <> "B45" And ([rptQry-CMvsCYTD-RepGrpCat].SRNum) <> "L01" And ([rptQry-CMvsCYTD-RepGrpCat].SRNum) <> "unknown")) ORDER BY [rptQry-CMvsCYTD-RepGrpCat].SRNum;")

Thanks,

 

[Alc]

This may not be the only fault, but having quotation marks within quoatation marks will be causing some problems.

Is this any better?

Set Rs = CurrentDb.OpenRecordset ("SELECT [rptQry-CMvsCYTD-RepGrpCat].SRNum, [rptQry-CMvsCYTD-RepGrpCat].SalesRep FROM [rptQry-CMvsCYTD-RepGrpCat] GROUP BY [rptQry-CMvsCYTD-RepGrpCat].SRNum, [rptQry-CMvsCYTD-RepGrpCat].SalesRep HAVING ((([rptQry-CMvsCYTD-RepGrpCat].SRNum) Is Not Null And ([rptQry-CMvsCYTD-RepGrpCat].SRNum) <> 'B45' And ([rptQry-CMvsCYTD-RepGrpCat].SRNum) <> 'L01' And ([rptQry-CMvsCYTD-RepGrpCat].SRNum) <> 'unknown')) ORDER BY [rptQry-CMvsCYTD-RepGrpCat].SRNum;")

 

[pbaldy]

For one thing, the double quotes around your values:

<> "B45"

terminate the string. Change those to single quotes.

 

[Sevn]

Thanks guys.

My original query was just eliminating null values (which I copied from SQL View), so I didn't have any quotes. When I added the additional criteria and copied from SQL View, Access added the double quotes. Therefore, that's what I pasted in to my VBA window. Is there a reason Access doesn't spit out VBA compliant code?

Thanks,