Change query criteria by form with wildcards

[Benny]

I'm using access 2000. I am trying to set up a form that will change the
criteria in a query to show whatever has been typed into a text box on the
form. I have successfully managed to link a parameter query to the form,
however I would like the "search" facility to change the criteria in more
than one field (same criteria in both) and to have the ability to include
wildcard characters in the criteria. Whenever I try to use an asterisk (for
example) in the text box, access takes it literally and searches for the term
including the asterisk. Any suggestions?

 

[Dale Fye]

Benny,

Can you give us an example of what the SQL string is that is returning the
wrong results? If you are building this string programmatically, then
provide a code snipet so we can see how it is being built.

Dale

 

[John Spencer]

Are you using the LIKE operator for the comparison? If you use the
equals operator (=) then the wildcard character is treated as if it were
just a character and not as if it were a wildcard.

The other possibility is that you are not using an mdb and that the wild
card characters are different than you expect. If you are using
Like Forms!YourFormName!YourControlName
and that is failing try using "%" in place of "*" and "_" in place of "?".

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Helen Martin]

I'm using access 2000. I am trying to set up a form that will change the
criteria in a query to show whatever has been typed into a text box on the
form. I have successfully managed to link a parameter query to the form,
however I would like the "search" facility to change the criteria in more
than one field (same criteria in both) and to have the ability to include

I have a similar problem to this, but without the wildcards...

I have a database which keeps the records for a sheet music library...

I have created a search form with several combo boxes to allow people to
select criteria from some pick lists...

I have a command button which triggers a query...

this is the SQL statement for the query..

SELECT libraryitems.itemID, libraryitems.subject, libraryitems.category,
libraryitems.genre, libraryitems.composer, libraryitems.title,
libraryitems.method, libraryitems.edition, libraryitems.instrumentation
FROM libraryitems
WHERE (((libraryitems.subject)=[forms]![frmsearch].[subject]) AND
((libraryitems.category)=[forms]![frmsearch].[category]) AND
((libraryitems.genre)=[forms]![frmsearch].[genre]) AND
((libraryitems.composer)=[forms]![frmsearch].[composer]) AND
((libraryitems.title)=[forms]![frmsearch].[title]) AND
((libraryitems.method)=[forms]![frmsearch].[method]) AND
((libraryitems.edition)=[forms]![frmsearch].[edition]));


I want people to be able to enter more than one criteria.. ie, look for
all the music for strings by JS Bach, or just look for everything by
Bach..

I figure if I enter no criteria, it should return the whole table.. but
instead I get nothing... if I enter one criteria, I still return
nothing, etc.. I know I'm close.. but I've obviously missed
something vital.. can you see what I am doing wrong...?

 

[Helen Martin]

I'm using access 2000. I am trying to set up a form that will change
the criteria in a query to show whatever has been typed into a text
box on the form. I have successfully managed to link a parameter query
to the form, however I would like the "search" facility to change the
criteria in more than one field (same criteria in both) and to have
the ability to include

I have a similar problem to this, but without the wildcards...

I have a database which keeps the records for a sheet music library...

I have created a search form with several combo boxes to allow people to
select criteria from some pick lists...

I have a command button which triggers a query...

this is the SQL statement for the query..

SELECT libraryitems.itemID, libraryitems.subject, libraryitems.category,
libraryitems.genre, libraryitems.composer, libraryitems.title,
libraryitems.method, libraryitems.edition, libraryitems.instrumentation
FROM libraryitems
WHERE (((libraryitems.subject)=[forms]![frmsearch].[subject]) AND
((libraryitems.category)=[forms]![frmsearch].[category]) AND
((libraryitems.genre)=[forms]![frmsearch].[genre]) AND
((libraryitems.composer)=[forms]![frmsearch].[composer]) AND
((libraryitems.title)=[forms]![frmsearch].[title]) AND
((libraryitems.method)=[forms]![frmsearch].[method]) AND
((libraryitems.edition)=[forms]![frmsearch].[edition]));


I want people to be able to enter more than one criteria.. ie, look for
all the music for strings by JS Bach, or just look for everything by
Bach..

I figure if I enter no criteria, it should return the whole table.. but
instead I get nothing... if I enter one criteria, I still return
nothing, etc.. I know I'm close.. but I've obviously missed
something vital.. can you see what I am doing wrong...?

I just found Allen's search database page.. I'm checking that..

 

[Benny]

Thanks John. Now using Like "*" & [Forms]![Find Form]![Text4] & "*" and it's
working great.

 

[Benny]

Feel like I'm being cheeky but could you help me with a problem that runs on
from the last?

I want to be able to narrow the search by adding other search criteria in
another text box. I currently have SQL of:

SELECT StateAidLibrary.File, StateAidLibrary.[Document Title],
StateAidLibrary.[Document Number]
FROM StateAidLibrary
WHERE (((StateAidLibrary.File) Like "*" & [Forms]![Find Form]![Text4] &
"*")) OR (((StateAidLibrary.[Document Title]) Like "*" & [Forms]![Find
Form]![Text4] & "*"));

If I were to add a text box [Text14] how would i need to alter the SQL to
ensure that if one of the text boxes were blank (and the other was not) that
the query wouldn't show all the records, and that if both were not blank that
the query would return records only with both search terms present?