Search Form not finding all records, though they DO exist

[Modify_inc]

What could cause certain records to not be searchable when performing a search?



I have created a pretty simple Search Form that I can look up a record by a Job#, Phone#, or Name. It populates a list of record(s) on the bottom pane using a split-form.

The issue that I have recently discovered is that some records are not showing up when searched, though the record does exist. I can find the record by manually going to a record in the database that is close to the one I’m looking for and then using the previous and next record button to view the record I want.

So if a record exist and has the relevant data for the field that is being searched, why would it not find it? Using the default search built into Access on the bottom record bar, I can find the record using the search criteria I mentioned above, if that helps.

I also have the Search Form set that if no search criteria is entered, it will display ALL records. Even with all the records shown, these few records do not show up.

Could these few records have a feature or setting that is disabled that needs changed? If so, I have no idea what to look for at this point.


Thanks
Mike

[ranman256]

we would need an example of the keyword your are searching,
the example of what DIDNT get found,

it could be a the search string, the table structure..etc.

[Modify_inc]

we would need an example of the keyword your are searching,
the example of what DIDNT get found,

it could be a the search string, the table structure..etc.

Search Form has 3 search boxes:

Job#
Phone#
Name

I can enter any relevant data critera for the bad records. I'll call them bad since these few records are being difficult at the moment.

I can enter 612 for Job#, the Search Form finds nothing. Yet the record for job# 612 exist; I can go to it manually.
I can enter the phone# or the name for this same job/record, and the search form, once again, finds no records for this job. Yet the record for job# 612 exist; I can go to it manually.

Fortunately, most of the job searches work, as I have only recently found a few that do not and it is frustrating trying to understand what is different about these few job records. I even changed the job # from 612 to another number. Saved and closed out of the database. Opened it back up, performed a search for this job record using the new job # I assigned to it. No Records Found, yet I can go to it manually still.

[June7]

What method are you using for the search? Dynamic parameterized query? VBA to set filter property?

[Modify_inc]

Ok, I have found the culprit, but I am not sure how to resolve it.

The issue results when one of the search criteria is actually null or left blank in the record.

For example, I enter 612 for Job# 612. That job# is present in the record (Not blank), the name (which is another search criteria) is present in the record (Not blank). But the phone# (another search criteria) in the record is blank. So even though the search finds the job#, it will NOT display the record because the phone # field is blank in this particular record. Why it does this by default is beyond me. I am not even searching by phone# for this job, why should Access care if this field is left blank in the actual record? If I enter the job# for the record and that job# is present in the record, it should display that record.

I assume this behavior is the result of my query. I say this because I also have comments that are included in the query. You can't search for comments using my Search Form, but they are displayed next to their relative record. And if the comments are blank, or that particular record has no comments, then as described above, the record will NOT display.

So I think this confirms my theory the issue is in the query for the Search Form. What would I need to add to make the query show records even if some of the fields in the record are blank?

Thanks

[Modify_inc]

What method are you using for the search? Dynamic parameterized query? VBA to set filter property?

I have no ideal what those are, but the steps I used seem much easier than those two types you mentioned.

I use a Macro for each Search box

Job# Search:

ApplyFilter
Filter Name
WhereCondition =[Job Number]=[Forms]![Search]![Text12]
Control Name

Phone# Search:

ApplyFilter
Filter Name
WhereCondition =[Home Phone]=Forms![Search]!Text13
Control Name

Name Search:

ApplyFilter
Filter Name
WhereCondition =[Name] Like "*" & [Forms]![Search]![Text14] & "*"
Control Name

[June7]

You can use the wildcard on the [Home Phone] field - I assume it is text type.

If [Job Number] is also text type then use the wildcard there as well. If it is a number, then this becomes somewhat impractical. If this is text type, show example of values.

I don't use macros. I use VBA. Review http://allenbrowne.com/ser-62code.html

[Modify_inc]

I don't use macros. I use VBA. Review http://allenbrowne.com/ser-62code.html

Thanks for the link, but I'm confident the issue is not with the Macro or programming of the Search buttons, but more to do with the query of the search as I mentioned earlier.

I get the same end result if the comments field is also left blank, and yet I do not have a search box for comments.

The only mention of comments is found in my search query (So as to display the comments for the relative records that are searched). So I gather the search query is responsible for this madness.

Is there code I can add to the search query that basically says, "Hey Access! I want you to show all the records I search, WHETHER any of the other search query fields might be blank!"

I mean it works perfect until of the fields in the query for the search form is blank, then it is like Access goes stupid, and assumes I would not want to view the record because the phone# field is blank, or one of the other fields are blank. That is silly, of course I still want to view the record.

Thanks

[June7]

That's what the wildcard accomplishes. But it is only useful with text fields.

It isn't really silly. Access is doing what you ask it to. You reference a control as a parameter and then if that control is empty (Null or empty string), the only data it has to work with is Null or empty string. Null is ignored because can't do a comparison with Null and no records match empty string.

http://allenbrowne.com/casu-12.html

The concatenation of textbox Null with wildcard results in a parameter that says "match anything" but records with Null are still rejected because can't compare with Null.

Change the criteria to:

WhereCondition =[Name] Like "*" & [Forms]![Search]![Text14] & "*" Or Is Null

I am not sure what is happening with the comments field. What is the join type of the query?

[Modify_inc]

That's what the wildcard accomplishes. But it is only useful with text fields.

It isn't really silly. Access is doing what you ask it to. You reference a control as a parameter and then if that control is empty (Null or empty string), the only data it has to work with is Null or empty string. Null is ignored because can't do a comparison with Null and no records match empty string.

http://allenbrowne.com/casu-12.html

The concatenation of textbox Null with wildcard results in a parameter that says "match anything" but records with Null are still rejected because can't compare with Null.

Change the criteria to:

WhereCondition =[Name] Like "*" & [Forms]![Search]![Text14] & "*" Or Is Null

I am not sure what is happening with the comments field. What is the join type of the query?

Thanks for the clarification, it makes since when you put it like that.

I have some of the records that were not displaying before, now displaying, but I still have the same issue with records that have no comments.

I'm not sure how to go about resolving it, since as I mentioned earlier, I don't have a search box for comments, thus no programming or commands to alter. The comments are just added, to be displayed alongside their relative records in the search results.

I assume I would need to add the Is Null OR Is Not Null to the criteria field in the search query for the comments field. Anyhow, I did, but now I don't get any results for any search criteria I enter in the search boxes, so I reverted back.

This is the SQL data from my search query:

SELECT tblClientInfo.[Job Number], tblClientInfo.[Insured's Name], tblClientInfo.[Home Phone], tblNotes.NoteDate, tblNotes.User, tblNotes.Notes
FROM tblClientInfo INNER JOIN tblNotes ON tblClientInfo.[Job Number] = tblNotes.ClientID;

[June7]

Try LEFT or RIGHT JOIN instead of INNER: "Retrieve all records from tblClientInfo and only those from tblNotes that match"

[Modify_inc]

Try LEFT or RIGHT JOIN instead of INNER: "Retrieve all records from tblClientInfo and only those from tblNotes that match"

Thank you very much, the LEFT JOIN worked! I tried the RIGHT JOIN also, but it didn't work.

I will have to read up more on the LEFT, RIGHT and INNER JOIN as I do not understand their usage in the SQL programming.