Search criteria (3 Viewers)

[Eljefegeneo]

I have been through the tutorials from uncle Gizmo but still don't see how to solve the problem if there is no data in a certain field. If all the fields in all the record are filled in, there is no problem. But If there is a null or blank value in one of the fields, it doesn't show up in the query. Or am I wrong about what is shown on the videos? I tried to insert a false criteria: insert and "x" into one of the unbound text boxes, but that didn't work either. The only way I think to do this is to have 49 different criteria with AND/OR statements (maybe even more) which will probably result in an error message telling me that the query is too complex.

I have been using a multi-search form which works fine, but there are just too many records; the need is for multi field filtering, i.e. all people named Jones in California. But if there is no telephone number, the the record is not returned.

Is there any solution to this or is this just not possible in Access 2010?

 

[vbaInet]

Is Null (i.e. the way I wrote it) and IsNull() (the way you wrote it) aren't quite the same in that context.

I'm going to use two fields as an example, Surname and Organisation. If I were to enter "how" in Surname and "ABC" in Organisation, what record(s) do you expect to see?

 

[GinaWhipp]

Hmm this might help...

IsNull vs Is Null
http://www.techrepublic.com/article/learn-the-differences-between-is-null-and-isnull-in-access/

So, your Criteria should look like...

Like "*" & [Forms]![frmJeffSearch]![[COLOR=red]txtSurname[/COLOR]] & "*" Or Is Null

Change field names (red) accordingly but second half should be as above not IsNull([txtSurname]), that is never going to work.

That said, I prefer not to perform the search via a query but use a Form. I use Allen Browne's Search Form...
http://allenbrowne.com/ser-62.html

Allows for a lot more flexibility as I can also pass the WHERE Statement to a Report without tying up two seperate queries (one for the Form and one for the Report).

Just my two cents...

 

[Eljefegeneo]

I finally had a chance to change all the code as directed and it seems to work fine. But this may be a case of be careful what you ask for. Maybe now there is too much date which includes all null fields. But it certainly is better than what I had previously that only brought up about 70% of the records. So this is a work still in progress. But I thank both of you who spent time on this and put up with my questions.

 

[GinaWhipp]

Glad you finally got it working... and that's what we're for, you ask we answer!

 

[DevTycoon]

I have tried Allen Brown method and uncle Gizmo. The only difference is Allen uses a set filter command and uncle gizmo uses a record source command on the control. Both work well but break the same way when used a certain way..................Suppose someone typed the entire string in for the Surname organization and program title. No results appear. Its like the wild card does not like it when you input the entire field value.

I need the user to be able to type in the entire field value or a portion of the value and have all results populate. :banghead: :banghead::banghead:

 

[vbaInet]

I have tried Allen Brown method and uncle Gizmo. The only difference is Allen uses a set filter command and uncle gizmo uses a record source command on the control. Both work well but break the same way when used a certain way..................Suppose someone typed the entire string in for the Surname organization and program title. No results appear. Its like the wild card does not like it when you input the entire field value.

I need the user to be able to type in the entire field value or a portion of the value and have all results populate. :banghead: :banghead::banghead:

Perhaps you can create a thread of your own (instead of hijacking someone else's) and we can look into it for you. And please when you do, give more information like showing us the SQL statement you wrote.

 

[DevTycoon]

Perhaps you can create a thread of your own (instead of hijacking someone else's) and we can look into it for you. And please when you do, give more information like showing us the SQL statement you wrote.

My apologies.

 

[vbaInet]

It just helps to get your question answered quicker. If we see new threads we tend to follow-up those threads quickly. And although your problem may be related to this one, the original poster may get confused and he/she will keep getting notification e-mails when a message is posted.

When you create your thread it would helpful if you could post a link to this thread so we know where to reference