Queries in Memo field

[kchard]

Hello forum,
Hoping to get some direction on this.. I have a database that contains contact record information and also their skillset, (Java, VB, PeopleSoft, etc.) which is stored in a memo field, separated by commas. I've learned, thanks to this forum, how to search on keywords within that memo field, such as the following query:

SELECT FirstName, LastName, Skillset
FROM tbl_candidate
WHERE Skillset Like '*Java*'

This returns the correct data. However, I need it to search on multiple criteria keywords using the AND operator, and also set up parameters so that users can enter their criteria. Something like this:

SELECT FirstName, LastName, Skillset
FROM tbl_candidate
WHERE Skillset Like '*[Enter Criteria1]*'
AND Skillset Like '*[Enter Criteria2]*'
AND Skillset Like '*[Enter Criteria3]*'

This currently returns all rows in the database and does not prompt for criteria.. Can what I've explained be done? Any direction is greatly appreciated..
Thanks in advance..

 

[GingerR]

ok, first i have to give you the big lecture about normalization and the basics of a relational database: instead of a memo field, the best thing to do is to have a second table i.e. tblSkills, which lists each skill one time
SkillID Skill
1 VB
2 Java
3 HTML
4 C++

then a third table (tblCandidateSkills) which creates the relationship between a candidate and their skills:

CandidateID SkillID
1 3
1 4
2 4
3 5

there are an incredible number of advantages: no mis-spellings by data-entry people (they choose from a drop-down box instead) or mis-spelllings by people trying to search, easier searching capabilities..blah blah there are lots of posts here about this kind of thing.

ok, well, that being said, try this: you have to parse the code a little differently:

SELECT FirstName, LastName, Skillset
FROM tbl_candidate
WHERE Skillset Like '*' & [Enter Criteria1] & '*'
AND Skillset Like '*' & [Enter Criteria2] & '*'
AND Skillset Like '*' & [Enter Criteria3] & '*'