Search statement not working

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a search form and this code is on the keypress of a textbox
Me.RecordSource = "SELECT * FROM [qrysearchcomplaint] WHERE
[txtcomplainant] like ""*" & Me.txtSearch.Value & "*"" or [txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr] Like "" * " & Me.txtSearch.Value &
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """

It works apart from txtrefnbr. All the other fields work ok but are text
fields txtrefnbr is a number field is that the problem and if so how do I
correct it.
Thanks
Tony
 
I have a search form and this code is on the keypress of a textbox
Me.RecordSource = "SELECT * FROM [qrysearchcomplaint] WHERE
[txtcomplainant] like ""*" & Me.txtSearch.Value & "*"" or [txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr] Like "" * " & Me.txtSearch.Value &
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """

It works apart from txtrefnbr. All the other fields work ok but are text
fields txtrefnbr is a number field is that the problem and if so how do I
correct it.
Thanks
Tony
Click to expand...

The LIKE operator is used to compare two strings. It doesn't work
with numbers. You could try to create a user defined field and use
the LIKE operator with it. It would be similar to :
"SELECT [txtcomplainant],[txtbusinessname],
[txtsurname],Format([txtrefnbr]) as txtrefnbr2, ... ...[txtrefnbr2]
LIKE ""*" & me.txtSearch.value & "*""...
 
Sorry don't quite understand that. Would my SELECT statement then be
"SELECT * FROM [qrysearchcomplaint] WHERE [txtcomplainant],
[txtbusinessname], [txtsurname], [txtmemnbr], Fomat([txtrefnbr]) as
[txtrefnbr2] like ""*" &
Me.txtSearch.Value & "*"" "
Would this compare any of the controls or would it expect all the fields to
be like txtsearch?
Sorry to be so dim
Tony
OldPro said:
I have a search form and this code is on the keypress of a textbox
Me.RecordSource = "SELECT * FROM [qrysearchcomplaint] WHERE
[txtcomplainant] like ""*" & Me.txtSearch.Value & "*"" or
[txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr] Like "" * " & Me.txtSearch.Value
&
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """

It works apart from txtrefnbr. All the other fields work ok but are text
fields txtrefnbr is a number field is that the problem and if so how do I
correct it.
Thanks
Tony
Click to expand...

The LIKE operator is used to compare two strings. It doesn't work
with numbers. You could try to create a user defined field and use
the LIKE operator with it. It would be similar to :
"SELECT [txtcomplainant],[txtbusinessname],
[txtsurname],Format([txtrefnbr]) as txtrefnbr2, ... ...[txtrefnbr2]
LIKE ""*" & me.txtSearch.value & "*""...
Click to expand...
 
Sorry don't quite understand that. Would my SELECT statement then be
"SELECT * FROM [qrysearchcomplaint] WHERE [txtcomplainant],
[txtbusinessname], [txtsurname], [txtmemnbr], Fomat([txtrefnbr]) as
[txtrefnbr2] like ""*" &
Me.txtSearch.Value & "*"" "
Would this compare any of the controls or would it expect all the fields to
be like txtsearch?
Sorry to be so dim
Click to expand...

Instead of using * to represent all fields, you would have to
explicitly write them out as per my example. Here is a more detailed
version:
"SELECT [txtcomplainant],[txtbusinessname],
[txtsurname],Format([txtrefnbr]) as txtrefnbr2,[txtmemnbr] FROM
[qrysearchcomplaint] WHERE [txtcomplainant] like ""*" &
Me.txtSearch.Value & "*"" or [txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr2] Like "" * " &
Me.txtSearch.Value &
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """

The format( ) function changes the numeric field txtrefnbr into the
user-defined text field txtrefnbr2. If txtmemnbr is also a number
then you will have to do the same thing to it.
BTW, it is good practice to name textboxes so that they start with
"txt", but not fields in a table. It causes confusion. Some people
prefix fields with an "fld" to distinguish them, but I haven't found
that necessary. I usually prefix memory variables with an s for
string, i for integer, and b for boolean. That way I can easily tell
if I am working with a field, variable, or screen control. For
example, to assign the value of a textbox to a memory variable, I
would use something like this:
sName=txtName
To assign the value of a variable to a field in a table (represented
by the recordset "rs"), I would use something like this:
rs!Name=sName
Note that the equal sign is read as "is assigned", as in this case it
is being used as an assignment operator.
 
