Query with optional multiple parameters

[helent24]

I want to set up a parameter query to run from data input into a form. I'd like users to be able to search on however many fields they want to. So:

Form looks like:

Name:
Organisation:
Address1:
Address2:
Suburb:
Postcode:

And the user can enter data in as many fields as possible to narrow the search.

I've followed the below instructions to set up my form and query:

http://office.microsoft.com/en-au/access/HA011170771033.aspx?pid=CL100570041033

Which results in a query where the criteria refers back to the form, like below (I've added the wildcards so that the user can enter part of the name):

Like "*" & [Forms]![FRM: Data entry form]![Name] & "*"

SQL for whole query looks like (I've spaced it out):

SELECT [TBL Addresses].Name, [TBL Addresses].Address1, [TBL Addresses].Address2, [TBL Addresses].Suburb, [TBL Addresses].Postcode, [TBL Addresses].DateRequested, [TBL Addresses].DateSent

FROM [TBL Addresses]

WHERE ((([TBL Addresses].Name) Like "*" & [Forms]![FRM: Data entry form]![Name] & "*")
AND (([TBL Addresses].Address1) Like "*" & [Forms]![FRM: Data entry form]![Address1] & "*")
AND (([TBL Addresses].Address2) Like "*" & [Forms]![FRM: Data entry form]![Address2] & "*")
AND (([TBL Addresses].Suburb) Like "*" & [Forms]![FRM: Data entry form]![Suburb] & "*")
AND (([TBL Addresses].Postcode) Like "*" & [Forms]![FRM: Data entry form]![Postcode] & "*"));

However, using this syntax I get no results.

I've tried setting the default value in the form to a wild card, but that doesn't work.

Presumably, I need to use some ORs rather than ANDs, but short of writing a dozen lines with every combination of fields possible, I'm not sure how to do this.

Also, not sure if this is changes things, but some of the fields in the table may be blank/null.

Thanks in advance, Helen

 

[DCrake]

This is a snippet of code taken from a multi select search form in which the user can enter full or parital details into one or more fields on the form

Function BuildCriteria() As String
Dim MySql As String

If Me.TxtSurname <> "" Then
    MySql = "Surname Like '" & Me.TxtSurname & "*' AND "
    
End If
If Me.TxtForename <> "" Then
    MySql = MySql & "Forename Like '" & Me.TxtForename & "*' AND "
End If

If Me.TxtDOB <> "" Then
    MySql = MySql & "DOB=#" & Format(Me.TxtDOB, "mm/dd/yyyy") & "# AND "
End If

If Me.TxtPostcode <> "" Then
    MySql = MySql & "Address_Postcode Like '" & Me.TxtPostcode & "*' AND "
End If

If Me.TxtPracticeCode <> "" Then
    MySql = MySql & "PracticeCode Like '" & Me.TxtPracticeCode & "' AND "
End If

If Trim(MySql) = "" Then
    MySql = "False"
End If


'Drop the last and from the string

If Right(MySql, 4) = "AND " Then
    MySql = Left(MySql, Len(MySql) - 4)
End If

'next add the where to the front of the string if the user has entered something in at least one field
If MySql <> "False" Then
    MySql = "WHERE " & MySql
End If

BuildCriteria = MySql


End Function

This contructs the sql for each field only if the user has entered data into that field.

David

 

[helent24]

Thanks for your response David - I haven't had a chance to work on this, but hopefully will give it a crack early next week.