Adding wildcard character to query criteria

[bees292]

I've got a query that does exactly what this chap got his to do.

However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:

[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null

However when I try the logical extension:

[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null

this doesn't work, and nor does:

[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null

WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! thanks and sorry for being so dumb!

 

[TessB]

Did you try

[title] Like [Forms].[Search]![Title] & "*"

instead of =

 

[bees292]

Yes I have tried that. The difference between that and the one above is that the 'Like.... & "*"' doesn't include null records.

This is what I want it to do:

3 fields (for now)

Field A
Field B
Field C

If I enter text into fields A and B I want it to search using criteria for just those fields (ignoring C), and this is what the 1st bit of my 1st post does. I now want to add a wild card function to each search field so I can search for all records matching in part. e.g.,

Search Bloggs - return records for bloggsy, bloggydodah etc.

Your expression

[title] Like [Forms].[Search]![Title] & "*"

does in fact do that but will not include null records.

 

[TessB]

If you want to include Null records, wouldn't you use

[title] Like "*" & [Forms].[Search]![Title] & "*" OR [title] Is Null OR [Forms].[Search]![Title] Is Null Or [Forms].[Search]![Title] Like ""


This is saying;
Return all records in which the title field includes anything entered in the form's input box, include records where the title field is null, and if the Form field is blank, return all records.

 

[bees292]

I thought you couldn't use Is Null with Like?

 

[TessB]

You can't say [title] Like Is Null.

You can say [title] Like "" OR [title] Is Null.

 

[bees292]

If you want to include Null records, wouldn't you use

[title] Like "*" & [Forms].[Search]![Title] & "*" OR [title] Is Null OR [Forms].[Search]![Title] Is Null Or [Forms].[Search]![Title] Like ""


This is saying;
Return all records in which the title field includes anything entered in the form's input box, include records where the title field is null, and if the Form field is blank, return all records.

That works well. Thank you very much!