Query Syntax

[redrover]

I have a compiler issue with the syntax for my recordset query. Can someone
help me identify the syntax error?

Private Sub TQDate_AfterUpdate()
'If sw_dte = "0" Then
' sw_dte = "1"
' AutoDte = Me.TQDate
'End If
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
set rs = db.Open("SELECT [TQDate].[qryDisplayTrainingResultsCopyQ] &_
FROM [qryDisplayTrainingResultsCopyQ] &_
WHERE [EvalNbr].[qryDisplayTrainingResultsCopyQ] = " & me.[TQDate])"
rs.MoveFirst
Do While Not rs.EOF
If IsNull(rs![TQDate]) Then
With rs.Edit
rs!![TQDate] = Me.[TQDate].Update
End With
End If
rs.MoveNext
Loop
End Sub

The error is pointing to the state for 'set rs ='.

 

[Dirk Goldgar]

I have a compiler issue with the syntax for my recordset query. Can
someone
help me identify the syntax error?

Private Sub TQDate_AfterUpdate()
'If sw_dte = "0" Then
' sw_dte = "1"
' AutoDte = Me.TQDate
'End If
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
set rs = db.Open("SELECT [TQDate].[qryDisplayTrainingResultsCopyQ] &_
FROM [qryDisplayTrainingResultsCopyQ] &_
WHERE [EvalNbr].[qryDisplayTrainingResultsCopyQ] = " & me.[TQDate])"
rs.MoveFirst
Do While Not rs.EOF
If IsNull(rs![TQDate]) Then
With rs.Edit
rs!![TQDate] = Me.[TQDate].Update
End With
End If
rs.MoveNext
Loop
End Sub

The error is pointing to the state for 'set rs ='.

You need to end the string literal on each line with closing quote, and keep
the reference to Me.TQDate outside the literal, like this:

Set rs = db.Open( _
"SELECT [TQDate].[qryDisplayTrainingResultsCopyQ] " & _
"FROM [qryDisplayTrainingResultsCopyQ] " &_
"WHERE [EvalNbr].[qryDisplayTrainingResultsCopyQ] = " & Me.TQDate)

 

[redrover]

THank you -- I'll give that a go!

I have a compiler issue with the syntax for my recordset query. Can
someone
help me identify the syntax error?

Private Sub TQDate_AfterUpdate()
'If sw_dte = "0" Then
' sw_dte = "1"
' AutoDte = Me.TQDate
'End If
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
set rs = db.Open("SELECT [TQDate].[qryDisplayTrainingResultsCopyQ] &_
FROM [qryDisplayTrainingResultsCopyQ] &_
WHERE [EvalNbr].[qryDisplayTrainingResultsCopyQ] = " & me.[TQDate])"
rs.MoveFirst
Do While Not rs.EOF
If IsNull(rs![TQDate]) Then
With rs.Edit
rs!![TQDate] = Me.[TQDate].Update
End With
End If
rs.MoveNext
Loop
End Sub

The error is pointing to the state for 'set rs ='.

You need to end the string literal on each line with closing quote, and keep
the reference to Me.TQDate outside the literal, like this:

Set rs = db.Open( _
"SELECT [TQDate].[qryDisplayTrainingResultsCopyQ] " & _
"FROM [qryDisplayTrainingResultsCopyQ] " &_
"WHERE [EvalNbr].[qryDisplayTrainingResultsCopyQ] = " & Me.TQDate)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)