Search Form not pulling records unless completely blank

[Eirea]

I'm building a database and am currently trying to input a search function. The problem is, it's a little complicated for a combo box and I'm not exactly a wizard with Access (or Visual Basic, which is something this seems it will require). I have never done any real training in any of this, and only know what I've been able to figure out from pulling apart previous databases and from Google so I apologize for all the gaps I have in my knowledge about this stuff.



I've created a query and a search form, and the two are even linked. When the search form is empty, it will pull up all records. The moment I type something into any of the boxes, even if I know there is data it should pull up, it all comes up empty. Currently I have the query criteria set to the following:

Like [Forms]![f-CCWaitListSearch]![txtCRLast] & "*"
or
[Forms]![f-CCWaitListSearch]![txtCRLast] is Null

There are 12 boxes people need to be able to fill in to search, but four are true/false and one is a date (so far, I've left all that blank in the query so I don't know if that has something to do with it). I need the database to be able to search any of the boxes, with both complete and incomplete information (for example, they have the first and last name, just a last name, or just part of the last name).

I've also added the following in VB for the search form:

Me.txtPIMS.Value = ""
Me.txtCRFirst.Value = ""
Me.txtCRLast.Value = ""
Me.txtCGFirst.Value = ""
Me.txtCGLast.Value = ""
Me.txtCounty.Value = ""
Me.txtAssessor.Value = ""

Unless I am much mistaken, that just clears out the search boxes for the users and the search wasn't working right before I added that to the mix, but I thought it might help to have everything cleared out.

I'd appreciate any help you can give me!

[Eirea]

Update!!

I changed the search information in the query to:

Like "*" & [Forms]![f-CCWaitListSearch]![txtEnterCareRecipientLastName] Or IsNull("*")

Which Access seems to like better, but it won't search the beginning of the data. So if I search for "smith" the name will come up. If I search for "ith" I will also get results. If I search for "smi" nothing comes up.

[NTC]

well you post yourself as a novice - although your code is not novice...... but the novice answer is to rely on the filtering inherent in the Access product. In your form - right click in any field and explore the various filtering/sort/search features that are there. It is very comprehensive. One needs to learn just a little about Access.

to build your own I would not use the method of relying on a query, and then calling criteria from form controls - - this approach works fine for more static query selection situations and we do that all the time.... for instance to date range a report with a Start Date and an End Date which are fields/controls in a form.

but for a more flexible search situation one really should build the sql statement from scratch and run it totally in vba. example of that makes for a long post so I'll just suggest you bing/google on the topic of building sql statement where you will find other Q&A with samples. A helpful way to get started on it is to put your core query into sql view and then copy that code into your vba area - its a short cut to at least bringing in all the basic select code.... get that tweaked so it runs plain vanilla so that you can then focus on modifying it based on the control values of your form. in vba you'll be able to use if/then or case methods to give a lot more flexibility in the search criteria.

hope this helps

[Eirea]

Thanks for the reply! It's nice to hear someone tell me my code looks good, but I just copied and pasted most of it from another database or Google and updated it so I don't think I can take too much credit.

Also, do you have any good resources to see how this works? I've been looking but I don't think I'm asking Google the right questions because I'm not getting anywhere. I've figured out how to convert my SQL code to VBA, but I'm at a loss of what I do with it now.

[orange]

Here is a good source of info for Access http://www.accessmvp.com/strive4peace/

Good luck with you project.

[Eirea]

Okay, I've been looking through the resources (thank you very much!) and I think I'm over-analyzing something. I understand how to convert my SQL text into something I can use in VBA, but where does it go? Do I need to make a new module or should I be inserting it under the form I intend to run it off or what?

Sorry for being dense, I just have some kind of brain-block about this for some reason.

[orange]

It may be useful to readers if you would describe your business and its data in plain simple English, just so people can understand your situation/opportunity.
It is difficult to give focused advice since we don't know all the facts and we don't know your skills.

You can look at Crystal's vba section in the link I provided.
You can try youtube Forms Access Search
You can try google MS Access Search example vba

Or you can ask more focused question as long as you provide some context for readers.

You may get some ideas from the search code here, but you will need to be familiar with vba.

Good luck.