Hi there thanks for the tips, I'm a 63 year old self taught and some of this
is over my head but I'm getting there! I changed my Select statement as your
example but when I click on the search box I get a popup box that asks me
for the value of txtrefnbr2?
What am I missing?
Thanks
Tony
OldPro said:
Sorry don't quite understand that. Would my SELECT statement then be
"SELECT * FROM [qrysearchcomplaint] WHERE [txtcomplainant],
[txtbusinessname], [txtsurname], [txtmemnbr], Fomat([txtrefnbr]) as
[txtrefnbr2] like ""*" &
Me.txtSearch.Value & "*"" "
Would this compare any of the controls or would it expect all the fields
to
be like txtsearch?
Sorry to be so dim
Click to expand...

Instead of using * to represent all fields, you would have to
explicitly write them out as per my example. Here is a more detailed
version:
"SELECT [txtcomplainant],[txtbusinessname],
[txtsurname],Format([txtrefnbr]) as txtrefnbr2,[txtmemnbr] FROM
[qrysearchcomplaint] WHERE [txtcomplainant] like ""*" &
Me.txtSearch.Value & "*"" or [txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr2] Like "" * " &
Me.txtSearch.Value &
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """

The format( ) function changes the numeric field txtrefnbr into the
user-defined text field txtrefnbr2. If txtmemnbr is also a number
then you will have to do the same thing to it.
BTW, it is good practice to name textboxes so that they start with
"txt", but not fields in a table. It causes confusion. Some people
prefix fields with an "fld" to distinguish them, but I haven't found
that necessary. I usually prefix memory variables with an s for
string, i for integer, and b for boolean. That way I can easily tell
if I am working with a field, variable, or screen control. For
example, to assign the value of a textbox to a memory variable, I
would use something like this:
sName=txtName
To assign the value of a variable to a field in a table (represented
by the recordset "rs"), I would use something like this:
rs!Name=sName
Note that the equal sign is read as "is assigned", as in this case it
is being used as an assignment operator.
Click to expand...
 
Hi there thanks for the tips, I'm a 63 year old self taught and some of this
is over my head but I'm getting there! I changed my Select statement as your
example but when I click on the search box I get a popup box that asks me
for the value of txtrefnbr2?
What am I missing?
Thanks

Instead of using * to represent all fields, you would have to
explicitly write them out as per my example. Here is a more detailed
version:
"SELECT [txtcomplainant],[txtbusinessname],
[txtsurname],Format([txtrefnbr]) as txtrefnbr2,[txtmemnbr] FROM
[qrysearchcomplaint] WHERE [txtcomplainant] like ""*" &
Me.txtSearch.Value & "*"" or [txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr2] Like "" * " &
Me.txtSearch.Value &
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """
Click to expand...
Click to expand...

I couldn't get it to work either. Apparently SQL doesn't allow
sorting or filtering on a calculated field.

To get around that restriction, you would have to add a new text field
to the table and populate it with an Update query based on the numeric
version.
"UPDATE qrysearchcomplaint SET [txtrefnbr2]=format([txtrefnbr])"

then call

