Search a word

  • Thread starter Thread starter Ac
  • Start date Start date
A

Ac

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
 
If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
 
Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



Klatuu said:
If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


Ac said:
I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
 
There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


Ac said:
Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



Klatuu said:
If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


Ac said:
I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

Klatuu said:
There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


Ac said:
Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



Klatuu said:
If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
I can't recreate your problem. Please post the code as you have it now.
--
Dave Hargis, Microsoft Access MVP


Ac said:
Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

Klatuu said:
There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


Ac said:
Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



:

If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

strWhere = "[Title]=" & strWhere & ""
End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
_____________________________________________________________

The error message is: Syntax error (missing operator) in query expression
‘[Title]=Like * Proposal *’ .

Does this problem come from misuse the “ or ‘ sign? Thanks!


Klatuu said:
I can't recreate your problem. Please post the code as you have it now.
--
Dave Hargis, Microsoft Access MVP


Ac said:
Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

Klatuu said:
There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



:

If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
This is the problem:

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

What I originally posted was:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

That is why those spaces are getting in there and you are getting the error.
--
Dave Hargis, Microsoft Access MVP


Ac said:
Thanks, Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

strWhere = "[Title]=" & strWhere & ""
End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
_____________________________________________________________

The error message is: Syntax error (missing operator) in query expression
‘[Title]=Like * Proposal *’ .

Does this problem come from misuse the “ or ‘ sign? Thanks!


Klatuu said:
I can't recreate your problem. Please post the code as you have it now.
--
Dave Hargis, Microsoft Access MVP


Ac said:
Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

:

There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



:

If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
I modified the code as you pointed out, but the error message now is: Syntax
error (missing operator) in query expression
‘[Title]=LIKE “* Proposal*â€â€â€™ . I do not know what is wrong?

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title]=" & strWhere & ""

End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function



Klatuu said:
This is the problem:

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

What I originally posted was:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

That is why those spaces are getting in there and you are getting the error.
--
Dave Hargis, Microsoft Access MVP


Ac said:
Thanks, Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

strWhere = "[Title]=" & strWhere & ""
End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
_____________________________________________________________

The error message is: Syntax error (missing operator) in query expression
‘[Title]=Like * Proposal *’ .

Does this problem come from misuse the “ or ‘ sign? Thanks!


Klatuu said:
I can't recreate your problem. Please post the code as you have it now.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

:

There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



:

If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
strWhere = "[Title]=" & strWhere & ""
Change it to
strWhere = "[Title]=" & strWhere


--
Dave Hargis, Microsoft Access MVP


Ac said:
I modified the code as you pointed out, but the error message now is: Syntax
error (missing operator) in query expression
‘[Title]=LIKE “* Proposal*â€â€â€™ . I do not know what is wrong?

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title]=" & strWhere & ""

End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function



Klatuu said:
This is the problem:

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

What I originally posted was:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

That is why those spaces are getting in there and you are getting the error.
--
Dave Hargis, Microsoft Access MVP


Ac said:
Thanks, Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

strWhere = "[Title]=" & strWhere & ""
End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
_____________________________________________________________

The error message is: Syntax error (missing operator) in query expression
‘[Title]=Like * Proposal *’ .

Does this problem come from misuse the “ or ‘ sign? Thanks!


:

I can't recreate your problem. Please post the code as you have it now.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

:

There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



:

If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
I still can not make it works, the new error message is:
Syntax error (missing operator) in query expression
‘[Title]=LIKE “* Proposal*'†. I do not know what is wrong agian?

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title]=" & strWhere

End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function



Klatuu said:
strWhere = "[Title]=" & strWhere & ""
Change it to
strWhere = "[Title]=" & strWhere


--
Dave Hargis, Microsoft Access MVP


Ac said:
I modified the code as you pointed out, but the error message now is: Syntax
error (missing operator) in query expression
‘[Title]=LIKE “* Proposal*â€â€â€™ . I do not know what is wrong?

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title]=" & strWhere & ""

End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function



Klatuu said:
This is the problem:

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

What I originally posted was:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

That is why those spaces are getting in there and you are getting the error.
--
Dave Hargis, Microsoft Access MVP


:

Thanks, Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

strWhere = "[Title]=" & strWhere & ""
End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
_____________________________________________________________

The error message is: Syntax error (missing operator) in query expression
‘[Title]=Like * Proposal *’ .

Does this problem come from misuse the “ or ‘ sign? Thanks!


:

I can't recreate your problem. Please post the code as you have it now.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

:

There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



:

If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
We covered this in a previous post. Sorry I didn't notice it this time.
Now, please put this in your code exactly like this:

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title] " & strWhere

End If

--
Dave Hargis, Microsoft Access MVP


Ac said:
I still can not make it works, the new error message is:
Syntax error (missing operator) in query expression
‘[Title]=LIKE “* Proposal*'†. I do not know what is wrong agian?

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title]=" & strWhere

