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 (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

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

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.