help with Update Query in VBA code

[Rob]

Hello all -

I am trying to execute an UPDATE SQL statement using ADO with a connection object set to CurrentProject.Connection.

Here is the UPDATE statement my script creates -
UPDATE [__Raw Promo Data] SET ExcludeTime = 0, StartHour = 0, EndHour = 2 WHERE SegmentID = 2 AND CohortID = 2 AND SourceID = 6 AND PromoID = 1 AND RptDate = #6/30/2008# AND OffDayPromo = 1

Sample of the table below...
__Raw Promo Data RawPromoID SegmentID CohortID SourceID StructureID RptDate StartHour EndHour ExcludeTime NCT PCT DisplayText OffDayPromo DefaultPromo AMPromo ALTPromo PromoID
131690 2 2 6 5124 6/30/2008 3 23 No 0 10 WALL-E: It's a Movie & a Game No No No No 1
130858 2 2 6 1007 6/30/2008 3 23 No 0 9 Win a MacBook Air! Yes No No No 1

ExcludeTime, OffdayPromo, DefaultPromo, AMPromo and ALTPromo are all Yes/No fields in MDB.

Update SQL query is created and executed in the attached text file. Basically, a group by SELECT statement pulls the all records (like the first one row in the above table) and then builds the UPDATE query, which **should** update the 2nd row. That isn't happening. Anyone know why and how to fix this so it will work?

Thanks in advance!

Rob

 

[Rob]

I figured this out... values for "yes" = -1 (not 1 like in my query)...

Hello all -

I am trying to execute an UPDATE SQL statement using ADO with a connection object set to CurrentProject.Connection.

Here is the UPDATE statement my script creates -
UPDATE [__Raw Promo Data] SET ExcludeTime = 0, StartHour = 0, EndHour = 2 WHERE SegmentID = 2 AND CohortID = 2 AND SourceID = 6 AND PromoID = 1 AND RptDate = #6/30/2008# AND OffDayPromo = 1

Sample of the table below...
__Raw Promo Data RawPromoID SegmentID CohortID SourceID StructureID RptDate StartHour EndHour ExcludeTime NCT PCT DisplayText OffDayPromo DefaultPromo AMPromo ALTPromo PromoID
131690 2 2 6 5124 6/30/2008 3 23 No 0 10 WALL-E: It's a Movie & a Game No No No No 1
130858 2 2 6 1007 6/30/2008 3 23 No 0 9 Win a MacBook Air! Yes No No No 1

ExcludeTime, OffdayPromo, DefaultPromo, AMPromo and ALTPromo are all Yes/No fields in MDB.

Update SQL query is created and executed in the attached text file. Basically, a group by SELECT statement pulls the all records (like the first one row in the above table) and then builds the UPDATE query, which **should** update the 2nd row. That isn't happening. Anyone know why and how to fix this so it will work?

Thanks in advance!

Rob