Search statement not working

[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

 

[OldPro]

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

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 & "*""...

 

[Tony Williams]

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

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

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 & "*""...

 

[OldPro]

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

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.

 

[Tony Williams]

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

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

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.

 

[OldPro]

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 & " * """

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 & " * """

 

[Tony Williams]

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

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 & " * """

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 & " * """

 

[OldPro]

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.

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

 

[Tony Williams]

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

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.

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

 

[OldPro]

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.

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- Hide quoted text -

- Show quoted text -

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?