Extra ) in Query Expression

[David]

Anyone Please Help!

Typed Code To open report from search. When I click on
the Preview Button I get a runtime error #3075
"Extra ) in Query Expression"

I can't find it.
Code follows

Private Sub cmdPreview_Click()
'On Error GoTo Err_cmdPreview_Click

'Dim stDocName As String
Dim strWhere As String
Dim lngLen As Long

'stDocName = "Recording Search Report2"
'DoCmd.OpenReport stDocName, acPreview
If Not IsNull(cboRecordingArtistName) Then
strWhere = strWhere & "([RecordingArtistName])
= """ & _
cboRecordingArtistName & """) AND "
End If
If Not IsNull(cboRecordingTitle) Then
strWhere = strWhere & "([RecordingTitle]) = """ & _
cboRecordingTitle & """) AND "
End If

If Not IsNull(cboTrackTitle) Then
strWhere = strWhere & "([TrackTitle]) = """ & _
cboTrackTitle & """) AND "
End If

If Not IsNull(cboTrackNumber) Then
strWhere = strWhere & "([TrackNumber]) = """ & _
cboTrackNumber & """) AND "
End If

If Not IsNull(cboTrackLength) Then
strWhere = strWhere & "([TrackLength]) = """ & _
cboTrackLength & """) AND "
End If

If Not IsNull(cboTrackTempo) Then
strWhere = strWhere & "([TrackTempo]) = """ & _
cboTrackTempo & """) AND "
End If

If Not IsNull(cboMusicCategory) Then
strWhere = strWhere & "([MusicCategory]) = """ & _
cboMusicCategory & """) AND "
End If

If Not IsNull(cboTrackSpecialty) Then
strWhere = strWhere & "([TrackSpecialty]) = """ & _
cboTrackSpecialty & """) "
End If

lngLen = Len(strWhere) - 8
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

'open Report
DoCmd.OpenReport "Recording Search Report",
acViewPreview, , strWhere
End Sub

 

[Van T. Dinh]

Get rid of the closing parentheses where I marked "***" in your code.

You got major problems with logic in constructing the WHERE String also. If
cboTrackSpecial is not Null, you have that condition and then later in the
code you delete the last 8 characters of the WHERE String which will most
likely bugger up your WHERE String. You need to re-think of your logic in
constructing your SQL String. Don't know where you get the number 8 from,
either.

 

[Douglas J. Steele]

Already answered in another newsgroup to which you posted the same question.

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

If you're using Microsoft's web interface to post, please note that you can
type the names of the various groups into the Newsgroup box. Separate each
newsgroup name with a semicolon. Note that it's generally consider to be A
Bad Thing to cross-post to more than about 2 or 3 newsgroups. (In fact, at
http://www.microsoft.com/presspass/features/2001/Mar01/Mar27pmvp.asp
Microsoft suggests that "One group will suffice")

 

[David]

You Saw My Code! Whats Wrong With It.
I am Trying to Open The Report "Recording Serach Report."
Removing the ending parenthasice, I was able to open the
report by clicking on
the Preview Button On My Form.
If I fill in the Controls of the form with search criteria
and click the
Preview Button, the report appears but no data on the
report.
Thank you for you help. I am at my wits end. If you would
like me to send you
the file please let me know.

David Wright

-----Original Message-----
Get rid of the closing parentheses where I marked "***" in your code.

You got major problems with logic in constructing the WHERE String also. If
cboTrackSpecial is not Null, you have that condition and then later in the
code you delete the last 8 characters of the WHERE String which will most
likely bugger up your WHERE String. You need to re-think of your logic in
constructing your SQL String. Don't know where you get the number 8 from,
either.

--
HTH
Van T. Dinh
MVP (Access)

Anyone Please Help!

Typed Code To open report from search. When I click on
the Preview Button I get a runtime error #3075
"Extra ) in Query Expression"

I can't find it.
Code follows

Private Sub cmdPreview_Click()
'On Error GoTo Err_cmdPreview_Click

'Dim stDocName As String
Dim strWhere As String
Dim lngLen As Long

'stDocName = "Recording Search Report2"
'DoCmd.OpenReport stDocName, acPreview
If Not IsNull(cboRecordingArtistName) Then
strWhere = strWhere & "([RecordingArtistName]) ***
= """ & _
cboRecordingArtistName & """) AND "
End If
If Not IsNull(cboRecordingTitle) Then
strWhere = strWhere & "([RecordingTitle])*** = """ & _
cboRecordingTitle & """) AND "
End If

If Not IsNull(cboTrackTitle) Then
strWhere = strWhere & "([TrackTitle])*** = """ & _
cboTrackTitle & """) AND "
End If

If Not IsNull(cboTrackNumber) Then
strWhere = strWhere & "([TrackNumber])*** = """ & _
cboTrackNumber & """) AND "
End If

If Not IsNull(cboTrackLength) Then
strWhere = strWhere & "([TrackLength])*** = """ & _
cboTrackLength & """) AND "
End If

If Not IsNull(cboTrackTempo) Then
strWhere = strWhere & "([TrackTempo])*** = """ & _
cboTrackTempo & """) AND "
End If

If Not IsNull(cboMusicCategory) Then
strWhere = strWhere & "([MusicCategory])*** = """ & _
cboMusicCategory & """) AND "
End If

If Not IsNull(cboTrackSpecialty) Then
strWhere = strWhere & "([TrackSpecialty])*** = """ & _
cboTrackSpecialty & """) "
End If

lngLen = Len(strWhere) - 8
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

'open Report
DoCmd.OpenReport "Recording Search Report",
acViewPreview, , strWhere
End Sub

.

 

[Van T. Dinh]

Doug and I already told you what's wrong with it. John Vinson already
showed you how to see the result of the concatenation as one complete String
so that you can check the selection criteria just prior to it being passed
to the JET engine database.