Do not know VBA, but perhaps in VBA something like the pseudo-code below
strsql = "SELECT myTable.field1, myTable.field2, myTable.field3, myTable.field4, myTable.field5 FROM myTable WHERE 1 = 1"
if [Forms]![Form1]![Field1] Is Not Null Then
strsql = strsql & "AND Field1 Like '*' & [Forms]![Form1]![Field1] & '*' "
End if
if [Forms]![Form1]![Field2] Is Not Null Then
strsql = strsql & "AND Field2 Like '*' & [Forms]![Form1]![Field2] & '*' "
End if
Just check out if below query gives some guidelines :
Code:
SELECT
myTable.field1, myTable.field2, myTable.field3, myTable.field4, myTable.field5
FROM
myTable
WHERE
(
(
(1)=1
)
AND
(
(IIf(IsNull([Forms]![Form1]![Field1]),"1 = 1",[Field1] Like "*" & [Forms]![Form1]![Field1] & "*"))<>False
)
AND
(
(IIf(IsNull([Forms]![Form1]![Field2]),"1 = 1",[Field2] Like "*" & [Forms]![Form1]![Field2] & "*"))<>False
)
AND
(
(IIf(IsNull([Forms]![Form1]![Field3]),"1 = 1",[Field3] Like "*" & [Forms]![Form1]![Field3] & "*"))<>False
)
);
Thanks