Search by Keywords

[Bibi]

I have a property management db that includes a work order component .
I am self taught.
There are gaps in my knowledge and understanding but in general the db has
worked really well for 8 years. The users now want to be able to search work
orders by KEYWORD. They want to be able to do this from a form – they do not
want to use the filter icon.

The work order data does not at this time have set keywords and there are
over 30,000 records so reviewing them and adding a selected keyword or words
is not an option.

Two tables are currently involved:
Work Orders and Properties
Fields:
in the Work Orders table
Number
Property ID
Date Reported
Problem
ActionTaken

in the Properties table:
Property ID
Property Name
Address


I have a query that will capture the Problem and Action Taken text in one
field – but now – I am stumped – I do not know how to filter by one let alone
multiple key works –Ideally, the user would like to filter by all or part of
a word and have a drop down box for this....here's the one query I have:

SELECT [Work Orders].Number, [Work Orders].[Property ID],
Properties.[Property Name], Properties.Address, [Work Orders].[Date
Reported], "Problem:" & [Problem] & " Action Taken:" & [Action Taken] AS
ProblemAndOrActionTaken
FROM Properties INNER JOIN [Work Orders] ON Properties.[Property ID] = [Work
Orders].[Property ID];


All help appreciated.
TIA
Bibi

 

[Damon Heron]

You are almost there! Use a where statement and LIKE
Say the user types in textbox1 "Widgets". The where statement on the end of
your query statement would be
WHERE WorkOrders.Problem LIKE "*" & [textbox1] & "*"
If there are multiple keywords you will have to separate them with an OR,
but this should get you started.

Damon

 

[Bibi]

Damon
thank you - that did help me move closer......but I'm not there
yet.............I'm having trouble in two areas - I just can't figure out the
unbound box...set up - I've created a form with one- I don't know how to tie
it's source to the filter??? the way it's operating now - when I try to open
the form - the query dialog pops up, I enter the key word and the form
opens with the correct data but the text box is empty ---just there for show
so to speak....I think I need to have the text box tied to the filter but...I
am at a loss....and I can't get the OR to work....but first it's the unbound
text box is my current hurdle....
--
TIA
Bibi

You are almost there! Use a where statement and LIKE
Say the user types in textbox1 "Widgets". The where statement on the end of
your query statement would be
WHERE WorkOrders.Problem LIKE "*" & [textbox1] & "*"
If there are multiple keywords you will have to separate them with an OR,
but this should get you started.

Damon

I have a property management db that includes a work order component .
I am self taught.
There are gaps in my knowledge and understanding but in general the db has
worked really well for 8 years. The users now want to be able to search
work
orders by KEYWORD. They want to be able to do this from a form - they do
not
want to use the filter icon.

The work order data does not at this time have set keywords and there are
over 30,000 records so reviewing them and adding a selected keyword or
words
is not an option.

Two tables are currently involved:
Work Orders and Properties
Fields:
in the Work Orders table
Number
Property ID
Date Reported
Problem
ActionTaken

in the Properties table:
Property ID
Property Name
Address


I have a query that will capture the Problem and Action Taken text in one
field - but now - I am stumped - I do not know how to filter by one let
alone
multiple key works -Ideally, the user would like to filter by all or part
of
a word and have a drop down box for this....here's the one query I have:

SELECT [Work Orders].Number, [Work Orders].[Property ID],
Properties.[Property Name], Properties.Address, [Work Orders].[Date
Reported], "Problem:" & [Problem] & " Action Taken:" & [Action Taken] AS
ProblemAndOrActionTaken
FROM Properties INNER JOIN [Work Orders] ON Properties.[Property ID] =
[Work
Orders].[Property ID];


All help appreciated.
TIA
Bibi

 

[Damon Heron]

On an unbound form, add a subform based on the MSysAccessStorage table as an
example.
Just include the 4 fields DateCreate, DateUpdate, ID, and Name on your
subform.
Add a textbox on the main form.

In the after update event of the textbox enter this code:

Private Sub Text10_AfterUpdate()
Dim strsql As String
Dim strWhere As String
Dim replstr As String
replstr = "*" & """" & " Or (MSysAccessStorage.Name) Like " & """" & "*"
Dim mytxt As String
mytxt = "*" & Me.Text10
mytxt = Replace([mytxt], ",", replstr, 1)
mytxt = mytxt & "*"
strsql = "SELECT MSysAccessStorage.DateCreate, MSysAccessStorage.DateUpdate,
MSysAccessStorage.Id, MSysAccessStorage.Name " & _
"FROM MSysAccessStorage"
strWhere = " WHERE (((MSysAccessStorage.Name) Like " & """" & [mytxt] & """"
& "));"
strsql = strsql & strWhere
Me!frmMSysAccessStorage.Form.RecordSource = strsql
End Sub

Save and close the form. Open and:
Using a comma separator, (with no spaces) type in md,dir,blo,da in the
textbox
and hit enter.
Your subform MSysAccessTable will be filtered to just those records with
those keywords.

You can adapt this example to your own form. The textbox could be further
refined to check for spaces (which will stymie the filter) and an ending
comma.

Damon

Damon
thank you - that did help me move closer......but I'm not there
yet.............I'm having trouble in two areas - I just can't figure out
the
unbound box...set up - I've created a form with one- I don't know how to
tie
it's source to the filter??? the way it's operating now - when I try to
open
the form - the query dialog pops up, I enter the key word and the form
opens with the correct data but the text box is empty ---just there for
show
so to speak....I think I need to have the text box tied to the filter
but...I
am at a loss....and I can't get the OR to work....but first it's the
unbound
text box is my current hurdle....
--
TIA
Bibi

You are almost there! Use a where statement and LIKE
Say the user types in textbox1 "Widgets". The where statement on the end
of
your query statement would be
WHERE WorkOrders.Problem LIKE "*" & [textbox1] & "*"
If there are multiple keywords you will have to separate them with an OR,
but this should get you started.

Damon

I have a property management db that includes a work order component .
I am self taught.
There are gaps in my knowledge and understanding but in general the db
has
worked really well for 8 years. The users now want to be able to
search
work
orders by KEYWORD. They want to be able to do this from a form - they
do
not
want to use the filter icon.

The work order data does not at this time have set keywords and there
are
over 30,000 records so reviewing them and adding a selected keyword or
words
is not an option.

Two tables are currently involved:
Work Orders and Properties
Fields:
in the Work Orders table
Number
Property ID
Date Reported
Problem
ActionTaken

in the Properties table:
Property ID
Property Name
Address


I have a query that will capture the Problem and Action Taken text in
one
field - but now - I am stumped - I do not know how to filter by one let
alone
multiple key works -Ideally, the user would like to filter by all or
part
of
a word and have a drop down box for this....here's the one query I
have:

SELECT [Work Orders].Number, [Work Orders].[Property ID],
Properties.[Property Name], Properties.Address, [Work Orders].[Date
Reported], "Problem:" & [Problem] & " Action Taken:" & [Action Taken]
AS
ProblemAndOrActionTaken
FROM Properties INNER JOIN [Work Orders] ON Properties.[Property ID] =
[Work
Orders].[Property ID];


All help appreciated.
TIA
Bibi