"SELECT [txtcomplainant],[txtbusinessname],
[txtsurname],[txtrefnbr2],[txtmemnbr] FROM
[qrysearchcomplaint] WHERE [txtcomplainant] like ""*" &
Me.txtSearch.Value & "*"" or [txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr2] Like "" * " &
Me.txtSearch.Value &
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """
 
Sorry to be so dim again Oldpro but I've added that line to my code and the
only way I could get it to get through the debug was to use this
Me.RecordSource = "UPDATE qrysearchcomplaint SET
[txtrefnbr2]=format([txtrefnbr])"

However when I click on the search button I get an error message that says
query can't be used as a rowsource. I think I need to go back to square 1
and rethink my search code. I need to be able to search both text and number
controls and maybe I need to think about splitting it up into two different
sets of code.
Thanks for sticking with me though.
Cheers
Tony
OldPro said:
Hi there thanks for the tips, I'm a 63 year old self taught and some of
this
is over my head but I'm getting there! I changed my Select statement as
your
example but when I click on the search box I get a popup box that asks
me
for the value of txtrefnbr2?
What am I missing?
Thanks

Instead of using * to represent all fields, you would have to
explicitly write them out as per my example. Here is a more detailed
version:
"SELECT [txtcomplainant],[txtbusinessname],
[txtsurname],Format([txtrefnbr]) as txtrefnbr2,[txtmemnbr] FROM
[qrysearchcomplaint] WHERE [txtcomplainant] like ""*" &
Me.txtSearch.Value & "*"" or [txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr2] Like "" * " &
Me.txtSearch.Value &
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """
Click to expand...
Click to expand...

I couldn't get it to work either. Apparently SQL doesn't allow
sorting or filtering on a calculated field.

To get around that restriction, you would have to add a new text field
to the table and populate it with an Update query based on the numeric
version.
"UPDATE qrysearchcomplaint SET [txtrefnbr2]=format([txtrefnbr])"

then call

"SELECT [txtcomplainant],[txtbusinessname],
[txtsurname],[txtrefnbr2],[txtmemnbr] FROM
[qrysearchcomplaint] WHERE [txtcomplainant] like ""*" &
Me.txtSearch.Value & "*"" or [txtbusinessname]
like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" &
Me.txtSearch.Value & "*"" Or [txtrefnbr2] Like "" * " &
Me.txtSearch.Value &
" * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * """
Click to expand...
 
Sorry to be so dim again Oldpro but I've added that line to my code and the
only way I could get it to get through the debug was to use this
Me.RecordSource = "UPDATE qrysearchcomplaint SET
[txtrefnbr2]=format([txtrefnbr])"

However when I click on the search button I get an error message that says
query can't be used as a rowsource.
Click to expand...

This is the way you would do it:

dim db as dao.database
set db=currentdb()
db.execute "UPDATE qrysearchcomplaint SET
[txtrefnbr2]=format([txtrefnbr])"
db.close
set db=nothing
 
Still no joy I now get a message that says "Operation must use an Updateable
query" Does this mean I have to create another query?
Thanks for sticking with me
Tony
OldPro said:
Sorry to be so dim again Oldpro but I've added that line to my code and
the
only way I could get it to get through the debug was to use this
Me.RecordSource = "UPDATE qrysearchcomplaint SET
[txtrefnbr2]=format([txtrefnbr])"

However when I click on the search button I get an error message that
says
query can't be used as a rowsource.
Click to expand...

This is the way you would do it:

dim db as dao.database
set db=currentdb()
db.execute "UPDATE qrysearchcomplaint SET
[txtrefnbr2]=format([txtrefnbr])"
db.close
set db=nothing
Click to expand...
 
Still no joy I now get a message that says "Operation must use an Updateable
query" Does this mean I have to create another query?
Thanks for sticking with me



Sorry to be so dim again Oldpro but I've added that line to my code and
the
only way I could get it to get through the debug was to use this
Me.RecordSource = "UPDATE qrysearchcomplaint SET
[txtrefnbr2]=format([txtrefnbr])"
However when I click on the search button I get an error message that
says
query can't be used as a rowsource.
Click to expand...
Click to expand...
This is the way you would do it:
Click to expand...
dim db as dao.database
set db=currentdb()
db.execute "UPDATE qrysearchcomplaint SET
[txtrefnbr2]=format([txtrefnbr])"
db.close
set db=nothing- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Is qrysearchcomplaint a query or a table? I was treating it like a
table as it would make no sense as a query. You don't update queries,
per se, you update tables. You used qrysearchcomplaint in a SELECT
statement which creates a recordset from a table, not a query. A
query is usually a read-only recordset built from one or more filtered
tables. Use the UPDATE statement with the actual table, not a query.
Are we speaking the same language yet?
 
Back
Top