Finding a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am relatively new to Access programming so please be patient. I have a
table (Tbl_Test) with 5 fields. I want to search for an enter word (inputbox
will do) which could be in any one of the 5 fields.

Can someone please advise how to produce a msgbox the says the entered word
was found in field x. Actually even better I would prefer to product a
datasheet-view-query that would show all of the table headings and all fields
in that found record(s).

Andrew B
 
Your description of the table's structure suggests that it is designed
incorrectly for database use. Having multiple fields with "similar" data
often is an indication that you need to normalize the data -- meaning, one
table as a parent, and one child table with three fields: one field to link
to the parent, one field with the type of data, and one field with the data
itself.

Such a child structure makes it easy to search the data as you wish.

Otherwise, with your structure, you'll need to normalize the data via a
union query:

SELECT FieldNameWithData1, "Field1"
FROM TableName
WHERE InStr([FieldNameWithData1], "TextToFind") > 0
UNION ALL
SELECT FieldNameWithData2, "Field2"
FROM TableName
WHERE InStr([FieldNameWithData2], "TextToFind") > 0
UNION ALL
SELECT FieldNameWithData3, "Field3"
FROM TableName
WHERE InStr([FieldNameWithData3], "TextToFind") > 0
UNION ALL
SELECT FieldNameWithData4, "Field4"
FROM TableName
WHERE InStr([FieldNameWithData4], "TextToFind") > 0
UNION ALL
SELECT FieldNameWithData5, "Field5"
FROM TableName
WHERE InStr([FieldNameWithData5], "TextToFind") > 0;


With this query, you can search all five fields; and each record has an
identifying field to tell you which field it is.
 
Back
Top