Long text or memo box filtering

[data808]

I have a long text box or memo box that I would like to filter but do not know how. Can someone point me in the right direction on how to set up a filtering feature to use for these types of boxes? I have no problems with short text boxes.

[rpeare]

a far as I know (this is old knowledge I haven't messed with memo fields in a while) you can't use criteria similar to

Like *FIND THIS*

you would likely have to have an 'indicator' field

HasString: instr([MemoFieldName], "FIND THIS")

then anything with a HASSTRING value of greater than zero would contain the search string.

[data808]

Ok thanks.

What about duplicate filtering using a long text box? The thing is, I used the query wizard to create this and I can get it to work, however, the duplicates have to be identical. In my long text boxes I have serial number entered, sometimes containing more than 1 serial number. I would like the query to pick up the serial number even though it may have a different combination of serials in one field but if one of those serial numbers has been used before in another record, I would like it to show it. Here is an example:

If user enters this in two separate records:
Serial #s: 123456, 789456, 456789
Serial #s: 741852, 852963, 123456

I would like these records to show in the query because even though these records have 3 serial numbers each, the serial # 123456 is used in both records. Is this possible?

[rpeare]

You're talking about storing multiple values in a single field (or using a multi value field) which is a really bad idea. Without a normalized structure you have to rely on the data being in a specific format (in this case a comma followed by a space). I would strongly advise you to use a normalized structure, in your case, when you need to store a series of serial numbers store them as they relate to a specific object (perhaps components of a larger item). What you're asking is possible but you would have to parse the text string for each value and compare that to every value in every other field and that is incredibly inefficient.

[orange]

I agree with rpeare that designing and using normalized tables is the best strategy.

But. here is a small test, that may be helpful.

In a table "A" there are 3 records. TestMemo is a memo field. The other fields have no meaning - it's just a table for testing. Records 1 and 3 have the "123456" within the testmemo field, record 2 does not.
It's possible to find records containing a string in the testmemo field.

You could alter your form/report recordsource with a revised query, and requery.

It's not exactly your Filter, by accomplishes same thing.
Query SQL

SELECT Len([testmemo]) AS LEN_A, A.*
FROM A
WHERE (((A.testmemo) Like "*" & "123456" & "*"));

QueryResult

LEN_A
id
mmonthYr
dday
hrs
taskid
testmemo

662
1
32010
10
10.00
100
123456, 789456, 456789You're talking about storing multiple values in a single field (or using a multi value field) which is a really bad idea. Without a normalized structure you have to rely on the data being in a specific format (in this case a comma followed by a space). I would strongly advise you to use a normalized structure, in your case, when you need to store a series of serial numbers store them as they relate to a specific object (perhaps components of a larger item). What you're asking is possible but you would have to parse the text string for each value and compare that to every value in every other field and that is incredibly inefficient.

1377
3
42010
10
11.00
101
Serial #s: 741852, 852963, 123456What about duplicate filtering using a long text box? The thing is, I used the query wizard to create this and I can get it to work, however, the duplicates have to be identical. In my long text boxes I have serial number entered, sometimes containing more than 1 serial number. I would like the query to pick up the serial number even though it may have a different combination of serials in one field but if one of those serial numbers has been used before in another record, I would like it to show it. Here is an example:What about duplicate filtering using a long text box? The thing is, I used the query wizard to create this and I can get it to work, however, the duplicates have to be identical. In my long text boxes I have serial number entered, sometimes containing more than 1 serial number. I would like the query to pick up the serial number even though it may have a different combination of serials in one field but if one of those serial numbers has been used before in another record, I would like it to show it. Here is an example:

If user enters this in two separate records:
Serial #s: 123456, 789456, 456789
Serial #s: 741852, 852963, 123456

I would like these records to show in the query because even though these records have 3 serial numbers each, the serial # 123456 is used in both records. Is this possible?

[data808]

You guys are both right as I have noticed that the data that would need to be entered by the user would have to very specific in its structure and would not be a good way to look for data. Some of the data would probably end up being over looked and never seen. I don't think I will move forward with this approach. Thanks for bringing light to this matter. Appreciate it.