extra ) in query expression/sql statement issues

A

Anna

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
 
S

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] &
");"
Click to expand...
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 <--
 
P

pietlinden

Anna said:
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] &
");"
Click to expand...

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.
 
A

Anna

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

Stefan Hoffmann said:
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] &
");"
Click to expand...
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 <--
Click to expand...
 
A

Anna

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

[email protected] said:
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] &
");"
Click to expand...

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.
Click to expand...
 
A

Anna

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

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

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

Anna said:
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
Click to expand...
Click to expand...
 
S

Stefan Hoffmann

hi Anna,
I took off the brackets and I get "syntax error (missing operator) in query
expression 'TrackNoVALIDATE tbl.TrackNoValid"
Click to expand...
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 <--
 
Top