Multi Search Form (1 Viewer)

[DatabaseTash]

I have this code for a Multi Search Form it is working fine, but needs a little tweaking. The form has 4 fields (Parish, Portion, section & Suburban_Section). The user will always input 2 of the 4 fields (the Parish and then one of the remaining). With the code how it is, say for example if I search for the Parish of Kalkie and the Portion 220. I get all the Parishes in the database with the Portion of 220. How can I alter this code so that it will only return the Parish I have input?

PARAMETERS [Forms]![frmMultiSearchTool]![Parish] Short, [Forms]![frmMultiSearchTool]![Portion] Short, [Forms]![frmMultiSearchTool]![Section] Short, [Forms]![frmMultiSearchTool]![Suburban_Section] Short;
SELECT tblCadastralPlansRegister.*
FROM tblCadastralPlansRegister
WHERE (((IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)))<>False) AND (([Forms]![frmMultiSearchTool]![Portion]) Is Null) AND (([Forms]![frmMultiSearchTool]![Section]) Is Null) AND (([Forms]![frmMultiSearchTool]![Suburban_Section]) Is Null)) OR (((IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)))<>False) AND ((tblCadastralPlansRegister.Portion) Like "*" & [Forms]![frmMultiSearchTool]![Portion] & "*") AND (([Forms]![frmMultiSearchTool]![Section]) Is Null) AND (([Forms]![frmMultiSearchTool]![Suburban_Section]) Is Null)) OR (((IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)))<>False) AND (([Forms]![frmMultiSearchTool]![Portion]) Is Null) AND (([Forms]![frmMultiSearchTool]![Suburban_Section]) Is Null) AND ((tblCadastralPlansRegister.Section) Like "*" & [Forms]![frmMultiSearchTool]![Section] & "*")) OR (((IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)))<>False) AND ((tblCadastralPlansRegister.Portion) Like "*" & [Forms]![frmMultiSearchTool]![Portion] & "*") AND (([Forms]![frmMultiSearchTool]![Suburban_Section]) Is Null) AND ((tblCadastralPlansRegister.Section) Like "*" & [Forms]![frmMultiSearchTool]![Section] & "*")) OR (((IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)))<>False) AND (([Forms]![frmMultiSearchTool]![Portion]) Is Null) AND (([Forms]![frmMultiSearchTool]![Section]) Is Null) AND ((tblCadastralPlansRegister.Suburban_Section) Like "*" & [Forms]![frmMultiSearchTool]![Suburban_Section] & "*")) OR (((IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)))<>False) AND ((tblCadastralPlansRegister.Portion) Like "*" & [Forms]![frmMultiSearchTool]![Portion] & "*") AND (([Forms]![frmMultiSearchTool]![Section]) Is Null) AND ((tblCadastralPlansRegister.Suburban_Section) Like "*" & [Forms]![frmMultiSearchTool]![Suburban_Section] & "*")) OR (((IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)))<>False) AND (([Forms]![frmMultiSearchTool]![Portion]) Is Null) AND ((tblCadastralPlansRegister.Section) Like "*" & [Forms]![frmMultiSearchTool]![Section] & "*") AND ((tblCadastralPlansRegister.Suburban_Section) Like "*" & [Forms]![frmMultiSearchTool]![Suburban_Section] & "*")) OR (((IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)))<>False) AND ((tblCadastralPlansRegister.Portion) Like "*" & [Forms]![frmMultiSearchTool]![Portion] & "*") AND ((tblCadastralPlansRegister.Section) Like "*" & [Forms]![frmMultiSearchTool]![Section] & "*") AND ((tblCadastralPlansRegister.Suburban_Section) Like "*" & [Forms]![frmMultiSearchTool]![Suburban_Section] & "*"))
ORDER BY IIf([Forms]![frmMultiSearchTool]![Parish]=-1,([tblCadastralPlansRegister].[Parish]),IIf([Forms]![frmMultiSearchTool]![Parish]=0,(Not [tblCadastralPlansRegister].[Parish]),True)), tblCadastralPlansRegister.Portion, [Forms]![frmMultiSearchTool]![Portion], [Forms]![frmMultiSearchTool]![Section], [Forms]![frmMultiSearchTool]![Suburban_Section], tblCadastralPlansRegister.Section, tblCadastralPlansRegister.Suburban_Section;

 

[June7]

I would not use a dynamic parameterized query. I would use VBA to build filter criteria. Review http://allenbrowne.com/ser-62.html