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

DStegon via AccessMonster.com

Very obvious... look at your AND at the end of each. If there is more than
ONE condition that is met then there is NO SPACE between the trailing AND of
the last clause and the "(" of the next...

Say both of these are true
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
Click to expand...

You get something like this

"([compFileAs] Like ""*Smith*"") AND([compAddress] Like ""*Main Street*"")
AND"

Right? No spcae between AND([ ... should be AND ([

So... you want to put a space " " or ) AND " and then delete 5 of the length
at the end of the code. to take the final trailing " AND " off the WHERE
clause that you are building.




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

DStegon via AccessMonster.com

DStegon said:
Very obvious... look at your AND at the end of each. If there is more than
Click to expand...

Ahhh.. never mind... after looking at the code I just typed and thinking...
the compiler will put space in after the AND and separate them into the
separate conditions. Ok... I am with John... Not so obvious..... In fact...
now I am going to test no spaces between ")AND(" in code. Never write
without spaces but after thinking about it the code will run. Ok... now this
one is going to take up my evening!!
 
D

DStegon via AccessMonster.com

Are you sure that cboCampaign is a number or is it text string. What is the
bound column??? IF the value of cboCampaign is "Jones" then you need ' or
"" around the control value like you have in the donor if statement, but that
would give you data type mismatch and not extra ). Sorry for the extra posts.
. getting mad on this one.. :eek:)
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...
 
D

DStegon via AccessMonster.com

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

DStegon via AccessMonster.com

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

My initial thought was that after looking at your code. But, then I probably
"over thought" the issue. Never did check the "no space" thing last night
because of dinner and kids. Glad it worked out... and hey... youhave a
little code snippet to now help with other SQL statements that you want to
post in code.
 

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 2
Create Report On the Fly 1
Filtering a multiple item form 1
Allen Browne's Search Form - Debugging help needed 2
Allen Brownes ECount() 5
Error on modified Allenbrowne SearchForm 6
Require a field in search form 3

Top