End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function



Klatuu said:
strWhere = "[Title]=" & strWhere & ""
Change it to
strWhere = "[Title]=" & strWhere


--
Dave Hargis, Microsoft Access MVP


Ac said:
I modified the code as you pointed out, but the error message now is: Syntax
error (missing operator) in query expression
‘[Title]=LIKE “* Proposal*â€â€â€™ . I do not know what is wrong?

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title]=" & strWhere & ""

End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function



:

This is the problem:

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

What I originally posted was:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

That is why those spaces are getting in there and you are getting the error.
--
Dave Hargis, Microsoft Access MVP


:

Thanks, Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

strWhere = "[Title]=" & strWhere & ""
End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
_____________________________________________________________

The error message is: Syntax error (missing operator) in query expression
‘[Title]=Like * Proposal *’ .

Does this problem come from misuse the “ or ‘ sign? Thanks!


:

I can't recreate your problem. Please post the code as you have it now.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

:

There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



:

If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dave, it works now. The problem from last time is the "=" sign, I had
one more "= " after the [Title]. I appreciate all your great help!

Klatuu said:
We covered this in a previous post. Sorry I didn't notice it this time.
Now, please put this in your code exactly like this:

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title] " & strWhere

End If

--
Dave Hargis, Microsoft Access MVP


Ac said:
I still can not make it works, the new error message is:
Syntax error (missing operator) in query expression
‘[Title]=LIKE “* Proposal*'†. I do not know what is wrong agian?

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title]=" & strWhere

End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function



Klatuu said:
strWhere = "[Title]=" & strWhere & ""
Change it to
strWhere = "[Title]=" & strWhere


--
Dave Hargis, Microsoft Access MVP


:

I modified the code as you pointed out, but the error message now is: Syntax
error (missing operator) in query expression
‘[Title]=LIKE “* Proposal*â€â€â€™ . I do not know what is wrong?

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

strWhere = "[Title]=" & strWhere & ""

End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function



:

This is the problem:

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

What I originally posted was:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

That is why those spaces are getting in there and you are getting the error.
--
Dave Hargis, Microsoft Access MVP


:

Thanks, Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = Trim("LIKE " & " * " & Me.txtTitle & " * " & "")

strWhere = "[Title]=" & strWhere & ""
End If

DoCmd.OpenForm "fMain", acNormal, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
_____________________________________________________________

The error message is: Syntax error (missing operator) in query expression
‘[Title]=Like * Proposal *’ .

Does this problem come from misuse the “ or ‘ sign? Thanks!


:

I can't recreate your problem. Please post the code as you have it now.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Dave,

I used the Trim() function to trim the space before and after the space for
txtFile, but it still shows the same error message. It looks like the “ “
problem; could you explain how to use the “ “ sign? Thanks!

:

There seems to be leading and trailing spaces around the word Proposal. It
should come out as
LIKE "*Proposal*"

Now, I copied the code you posted, but I did have to change Me.txtTitle
because I don't have that in my test database, but using the code it came out
as above when I looked at the value of strWhere. So, how are the spaces
getting in there?
--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

Thank you very much for your reply!

I just try the first part of the code and got the error message; here is my
new code,

Private Sub cmdSearch_Click()

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.txtTitle) Then

strWhere = "LIKE ""*" & Me.txtTitle & "*"""

‘ strWhere = "[Title]=""" & strWhere & """"


End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function


The error message is: Syntax error (missing operator) in query expression
‘Like†* proposal * â€â€

Proposal is the word from the txtTitle. Thanks again!



:

If you need to seach on text within text it needs to be:

strWhere = "LIKE ""*" & Me.txtTitle & "*"""
--
Dave Hargis, Microsoft Access MVP


:

I try to write a program for a user to search a word that is a partial word
inside of the record. I created a table containing the fields Files and
Descriptions, and a form named fSearch; there are two text boxes txtFiles and
txtDescriptions to take the inputs from user and a cmdSearch button; if the
user typed the word in the txtFiles or txtDescriptions, is a part of the word
inside of the Files or the Descriptions in the table, then the corresponding
record will be retrieved and displayed on the form named fMain.

I try to use the function InStr behind the cmdSearch button, but the result
did not come right. If it does not asked too much, could you help me for the
code? Or
should I use different function? Please give me an advice, thanks.

Here is the code:

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim Result As Integer

strWhere = ""

If Not IsNull(Me.txtTitle) Then

Result = InStr(1, Files.[Title], Me.txtTitle)
If Result > 0 Then



strWhere = "[Title]=""" & Files.[Title] & """"

End If

End If

If Not IsNull(Me.txtDescription) Then

If (InStr(1, Files.[Description], Me.txtDescription)) > 0 then

strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Description] =""" & Files.[Description] & """"
End If


Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & "AND"
Else
AddAnd = strFilterString
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top