Wildcard search

[Weezie1383]

Hi,

I have a wildcard search which is linked to a form with 2 textboxes on it
which allow my users to enter search criteria. My current SQL statement looks
like this:

SELECT DOCUMENTS.[UNIQUE ID], DOCUMENTS.[DOC ID], DOCUMENTS.[DOC TITLE]
FROM DOCUMENTS
WHERE (((DOCUMENTS.[DOC ID]) Like [Forms]![frmWildcardSearch]![cboDocNumber]
& "*") AND ((DOCUMENTS.[DOC TITLE]) Like
[Forms]![frmWildcardSearch]![cboDocTitle] & "*")) OR (((DOCUMENTS.[DOC
TITLE]) Like [Forms]![frmWildcardSearch]![cboDocTitle] & "*") AND
(([Forms]![frmWildcardSearch]![cboDocNumber]) Is Null)) OR (((DOCUMENTS.[DOC
ID]) Like [Forms]![frmWildcardSearch]![cboDocNumber] & "*") AND
(([Forms]![frmWildcardSearch]![cboDocTitle]) Is Null)) OR
((([Forms]![frmWildcardSearch]![cboDocNumber]) Is Null) AND
(([Forms]![frmWildcardSearch]![cboDocTitle]) Is Null));

This search currently only will return search results if the word typed in
is the first word in the document number or title. I know there is a way to
get the search to return results for a word wherever it exists in the
sentence, but due to my lack of access experience I'm not sure how to modify
my SQL statement to allow for this.

Any help would be greatly appreciated!

 

[Allen Browne]

Add the wildcard in front of the parameter, as well as after, e.g.:

WHERE DOCUMENTS.[DOC ID] Like "*" &
[Forms]![frmWildcardSearch]![cboDocNumber] & "*"

Hopefully you do realise that this may not be reliable, e.g.:
- If [DOC ID] is a Number field, and someone chooses number 1, you will also
get 11, 12, 13, ... 100, 101, ... 201, 210, 211, etc.

- If the field is null, the record will not be matched.

 

[Weezie1383]

Thanks so much for the help! It works perfectly!

Add the wildcard in front of the parameter, as well as after, e.g.:

WHERE DOCUMENTS.[DOC ID] Like "*" &
[Forms]![frmWildcardSearch]![cboDocNumber] & "*"

Hopefully you do realise that this may not be reliable, e.g.:
- If [DOC ID] is a Number field, and someone chooses number 1, you will also
get 11, 12, 13, ... 100, 101, ... 201, 210, 211, etc.

- If the field is null, the record will not be matched.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a wildcard search which is linked to a form with 2 textboxes on it
which allow my users to enter search criteria. My current SQL statement
looks
like this:

SELECT DOCUMENTS.[UNIQUE ID], DOCUMENTS.[DOC ID], DOCUMENTS.[DOC TITLE]
FROM DOCUMENTS
WHERE (((DOCUMENTS.[DOC ID]) Like
[Forms]![frmWildcardSearch]![cboDocNumber]
& "*") AND ((DOCUMENTS.[DOC TITLE]) Like
[Forms]![frmWildcardSearch]![cboDocTitle] & "*")) OR (((DOCUMENTS.[DOC
TITLE]) Like [Forms]![frmWildcardSearch]![cboDocTitle] & "*") AND
(([Forms]![frmWildcardSearch]![cboDocNumber]) Is Null)) OR
(((DOCUMENTS.[DOC
ID]) Like [Forms]![frmWildcardSearch]![cboDocNumber] & "*") AND
(([Forms]![frmWildcardSearch]![cboDocTitle]) Is Null)) OR
((([Forms]![frmWildcardSearch]![cboDocNumber]) Is Null) AND
(([Forms]![frmWildcardSearch]![cboDocTitle]) Is Null));

This search currently only will return search results if the word typed in
is the first word in the document number or title. I know there is a way
to
get the search to return results for a word wherever it exists in the
sentence, but due to my lack of access experience I'm not sure how to
modify
my SQL statement to allow for this.

Any help would be greatly appreciated!