extra ) in query expression/sql statement issues

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anna - 17 Nov 2006 13:56 GMT

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

Reply to this Message

Stefan Hoffmann - 17 Nov 2006 14:13 GMT

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
[quoted text clipped - 5 lines]
> [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 <--

Reply to this Message

Anna - 17 Nov 2006 18:29 GMT

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

> hi Anna,
>
[quoted text clipped - 12 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 19 Nov 2006 11:34 GMT

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

Reply to this Message

pietlinden@hotmail.com - 17 Nov 2006 18:23 GMT

> Hi,
> Can anyone help me with this? I have no idea why this is generating an error
[quoted text clipped - 7 lines]
> [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.

Reply to this Message

Anna - 17 Nov 2006 18:55 GMT

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
[quoted text clipped - 15 lines]
> If you build the SQL with the QBE grid, it inserts all the parentheses
> for you.

Reply to this Message

Anna - 17 Nov 2006 19:01 GMT

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
[quoted text clipped - 29 lines]
> End If
> rstTables.Close

Reply to this Message

Alex Dybenko - 18 Nov 2006 19:57 GMT

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

Signature

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)
[quoted text clipped - 35 lines]
>> End If
>> rstTables.Close

Reply to this Message