Multi-Field Search Query not working

[omair1051992]

Hello,



I used a Youtube video tutorial to make a multi-field search query. I have a form for navigating the records in my table. I made a similar form (with same text boxes i.e fields) called SearchForm. I want my query to search for records in the table that match the entries in the SearchForm. I have copied this code into the all fields in the form

Like "*" & [Forms]![SearchForm]![<<particular field>>] & "*"

The query shows a blank return, no matter what has been entered into the SearchForm. What should I do?

Thanks!

[June7]

Want to provide the db for analysis? Follow instructions at bottom of my post.

[omair1051992]

There you go, I have attached the database.

Mod Edit: Remove attachment because appears to have real personal data.

[June7]

Second time this week I've seen this same issue.

Problem is with Null fields. If you require a value in every field then this issue will not happen. Can populate with NA or whatever you like if no data provided. Otherwise, need to handle the Nulls in the query. Each field where Nulls are allowed will require an expression, like:

Middle: MiddleName & ""

Then apply the input parameter to the constructed field. The constructed fields do not have to be visible. The native fields can still be included in the query and controls bound to them.

[omair1051992]

I'm sorry but I do not quite understand how I am supposed to work that out. I do not require a value in every field; even if just one or two fields are filled out then the query should just filter using those.

What exactly should I change in this code, if each and every single field is allowed to have null value:
Like "*" & [Forms]![SearchForm]![FirstName] & "*"

[June7]

Yes, but unfortunately null interferes.

The parameter does not need to be changed. My instructions are to create fields with an expression like the one in my previous post that handle possible null and put the parameters under those constructed fields. The suggested expression will insure that every value is a string, even an empty string, and not a null.

[omair1051992]

I'm sorry but I am a complete novice in Access when it comes to these kinds of stuff. Where should I enter 'Middle: MiddleName & ""', and what exactly should I change to my table/query/form or whatever?

[June7]

Creating fields with expression in query is basic Access functionality. Review http://office.microsoft.com/en-us/ac...#_Toc288566554

So you need to use the query designer grid and:

1. create the Middle field with the expression: MiddleName & ""

2. put criteria parameter under the constructed field
Like "*" & [Forms]![SearchForm]![MiddleName] & "*"

[omair1051992]

I entered {FirstName: [FirstName] & ""} in the Field row of the query designer and Access gives an error 'Circular reference caused by alias 'FirstName' ...'

I entered {FirstName & ""} in the Field row. It automatically turns into {Expr1: [FirstName] & ""} but shows up each and every record when the query is run, no matter what values are entered in the SearchForm. The parameter is still the same.

I know how to make basic expressions for calculations but this is completely beyond me. I have attached the database for analysis again, with fake data.

[June7]

To avoid circular reference is why I used Middle as the name for field constructed with expression: MiddleName & "". You can use whatever you want in place of the generic Expr1, Expr2, etc or just leave them that way. Your Query2 seems to work just fine for me.

[omair1051992]

I changed the name fields to just First, Middle and Last and the circular reference error is handled. Query2 still doesn't work for me, showing up all the records regardless of what values are entered in the SearchForm.

[recyan]

Not sure, if this is what you are looking for.
Just check the search form in db
DB_1.zip
attached.

Thanks

[omair1051992]

I don't really see any difference. The queries are still the same and don't work at all.

[recyan]

1) The search query is now based on Query1.
2) Did you try using the search form, if yes what was the result ? For eg. If you enter lily in first name field in the form, what was the result ?

Thanks

[omair1051992]

Query1 doesn't contain any parameters in the criteria, just the fields and the table name.

When I enter Lily in the SearchForm, Query1 and SearchQuery2 show all the records. SearchQuery doesn't show any records. SearchQuery3 has circular reference error.