SQL criteria doesn't like my "Like" condition

[Zygor]

I'm trying to get the following code to run. If the cbo box has an entry, filter it on that. Otherwise use no filter. But the no filter (nothing selected in the cbo box) isn't working. Anyone see what I'm doing wrong?

Set rstSurg = db.OpenRecordset("SELECT tblMyTable.*, tblMyTable.Dept
FROM tblMyTable
WHERE (((tblMyTable.Dept)=IIf([forms]![frmMyForm]![cboDept]<>'',[forms]![frmMyForm]![cboDept],(tblMyTable.Dept) Like '*')));")

 

[Golom]

If [forms]![frmMyForm]![cboDept] is = '' then the WHERE clause resolves to

WHERE tblMyTable.Dept=tblMyTable.Dept Like '*'

and that's prety much guaranteed to never happen because the LIKE clause returns a Boolean and your field is (presumably) a text string.

Maybe you need

WHERE tblMyTable.Dept LIKE IIf([forms]![frmMyForm]![cboDept]<>'',
                               [forms]![frmMyForm]![cboDept],'*')

 

[PHV]

One way:

Set rstSurg = db.OpenRecordset("SELECT * FROM tblMyTable WHERE Dept Like '" & Nz(Forms!frmMyForm!cboDept, "*") & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886