Mising Operator Error 3075

[anon1971]

Can anyone tell me why I get the 3075 error in this code I am seeing cross eyed at this point? Thanks

DoCmd.RunSQL "UPDATE MailingList2 SET MailingList2.SS = True WHERE (((MailingList2.Address)having count(*) > 1)));", -1

 

[gmmastros]

I see a couple of problems here.

First, you are setting a column to the value True. Microsoft SQL Server would not recognize this. For Booleans, you should use 1 or 0.

[tt]Set MailingList2.SS = 1[/tt]

The biggest problem here is that you have a WHERE clause in your query, but the where condition is not valid. You have:

[tt]WHERE (((MailingList2.Address)having count(*) > 1)))[/tt]

What is MailingList2.Address doing in there? You are not comparing it to anything. You are missing the comparison operator (just like the error message is telling you).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon1971]

Ok I am trying to set the value to true if it finds more than one duplacate address?

 

[markros]

If you're using SQL 2005 and up, use this:

;with cte as (select PK, count(Address) over (partition by SomeField) as cntAddresses from MailingList2)

update cte set SS = 'true' where cntAddresses > 1

Which field in the mailinglist2 table should be a group field? You will need to put this field(s) into PARTITION clause.

PluralSight Learning Library

 

[anon1971]

thanks for the response now its giveing a 3319 error on union query:


DoCmd.RunSQL "(; with cte as select PK, count(Address) over (partition by Address) as cntAddresses from MailingList2)update cte set SS = 'true' where cntAddresses > 1"

 

[markros]

Remove ( at the beginning of the string.

DoCmd.RunSQL "; with cte as (select PK, count(Address) over (partition by Address) as cntAddresses from MailingList2) update cte set SS = 'true' where cntAddresses > 1"

Also, SELECT PK - by PK I meant the primary key from the MailingList2 table. What is this field - you need to use it here instead of PK.

PluralSight Learning Library

 

[anon1971]

Now its given a invaild sql statement error

DoCmd.RunSQL "; with cte as (select MailID, count(Address) over (partition by Address) as cntAddresses from MailingList2) update cte set SS = 'true' where cntAddresses > 1"

 

[gmmastros]

Are you using a microsoft SQL Server database, or are you using a Microsoft Access database?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

If you're using SQL Server 2005 and up, can you first test this statement in SSMS and find if it's working (make it work). Only then let's worry of running it from the application.

PluralSight Learning Library