search memo field using another field

[rcappell]

I have a database and I need to search in the body of the memo field for information in another tables field. Please help.

table 1
field Driveline
data : 805983D1755

table 2
field memo
data:
Turn on Speedometer Switches:
NO SPDO SWTCHS REQD
C/B D/L = 804248D1795
C/B Location @ 3204.0
ALUM STD 1810 3HOLE 16MM R036-226
X = 47.9
Y = 120.0
Ctr Brg Brkt = K257-223-11
Rear D/L = 805983D1755

 

[yammoo]

i wouldnt know how to do that directly, but can try the following link and convert the db to ur version of access. it searches the memo field and displays the highlighted result in a report. u can try to possibly tweak that to find things in other tables. give it a shot if u can.

http://www.lebans.com/mixbold-plain.htm

 

[DrSnuggles]

Look at InStr

 

[The_Doc_Man]

The problem with MEMO fields is that they cannot be JOINED effectively because they can't have a primary key. So you have to use less happy search criteria and methods. Such as the VBA INSTR function and more formal parsing routines.

I'm going to say it and you're not going to like it. This is a very bad design.

OK, now that I've hurt your feelings, you deserve to know why I said that.

Because... your data structure doesn't conveniently support one or more of the intended functions of the database. In this case, searching for a specific value in an arbitrary-length field of type MEMO, which cannot be easily used for normal search and matching functions. So you have to resort to VBA no matter how simple your intended search might have been.

One of the cardinal rules of DB design is that your structure must support your intended operations, or you are not going to have success. In that very specific criticism, you have failed to design proper support for search.

Oh, true, with VBA you can cover up a multitude of sins. But it would be better if you weren't a sinner in the first place.