What's wrong with this code? Compile Error...

[Layla]

I thought this was ok.... but... I get the following message: COMPILE
ERROR- EXPECTED END OF STATEMENT and the "Where" is highlighted. I'm a
bit of a novice at this so any help at all would be much appreciated!!

Thanks!
Private Sub Text18_AfterUpdate()

Dim db As Database
Dim strSQL As String

Set db = CurrentDb
strSQL = "UPDATE tblBusinessObjectives, tblPeriodDates SET
tblBusinessObjectives.intPeriod = tblPeriodDates!Period" WHERE
(((tblBusinessObjectives.DateCreated)>=[tblPeriodDates]![Start_Date]
And (tblBusinessObjectives.DateCreated)<=[tblPeriodDates]![End_Date]))

db.Execute strSQL
db.Close
End Sub

[fredg]

On 18 Jan 2005 09:30:28 -0800, Layla wrote:

I thought this was ok.... but... I get the following message: COMPILE
ERROR- EXPECTED END OF STATEMENT and the "Where" is highlighted. I'm a
bit of a novice at this so any help at all would be much appreciated!!

Thanks!

Private Sub Text18_AfterUpdate()

Dim db As Database
Dim strSQL As String

Set db = CurrentDb
strSQL = "UPDATE tblBusinessObjectives, tblPeriodDates SET
tblBusinessObjectives.intPeriod = tblPeriodDates!Period" WHERE
(((tblBusinessObjectives.DateCreated)>=[tblPeriodDates]![Start_Date]
And (tblBusinessObjectives.DateCreated)<=[tblPeriodDates]![End_Date]))

db.Execute strSQL
db.Close

End Sub.

Move the double quote before the word WHERE to the end of the SQL.
Everything you have after that " is not in the strSQL string.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Matthias Klaey]

la*******@hotmail.com (Layla) wrote:

I thought this was ok.... but... I get the following message: COMPILE
ERROR- EXPECTED END OF STATEMENT and the "Where" is highlighted. I'm a
bit of a novice at this so any help at all would be much appreciated!!

Thanks!
Private Sub Text18_AfterUpdate()

Dim db As Database
Dim strSQL As String

Set db = CurrentDb
strSQL = "UPDATE tblBusinessObjectives, tblPeriodDates SET
tblBusinessObjectives.intPeriod = tblPeriodDates!Period" WHERE
(((tblBusinessObjectives.DateCreated)>=[tblPeriodDates]![Start_Date]
And (tblBusinessObjectives.DateCreated)<=[tblPeriodDates]![End_Date]))

db.Execute strSQL
db.Close
End Sub

There is a double quote " just before WHILE that should not be there.
Is the code folowing strSQL = all on one line? If yes, the double
quote shoult be at the very end of the line.

You can break the statement into several lines as follows:

strSQL = "UPDATE tblBusinessObjectives, tblPeriodDates " & _
" SET tblBusinessObjectives.intPeriod = tblPeriodDates!Period " & _
" WHERE (((tblBusinessObjectives.DateCreated) >= " & _
" [tblPeriodDates]![Start_Date] " & _
" And (tblBusinessObjectives.DateCreated) <= " & _
" [tblPeriodDates]![End_Date]));"

That is, you write a continuation line with a Space and an Underline,
but then you have to put together the separate strings with the &
operator. You cannot break a line in the middle of a string.
(I'm using too many line breaks here because of the wrapping of the
Newsgroups editor.)

HTH
Matthias Kläy
--
www.kcc.ch