Query returning exact matches only

[illdill]

SELECT * FROM SearchActive Where title LIKE '::title::' or author LIKE '::author::' or subject LIKE '::subject::'

What am I doing wrong? Say I search for the word 'Time' in the title, I should expect to get all of the items with the word Time in the title, but I do not. If I search for the EXACT title, it finds it.

Thanks,

Tim

 

[doco]

Try SELECT * FROM SearchActive Where title LIKE '*title*' using the wildcard '*'

 

[pbaldy]

You need wildcards:

LIKE '*title*'

 

[illdill]

I get no results either way with that query '*title*'

 

[pbaldy]

Presumably you'd want to change "title" to the actual word you're looking for.

 

[illdill]

But '::title::' represents a text field in a form....

 

[ByteMyzer]

You are trying to use ::title::, ::author:: and ::subject:: as parameters. That is not the correct syntax for parameters in Access SQL.

Try something like the following (substitute highlighted text with actual names):

SELECT *
FROM SearchActive
WHERE SearchActive.title LIKE '*' & [Forms]![MyForm]![Title] & '*'
AND SearchActive.author LIKE '*' & [Forms]![MyForm]![Author] & '*'
AND SearchActive.subject LIKE '*' & [Forms]![MyForm]![Subject] & '*';

NOTE: I changed the OR's to AND's, because if any one of the parameters is empty, the query will return all records for that field.

 

[illdill]

Would [MyForm] be the name of the page that the form is on? I am using FrontPage DRW...

 

[ByteMyzer]

I would suggest that you check the help files for Frontpage DRW with respect to SQL wildcards.

Incidentally, it would help next time if you specify up front that you are using a front end tool other than Access when posting such questions to this forum.

 

[illdill]

This was my solution...

SELECT * FROM SearchActive Where title LIKE '::title::%' OR author LIKE '::author::%' OR subject LIKE '::subject::%'

I got it from the FrontPage 2003 Bible (Wiley)

Thanks for all your help