Help creating a query for a search form in Visual Basic

[Eirea]

I am trying to create a query that runs off a search form (I think the term is dynamic query?) but I can't run it through the SQL code because I have far too much information that I need it to run through to design it in SQL. I have seven text boxes and four check boxes that run off three tables that I need to be able to search through, and the text boxes need to be able to find whole or partial information (so either the name John, or the letters "jo"). They also need to be able to work together, so if I have John Smith or J Smith or Jo Smi the search function needs to be able to pull up matching records. I only need it to pull up the query as the end result, as opposed to a report because there is more information that the users don't need to search through but need to see as an end result and a report makes it more difficult to read.

When I tried to create the search in the SQL statement, it tells me that it is too long to run through via SQL statements. I know I can create a search function in Visual Basic, but I can't figure out how. I've been sorting through the resources on Google, but I'm not very good with Visual Basic. If I understand how to replace my information with theirs, the statement is too basic and doesn't do what I need it to. If I can't figure out where my information goes, the code seems to be what I need and I'm completely lost trying to read it or update it.

I apologize for the odd gaps in my knowledge and vocabulary for Access; most of what I'm learning is through Google and while I try to understand as much as I can about something before using it, I'll probably ask a lot of questions that I should know the answer to and don't.

If anyone has a sample for a search function and can tell me where to substitute in my own information, I'm certain I can build it myself, I just don't know what I'm doing well enough to do it from the ground up.

The following is the disaster I need to convert from SQL to visual basic. I'm sorry that the formatting isn't very good, I'm not sure how I'm supposed to format it for easy reading (though if someone lets me know, I'll fix it).

SELECT [t-ConsumerInformation].RecipientID, [t-ConsumerInformation].[PIMS #], [t-ConsumerInformation].ConsumerLastName, 
[t-ConsumerInformation].ConsumerFirstName, [t-ConsumerInformation].ConsumerPhoneNumber, [t-ConsumerInformation].ConsumerCellPhoneNumber, 
[t-ConsumerInformation].ConsumerAddress, [t-ConsumerInformation].ConsumerCity, [t-ConsumerInformation].ConsumerState, 
[t-ConsumerInformation].ConsumerZip, [t-ConsumerInformation].ConsumerCounty, [t-ConsumerReferral].DateAddedToWaitList, [t-ConsumerReferral].Assessor, 
[t-ConsumerReferral].CareCoordinationReferral, [t-ConsumerReferral].AlzheimersRespiteReferral, [t-ConsumerReferral].FamilyCaregiverReferral, 
[t-ConsumerReferral].RemoveFromWaitlist, [t-CaregiverInformation].CaregiverLastName, [t-CaregiverInformation].CaregiverFirstName, 
[t-CaregiverInformation].RelationshipToConsumer, [t-CaregiverInformation].CaregiverPhoneNumber, [t-CaregiverInformation].CaregiverCellPhoneNumber, 
[t-CaregiverInformation].CaregiverAddress, [t-CaregiverInformation].CaregiverCity, [t-CaregiverInformation].CaregiverState, 
[t-CaregiverInformation].CaregiverZip, [t-CaregiverInformation].CaregiverEmailAddress

FROM ([t-ConsumerInformation] 

INNER JOIN [t-CaregiverInformation] ON [t-ConsumerInformation].RecipientID = [t-CaregiverInformation].[ConsumerID]) 

INNER JOIN [t-ConsumerReferral] ON [t-ConsumerInformation].RecipientID = [t-ConsumerReferral].[ConsumerID]

WHERE ((([t-ConsumerInformation].[PIMS #]) Like "*" & [Forms]![f-CCWaitListSearch]![txtEnterPIMS] Or ([t-ConsumerInformation].[PIMS #])=IsNull("*")) 

AND (([t-ConsumerInformation].ConsumerLastName) Like "*" & [Forms]![f-CCWaitListSearch]![txtEnterCareRecipientLastName] Or 
([t-ConsumerInformation].ConsumerLastName)=IsNull("*")) AND (([t-ConsumerInformation].ConsumerFirstName) Like "*" & [Forms]![f-CCWaitListSearch]!
[txtEnterCareRecipientFirstName] Or ([t-ConsumerInformation].ConsumerFirstName)=IsNull("*")) 

AND (([t-ConsumerInformation].ConsumerCounty) Like "*" & [Forms]![f-CCWaitListSearch]![txtCounty] Or 
([t-ConsumerInformation].ConsumerCounty)=IsNull("*")) AND (([t-ConsumerReferral].Assessor) Like "*" & [Forms]![f-CCWaitListSearch]![txtEnterAssessor] Or 
([t-ConsumerReferral].Assessor)=IsNull("*")) AND (([t-ConsumerReferral].CareCoordinationReferral) Like IIf([Forms]![f-CCWaitListSearch]!
[txtCareCoordinationReferral]=1,"*",[Forms]![f-CCWaitListSearch]![txtCareCoordinationReferral])) 

AND (([t-ConsumerReferral].AlzheimersRespiteReferral) Like IIf([Forms]![f-CCWaitListSearch]![txtAlzheimersRespiteReferral]=1,"*",[Forms]![f-CCWaitListSearch]!
[txtAlzheimersRespiteReferral])) AND (([t-ConsumerReferral].FamilyCaregiverReferral) Like IIf([Forms]![f-CCWaitListSearch]![txtFamilyCareGiverReferral]=1,"*",
[Forms]![f-CCWaitListSearch]![txtFamilyCareGiverReferral])) AND (([t-ConsumerReferral].RemoveFromWaitlist) Like IIf([Forms]![f-CCWaitListSearch]!
[txtRemoveFromWaitList]=1,"*",[Forms]![f-CCWaitListSearch]![txtRemoveFromWaitList])) 

AND (([t-CaregiverInformation].CaregiverLastName) Like "*" & [Forms]![f-CCWaitListSearch]![txtEnterCareGiverLastName] Or 
([t-CaregiverInformation].CaregiverLastName)=IsNull("*")) AND (([t-CaregiverInformation].CaregiverFirstName) Like "*" & [Forms]![f-CCWaitListSearch]!
[txtEnterCareGiverFirstName] Or ([t-CaregiverInformation].CaregiverFirstName)=IsNull("*")));

[Ruegen]

Do you want to create a multi search form that searches through a list of records depending on what you have typed/selected on the controls of that form?

So say type "bob" into name field then it brings all records that have bob in it even say.. susan bobbins... etc?


If you are there are couple of things I have learnt. Making it so that it doesn't conflict with the code and break. Doing it in either VBA or in the form query.

I prefer VBA because I can reuse the form for other purposes.

[Eirea]

Yes, that is what I would like to do. I need to be able to let people type in whatever they want, and have the search pull up any record with matching information. I would like to do it through VBA as I think it will work better in the long run, I just don't know how.