extra ) in query expression/sql statement issues

[Guest]

Hi,
Can anyone help me with this? I have no idea why this is generating an error
"extra ) in query expression" and also it's not getting the value of
TrackNoValid field... Thanks!




strLMReleased = "SELECT [TrackNo VALIDATE tbl].TrackNoValid, [TrackNo
VALIDATE tbl].LSTReleaseDate " & _
"FROM [TrackNo VALIDATE tbl] " & _
"WHERE (([TrackNo VALIDATE tbl].TrackNoValid) = " &
[Forms]![frmListReleaseTrack]![ListRelease Track tbl sbfrm]![TrackNoValid] &
");"

MsgBox strLMReleased

Debug.Print strLMReleased

Set rstTables = dbsList.OpenRecordset(strLMReleased)
If Not rstTables.EOF Then
With rstTables
.MoveLast
.MoveFirst

If IsNull(rstTables!LSTReleaseDate) Then
MsgBox "This job has not been released by List Maintenance. Please
make sure the job is released before processing.", , "Cannot Process Job"
rstTable.Close
Exit Sub
End If

End With

End If
rstTables.Close

 

[Stefan Hoffmann]

hi Anna,

Can anyone help me with this? I have no idea why this is generating an error
"extra ) in query expression" and also it's not getting the value of
TrackNoValid field... Thanks!
strLMReleased = "SELECT [TrackNo VALIDATE tbl].TrackNoValid, [TrackNo
VALIDATE tbl].LSTReleaseDate " & _
"FROM [TrackNo VALIDATE tbl] " & _
"WHERE (([TrackNo VALIDATE tbl].TrackNoValid) = " &
[Forms]![frmListReleaseTrack]![ListRelease Track tbl sbfrm]![TrackNoValid] &
");"

Why don't you remove the brackets? They just mark the normal operator
precedence and have no impact on the result of the query.


mfG
--> stefan <--

 

[pietlinden]

Hi,
Can anyone help me with this? I have no idea why this is generating an error
"extra ) in query expression" and also it's not getting the value of
TrackNoValid field... Thanks!


strLMReleased = "SELECT [TrackNo VALIDATE tbl].TrackNoValid, [TrackNo
VALIDATE tbl].LSTReleaseDate " & _
"FROM [TrackNo VALIDATE tbl] " & _
"WHERE (([TrackNo VALIDATE tbl].TrackNoValid) = " &
[Forms]![frmListReleaseTrack]![ListRelease Track tbl sbfrm]![TrackNoValid] &
");"

Of course it is. Count the open parens and the close parens, and
you'll see the counts don't match. You're opening two after WHERE, but
you only close one. You should have
& "));" instead of just & ");"

If you build the SQL with the QBE grid, it inserts all the parentheses
for you.

 

[Guest]

I took off the brackets and I get "syntax error (missing operator) in query
expression 'TrackNoVALIDATE tbl.TrackNoValid"

hi Anna,

Can anyone help me with this? I have no idea why this is generating an error
"extra ) in query expression" and also it's not getting the value of
TrackNoValid field... Thanks!
strLMReleased = "SELECT [TrackNo VALIDATE tbl].TrackNoValid, [TrackNo
VALIDATE tbl].LSTReleaseDate " & _
"FROM [TrackNo VALIDATE tbl] " & _
"WHERE (([TrackNo VALIDATE tbl].TrackNoValid) = " &
[Forms]![frmListReleaseTrack]![ListRelease Track tbl sbfrm]![TrackNoValid] &
");"

Why don't you remove the brackets? They just mark the normal operator
precedence and have no impact on the result of the query.


mfG
--> stefan <--

 

[Guest]

Tried that, same thing.. Syntax error (missing operator) in query expression
'TrackNo VALIDATE tbl.TrackNoValid."

Hi,
Can anyone help me with this? I have no idea why this is generating an error
"extra ) in query expression" and also it's not getting the value of
TrackNoValid field... Thanks!


strLMReleased = "SELECT [TrackNo VALIDATE tbl].TrackNoValid, [TrackNo
VALIDATE tbl].LSTReleaseDate " & _
"FROM [TrackNo VALIDATE tbl] " & _
"WHERE (([TrackNo VALIDATE tbl].TrackNoValid) = " &
[Forms]![frmListReleaseTrack]![ListRelease Track tbl sbfrm]![TrackNoValid] &
");"

Of course it is. Count the open parens and the close parens, and
you'll see the counts don't match. You're opening two after WHERE, but
you only close one. You should have
& "));" instead of just & ");"

If you build the SQL with the QBE grid, it inserts all the parentheses
for you.

 

[Guest]

ps that error comes when it tries to execute this statement:
Set rstTables = dbsList.OpenRecordset(strLMReleased)

 

[Alex Dybenko]

Hi,
[Forms]![frmListReleaseTrack]![ListRelease Track tbl
bfrm]![TrackNoValid] - could it be an extra ) there?

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

ps that error comes when it tries to execute this statement:
Set rstTables = dbsList.OpenRecordset(strLMReleased)

Hi,
Can anyone help me with this? I have no idea why this is generating an
error
"extra ) in query expression" and also it's not getting the value of
TrackNoValid field... Thanks!




strLMReleased = "SELECT [TrackNo VALIDATE tbl].TrackNoValid, [TrackNo
VALIDATE tbl].LSTReleaseDate " & _
"FROM [TrackNo VALIDATE tbl] " & _
"WHERE (([TrackNo VALIDATE tbl].TrackNoValid) = " &
[Forms]![frmListReleaseTrack]![ListRelease Track tbl
sbfrm]![TrackNoValid] &
");"

MsgBox strLMReleased

Debug.Print strLMReleased

Set rstTables = dbsList.OpenRecordset(strLMReleased)
If Not rstTables.EOF Then
With rstTables
.MoveLast
.MoveFirst

If IsNull(rstTables!LSTReleaseDate) Then
MsgBox "This job has not been released by List Maintenance.
Please
make sure the job is released before processing.", , "Cannot Process Job"
rstTable.Close
Exit Sub
End If

End With

End If
rstTables.Close

 

[Stefan Hoffmann]

hi Anna,

I took off the brackets and I get "syntax error (missing operator) in query
expression 'TrackNoVALIDATE tbl.TrackNoValid"

Then there are somewhere missing the square brackets, as your field name
contains spaces and needs therefore enclosed in them:

[TrackNoVALIDATE tbl].[TrackNoValid]

Try the following using a table alias:

Dim TrackNoValid As Long

TrackNoValid = [Forms]![frmListReleaseTrack]! _
[ListRelease Track tbl sbfrm]![TrackNoValid]
strLMReleased = "SELECT t.TrackNoValid, t.LSTReleaseDate " & _
"FROM [TrackNo VALIDATE tbl] t " & _
"WHERE t.TrackNoValid = " & TrackNoValid


Also ensure that TracNoValid is a valid numeric value. Otherwise you may
need some formatting, e.g. if it is a string, you need to enclose it in
'" & TrackNoValid & "'.


mfG
--> stefan <--