Annoying Query, yet simple? - Please Help.

[manowar]

Hi guys,

A friend of mine has recently created a simple database which holds details of childrens story books. It logs the name of the Author, the storyline, its character etc in various tables...

He's currently creating some simple queries for his database. However, there is one query which neither he nor I can figure out, I'll try to explain to the best of my ability.

Basically, the 'story line' field in his database explains the basic plot of the book, some of the plots have the words "son", "dad", "father" and "boy" in them.

Now, he wants to create a simple query, which looks up and displays data which contains the keywords "dad", "father", "son" etc in them. So far he's tried...

Like "*Son*" Or Like "*Father*" etc...

This worked, briefly. The query was bringing up storylines with both contained the words "son" and "father" , which is correct. However, the query was also bringing up story lines with the word "song" in them, or any word which remotely related to one of the keywords, but didn't contain the exact criteria in the query (son, father etc).

In basic terms, how do you create a query which only brings up words that match the EXACT query criteria? In this case, 'Son' or 'Father' or 'Dad' etc? Rather than words which only briefly relate to the criteria, such as 'Song' or 'Fat' etc?

Thanks for your cooperation, feedback and examples would be highly appreciated.

Thanks all,

Jonathan.

 

[RuralGuy]

"* " & "Father" & " *", "* " & "Son" & " *"

 

[directormac]

The Details...

Expanding on Rural Guy's correct suggestion, Access was giving you exactly what you asked for. If your query searches for "*son*", then:

"Good Son"
"Son of Sam"
"Samson"
"Dry White Season"​

are all valid results because they all contain the characters "s", "o", and "n" in sequence. The difference in what RG showed you is the spaces, which means that Access will return only hits that contain "<space>son<space>" (only the first two of the list above would be hits).

--Searching Mac

 

[stephen81]

"* " & "Father" & " *", "* " & "Son" & " *"

This however might not pick up every instance of those words as it will only pick them up when they come directly before and after a space. If there are instances where they might be followed by a comma or full stop (or period for our American friends) then they won't be picked up as it is looking for a space immediately after.

 

[stephen81]

I think you might be able to refine your query by putting in a range of values instead of the spaces.

i.e. something like

"*[ ,.]" & "Father" & "[ ,.]*", "*[ ,.]" & "Son" & "[ ,.]*"

The idea of the square brackets is that it will return a match if it finds one of the characters within the brackets. I'm not sure if I've got the expression exactly right, I've not tried it, but something along those lines might do the trick.