This is a multi-part message in MIME format.
------=_NextPart_000_0021_01C8EBCB.1040A6A0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_0022_01C8EBCB.1040A6A0"
------=_NextPart_001_0022_01C8EBCB.1040A6A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello all -
I am trying to execute an UPDATE SQL statement using ADO with a =
connection object set to CurrentProject.Connection. =20
Here is the UPDATE statement my script creates -
UPDATE [__Raw Promo Data] SET ExcludeTime =3D 0, StartHour =3D 0, =
EndHour =3D 2 WHERE SegmentID =3D 2 AND CohortID =3D 2 AND SourceID =3D =
6 AND PromoID =3D 1 AND RptDate =3D #6/30/2008# AND OffDayPromo =3D 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=20
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=20
130858 2 2 6 1007 6/30/2008 3 23 No 0 9 Win a MacBook Air! Yes No =
No No 1=20
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
------=_NextPart_001_0022_01C8EBCB.1040A6A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16525" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Hello all -</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I am trying to execute an UPDATE SQL =
statement=20
using ADO with a connection object set to =
CurrentProject.Connection. =20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Here is the UPDATE statement my script =
creates=20
-</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>UPDATE [__Raw Promo Data] SET =
ExcludeTime =3D 0,=20
StartHour =3D 0, EndHour =3D 2 WHERE SegmentID =3D 2 AND CohortID =3D 2 =
AND SourceID =3D 6=20
AND PromoID =3D 1 AND RptDate =3D #6/30/2008# AND OffDayPromo =3D =
1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Sample of the table =
below...</FONT></DIV>
<DIV>
<TABLE cellSpacing=3D0 bgColor=3D#ffffff border=3D1>
<CAPTION><FONT face=3DArial size=3D2>__Raw Promo Data</FONT></CAPTION>
<THEAD>
<TR>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>RawPromoID</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>SegmentID</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>CohortID</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>SourceID</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>StructureID</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>RptDate</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>StartHour</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>EndHour</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>ExcludeTime</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>NCT</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>PCT</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>DisplayText</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>OffDayPromo</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>DefaultPromo</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>AMPromo</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>ALTPromo</FONT></TH>
<TH borderColor=3D#000000 bgColor=3D#c0c0c0><FONT face=3DArial=20
size=3D2>PromoID</FONT></TH></TR></THEAD>
<TBODY>
<TR vAlign=3Dtop>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial=20
size=3D2>131690</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>2</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>2</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>6</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>5124</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial=20
size=3D2>6/30/2008</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>3</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>23</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>0</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>10</FONT></TD>
<TD borderColor=3D#c0c0c0><FONT face=3DArial size=3D2>WALL-E: It's a =
Movie &=20
a Game</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>1</FONT></TD></TR>
<TR vAlign=3Dtop>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial=20
size=3D2>130858</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>2</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>2</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>6</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>1007</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial=20
size=3D2>6/30/2008</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>3</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>23</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>0</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>9</FONT></TD>
<TD borderColor=3D#c0c0c0><FONT face=3DArial size=3D2>Win a MacBook=20
Air!</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>Yes</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial =
size=3D2>No</FONT></TD>
<TD borderColor=3D#c0c0c0 align=3Dright><FONT face=3DArial=20
size=3D2>1</FONT></TD></TR></TBODY>
<TFOOT><FONT face=3DArial size=3D2></FONT></TFOOT></TABLE></DIV>
<DIV><FONT face=3DArial size=3D2>ExcludeTime, OffdayPromo, DefaultPromo, =
AMPromo and=20
ALTPromo are all Yes/No fields in MDB.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Update SQL query is created and =
executed in the=20
attached text file. Basically, a group by SELECT statement pulls =
the all=20
records (like the first one row in the above table) and then builds =
the=20
UPDATE query, which **should** update the 2nd row. That isn't=20
happening. Anyone know why and how to fix this so it will=20
work?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in advance!</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Rob</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV></BODY></HTML>
------=_NextPart_001_0022_01C8EBCB.1040A6A0--
------=_NextPart_000_0021_01C8EBCB.1040A6A0
Content-Type: text/plain;
name="Offdayfunc.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="Offdayfunc.txt"
Private Function UpdateOffDayFields() As Long
'Define Database Objects...
Dim rs As ADODB.Recordset, sSQL As String, lCount As Long, iEndTime =
As Integer
Set rs =3D New ADODB.Recordset
Set oDB =3D CurrentProject.Connection
sSQL =3D "SELECT SegmentID, CohortID, SourceID, PromoID, RptDate, =
MIN(StartHour) AS StartTime FROM [__Raw Promo Data] " & _
"WHERE ExcludeTime =3D 0 AND PromoID IS NOT NULL AND =
OffDayPromo =3D 0 " & _
"GROUP BY SegmentID, CohortID, SourceID, PromoID, RptDate " & =
_
"ORDER BY SegmentID, CohortID, SourceID, PromoID, RptDate"
=20
With rs
=20
'open a connection...
.Open sSQL, oDB, 3, 3, 1
=20
'Update all off-day promo records with correct StartHour and =
EndHour values.
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
iEndTime =3D CInt(.Fields("StartTime").Value) - 1
If iEndTime < 0 Then iEndTime =3D 0
sSQL =3D "UPDATE [__Raw Promo Data] SET ExcludeTime =3D =
0, StartHour =3D 0, EndHour =3D " & iEndTime & " " & _
"WHERE SegmentID =3D " & =
..Fields("SegmentID").Value & " AND CohortID =3D " & =
..Fields("CohortID").Value & " AND " & _
"SourceID =3D " & .Fields("SourceID").Value & " =
AND PromoID =3D " & .Fields("PromoID").Value & " AND " & _
"RptDate =3D #" & .Fields("RptDate").Value & "# =
AND OffDayPromo =3D 1"
Debug.Print sSQL
oDB.Execute (sSQL)
lCount =3D lCount + 1
.MoveNext
Loop
End If
=20
'done with current table...
.Close
=20
End With
=20
ExitNow:
'Done... clean up objects...
Set rs =3D Nothing
Set oDB =3D Nothing
UpdateOffDayFields =3D lCount
End Function
------=_NextPart_000_0021_01C8EBCB.1040A6A0--
|
