String Search

[Kamil]

Hello All,
I have a very big .mdb file which contains 15 fields. One of these fields
(say mysentence field) is containing a sentence like; "this is only an
example". Mysentence field contains "example" word for instance and i know
that there are many "example" word in whole database.
I have questions:
1. How can I find all the mysentence fields which contain "example" word
2. How can I find all "example" words in whole database (I know that there
are also other text fields containing "example")

Thanks...
Kamil

 

[Yumex85]

Hi Kamil,
I think u made a little confusion there. Let me see if I understood
you problem, you have a big TABLE with 15 Columns(fields)?
Is "Mysentence" a column in a table you have? If yes, then you could
run an Update query in this table, updating only the mysentence
column.
If you have many different tables, and many different columns with
the word "example" you wanna modify, then you should make some code in
VBA. Instead of manually updating each table searchin column by
column, you could come up with a loop that gets your table names, and
a sub-loop getting the tables columns names, and updating any field
that contain the "example" word.
regard,
yumi

 

[Kamil]

Hi Yumi,
Thank you for reply. Yes, you are right.
I know that I have to write some codes to find the "word" example in whole
table, but since I am new (or you can say not good instead of "new") in
vba, I am trying to find some examples I can use.
In fact the question I asked is only some part of my problem. I need a code
which queries the table.

Thanks and Regards
Kamil

 

[Yumex85]

hmmm a query?
to search one column it would be
SELECT * FROM TABLE WHERE COLUMN_YOU_WANNNA_SEARCH LIKE "*example*"
that would show all the lines in ur table where the column u searched
has the word example. If u want to update then...
update table set table.column where table.column like "*example*"

would u need to search in multiple columns?

 

[Kamil]

That's great!
Thank you.
Please suppose that I want to input the word in screen;
The code will ask me something like below for instance:
"Please enter the word you want to search: "
Is it possible,
Regards...

 

[Yumex85]

yes, you can.

Make a form with a label "enter the text below"
then insert also a text box. Lets call the textbox "txtSearch"
Insert also a button with "OK" caption. When the person writes
something in the text box, and press ok, it will search in ur table.

So, on_Click button event u can do something like:

dim sql as string

sql = "SELECT * FROM TABLE WHERE COLUMN_YOU_WANNNA_SEARCH LIKE "* &
txtSearch & *"

then u show the query results somewhere u might want. U can store it
in a recordset, or make it load in a subform.



Yumi

 

[Kamil]

Thank you mr. Yumi,
But I think I am making something wrong. I am getting an error message, and
I sent it to your gmail acount,
Regards...

 

[Douglas J. Steele]

There was an error in the suggestion made to you. Since you're looking for
Text, the seach term needs to be enclosed in quotes:

sql = "SELECT * FROM Table1 WHERE Field1 LIKE '*" & txtSearch & "*'"

Exagerated for clarity, that's

sql = "SELECT * FROM Table1 WHERE Field1 LIKE ' * " & txtSearch & " * ' "

That won't work, however, if there's an apostrophe in txtSearch. In that
case, you'll need one of:

sql = "SELECT * FROM Table1 WHERE Field1 LIKE ""*" & txtSearch & "*"""

sql = "SELECT * FROM Table1 WHERE Field1 LIKE " & Chr$(34) & _
"*" & txtSearch & "*" & Chr$(34)

sql = "SELECT * FROM Table1 WHERE Field1 LIKE '*" & _
Replace(txtSearch, "'", "''") & "*'"

(the Replace is Replace(txtSearch, " ' ", " ' ' "))

 

[Kamil]

Hello mr. Douglas,
Thank you for your reply but I am still having the same error message.
I think I am making a very simple mistake, but I don't know where I did.
I will be very happy, if you send me step by step what I have to do... My
table name is DENEME my field name that I will search for the word is
ALAN...

Regards...
Kamil...

 

[Douglas J. Steele]

Please show your code.

 

[Kamil]

Mr. Douglas this is the problem already. I have no any code except the
sentences mr. Yumi or you sent. I am trying to understand how to write it
and I need a sample. I have a big mdb file, I am about to finish data
entry. Some of the fields contain long texts, and I have to find any word
among these text. This is only one of the problem I have to solve. My
database includes a primary key, and I have to find programmatically the
datas corresponding to that key if I enter a value in a window for
instance. I can do it in Access by filtering out, but the potential users
may not have Ms Office, therefore I have to make an exe file...

For now, I want to make a form which asks the user to enter the word to be
searched:
like:
PLEASE ENTER THE WORD: ............the place word will be
input........................ (command button)
And when the command button is clicked, the results will be shown in screen
or will be stored in a text file...

Regards

 

[Douglas J. Steele]

You cannot make an exe file from an Access application, so you're already in
trouble! <g>

How do you want to ask the user? Do you want them to click on a button and
have an Input box appear, or do you want them to enter text into a text box
on a form, and then click on a button (or hit the Enter key)?

What do you want to happen once you know the text for which they're
searching? Do you want the matching data to be returned in a form, or some
other action to happen?

 

[Kamil]

I know I can't make an exe file with Access, I was thinking about Vbasic...
I want user to enter txt into a text box on a form, and then click on a
button, and if possible to store the matching data on a text file.
Regards...

 

[Douglas J. Steele]

If you're moving to VB, there's little point in showing you how to do it in
Access, but...

Create a query that returns all the fields you want, and put the following
in the Criteria cell under the appropriate field's name:

LIKE "*" & Forms!NameOfForm!NameOfTextbox & "*"

(replace "NameOfForm" and "NameOfTextbox" with the appropriate names)

In the button's Click event, use TransferText to transfer the query to the
text file.