Extra ) error in search form

T

Tal

Hello all,

I have a search form that I have been coding and testing with each filed
addition.
Everything worked perfectly until the last field (cboCampaign) was added.
Now I get a error 3075 extra ) in string. Any assistance is greatly
appreciated.
Cheers,
Tal

Here's the code:


Private Sub btnApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterDonorName) Then
strWhere = strWhere & "([compFileAs] Like ""*" & Me.txtFilterDonorName &
"*"") AND"
End If
If Not IsNull(Me.txtFilterDonorAddress) Then
strWhere = strWhere & "([compAddress] Like ""*" &
Me.txtFilterDonorAddress & "*"") AND"
End If
If Not IsNull(Me.txtFilterDescription) Then
strWhere = strWhere & "([txtDescription] Like ""*" &
Me.txtFilterDescription & "*"") AND"
End If
If Not IsNull(Me.txtFilterReceiptTo) Then
strWhere = strWhere & "([txtReceiptTo] Like ""*" & Me.txtFilterReceiptTo
& "*"") AND"
End If
If Me.cboDonationPaid = "Paid" Then
strWhere = strWhere & "([ynDonationPaid] = True) AND"
ElseIf Me.cboDonationPaid = "Unpaid" Then
strWhere = strWhere & "([ynDonationPaid] = False) AND"
End If
If Not IsNull(Me.cboCampaign) Then
strWhere = strWhere & "([keyCampaign] = " & Me.cboCampaign & ") AND"
End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing Entered"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
 
J

John W. Vinson

I have a search form that I have been coding and testing with each filed
addition.
Everything worked perfectly until the last field (cboCampaign) was added.
Now I get a error 3075 extra ) in string. Any assistance is greatly
appreciated.
Click to expand...

It's not blatantly obvious; I'd suggest setting a breakpoint in the code by
mouseclicking in the grey bar to the left of the line

lngLen = Len(strWhere) - 4

and see what strWhere actually contains. Might your code to trim off the AND
be trimming off one character too many?
 
T

Tal

Hi all,

Thank you so much for your responses. I am going to try this last one, if I
can possibly figure it out. I am a relative newbie after all.
To answer one question, the cboCampaign is 2 columns with 1 bound column
being the autonumber key field of the lookup. So, I am assuming that is a
number.
Let me know if I am mistaken.

Again, thanks for everyone's effort.

Tal

DStegon via AccessMonster.com said:
Put a stop in and grab the strwhere at the end of all the ifs

Here is a little thing we use. Paste the code below into a module. Call up
the function in the immediate window and paste the strWhere and run the code..
. it will print of the code into the immediate window and launch the query
designer and then after making the SQL what you want it will reprint the SQL
into the immediate window using the SplitIt Function below (also paste that
into the same module) with the " & _ ending wraps so your code wont be one
huge long string and you can set how long you want each single line.

Maybe this will help find it.. usually these are something SO EASY!!

Public Sub QA(sql As String)
Dim qry As New QueryDef
Dim QryName As String
QryName = "QueryAmbassador"
With qry
.NAME = QryName
.sql = sql
End With
On Error Resume Next
DoCmd.DeleteObject acQuery, QryName
On Error GoTo 0
Debug.Print sql
Application.CurrentDb.QueryDefs.Append qry
Application.CurrentDb.QueryDefs.Refresh
DoCmd.OpenQuery QryName, acViewDesign, acEdit

Stop
DoCmd.Close acQuery, QryName, acSaveYes
For Each qry In Application.CurrentDb.QueryDefs
If qry.NAME = QryName Then SplitIt qry.sql, 150
Next qry

End Sub

Public Function SplitIt(ByVal strx As String, Optional LineLength As Long =
100) As String
Dim SplitString As String
Dim splitvalue As Long
Do Until Len(strx) = 0
splitvalue = InStr(LineLength, strx, " ")
If splitvalue = 0 Then splitvalue = Len(strx)
SplitString = SplitString & """" & Replace(Left(strx, splitvalue), vbCrLf,
" ") & """" & IIf(Len(strx) = splitvalue, "", " & _" & vbCrLf)
strx = mID(strx, splitvalue + 1)
Loop
Debug.Print SplitString
SplitIt = SplitString
End Function

Hello all,

I have a search form that I have been coding and testing with each filed
addition.
Everything worked perfectly until the last field (cboCampaign) was added.
Now I get a error 3075 extra ) in string. Any assistance is greatly
appreciated.
Cheers,
Tal

Here's the code:

Private Sub btnApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterDonorName) Then
strWhere = strWhere & "([compFileAs] Like ""*" & Me.txtFilterDonorName &
"*"") AND"
End If
If Not IsNull(Me.txtFilterDonorAddress) Then
strWhere = strWhere & "([compAddress] Like ""*" &
Me.txtFilterDonorAddress & "*"") AND"
End If
If Not IsNull(Me.txtFilterDescription) Then
strWhere = strWhere & "([txtDescription] Like ""*" &
Me.txtFilterDescription & "*"") AND"
End If
If Not IsNull(Me.txtFilterReceiptTo) Then
strWhere = strWhere & "([txtReceiptTo] Like ""*" & Me.txtFilterReceiptTo
& "*"") AND"
End If
If Me.cboDonationPaid = "Paid" Then
strWhere = strWhere & "([ynDonationPaid] = True) AND"
ElseIf Me.cboDonationPaid = "Unpaid" Then
strWhere = strWhere & "([ynDonationPaid] = False) AND"
End If
If Not IsNull(Me.cboCampaign) Then
strWhere = strWhere & "([keyCampaign] = " & Me.cboCampaign & ") AND"
End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing Entered"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
Click to expand...
 
T

Tal

I am nearly ashamed to admit this after all of your effort, but it seems that
adding the extra space between the AND and " and changing the 4 to a 5 did
the trick.

Again, thank you thank you all.

DStegon via AccessMonster.com said:
Put a stop in and grab the strwhere at the end of all the ifs

Here is a little thing we use. Paste the code below into a module. Call up
the function in the immediate window and paste the strWhere and run the code..
. it will print of the code into the immediate window and launch the query
designer and then after making the SQL what you want it will reprint the SQL
into the immediate window using the SplitIt Function below (also paste that
into the same module) with the " & _ ending wraps so your code wont be one
huge long string and you can set how long you want each single line.

Maybe this will help find it.. usually these are something SO EASY!!

Public Sub QA(sql As String)
Dim qry As New QueryDef
Dim QryName As String
QryName = "QueryAmbassador"
With qry
.NAME = QryName
.sql = sql
End With
On Error Resume Next
DoCmd.DeleteObject acQuery, QryName
On Error GoTo 0
Debug.Print sql
Application.CurrentDb.QueryDefs.Append qry
Application.CurrentDb.QueryDefs.Refresh
DoCmd.OpenQuery QryName, acViewDesign, acEdit

Stop
DoCmd.Close acQuery, QryName, acSaveYes
For Each qry In Application.CurrentDb.QueryDefs
If qry.NAME = QryName Then SplitIt qry.sql, 150
Next qry

End Sub

Public Function SplitIt(ByVal strx As String, Optional LineLength As Long =
100) As String
Dim SplitString As String
Dim splitvalue As Long
Do Until Len(strx) = 0
splitvalue = InStr(LineLength, strx, " ")
If splitvalue = 0 Then splitvalue = Len(strx)
SplitString = SplitString & """" & Replace(Left(strx, splitvalue), vbCrLf,
" ") & """" & IIf(Len(strx) = splitvalue, "", " & _" & vbCrLf)
strx = mID(strx, splitvalue + 1)
Loop
Debug.Print SplitString
SplitIt = SplitString
End Function

Hello all,

I have a search form that I have been coding and testing with each filed
addition.
Everything worked perfectly until the last field (cboCampaign) was added.
Now I get a error 3075 extra ) in string. Any assistance is greatly
appreciated.
Cheers,
Tal

Here's the code:

Private Sub btnApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterDonorName) Then
strWhere = strWhere & "([compFileAs] Like ""*" & Me.txtFilterDonorName &
"*"") AND"
End If
If Not IsNull(Me.txtFilterDonorAddress) Then
strWhere = strWhere & "([compAddress] Like ""*" &
Me.txtFilterDonorAddress & "*"") AND"
End If
If Not IsNull(Me.txtFilterDescription) Then
strWhere = strWhere & "([txtDescription] Like ""*" &
Me.txtFilterDescription & "*"") AND"
End If
If Not IsNull(Me.txtFilterReceiptTo) Then
strWhere = strWhere & "([txtReceiptTo] Like ""*" & Me.txtFilterReceiptTo
& "*"") AND"
End If
If Me.cboDonationPaid = "Paid" Then
strWhere = strWhere & "([ynDonationPaid] = True) AND"
ElseIf Me.cboDonationPaid = "Unpaid" Then
strWhere = strWhere & "([ynDonationPaid] = False) AND"
End If
If Not IsNull(Me.cboCampaign) Then
strWhere = strWhere & "([keyCampaign] = " & Me.cboCampaign & ") AND"
End If

lngLen = Len(strWhere) - 4
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing Entered"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
Click to expand...
 
T

Tal

Absolutely.
You have greatly improved my ability to debug stuff. Huge help!!
Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search Form with a SubForm 3
Modified Allenbrowne Search Form displays error 1
Multiple Search Criteria with AND/OR??? 4
Allen Browne's Search Form - Debugging help needed 2
Error on modified Allenbrowne SearchForm 6
Help with VBA with an unbound form 4
Require a field in search form 1
Filter for words at the beginning or end using a Form Command Butt 5

Top