Complicated criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report form that has options to filter (or not) on several fields.
One in particular only stores the letters A, E, S or T. I have a combo box
that allows the choices A, E, S, T or 3. First, if the box is left blank, I
want all of them. Of course, if I pick a letter, I just want that filter. The
tricky part is that if I pick 3 I want all letters except S. How can I write
a criteria that would filter for these choices. Currently I have the
criteria: Like Nz([forms]![frmReports]![cboClassType],"*") which handles
every event except the 3 option.
 
Try something like

SELECT TableName.FieldName, TableName.FieldName
FROM TableName
WHERE TableName.FieldName Like nz([forms]![frmReports]![cboClassType],"*")
OR TableName.FieldName Not Like
IIf([forms]![frmReports]![cboClassType]="3","S","*")
 
Eureka! Thanks much!

Ofer Cohen said:
Try something like

SELECT TableName.FieldName, TableName.FieldName
FROM TableName
WHERE TableName.FieldName Like nz([forms]![frmReports]![cboClassType],"*")
OR TableName.FieldName Not Like
IIf([forms]![frmReports]![cboClassType]="3","S","*")

--
Good Luck
BS"D


rpurosky said:
I have a report form that has options to filter (or not) on several fields.
One in particular only stores the letters A, E, S or T. I have a combo box
that allows the choices A, E, S, T or 3. First, if the box is left blank, I
want all of them. Of course, if I pick a letter, I just want that filter. The
tricky part is that if I pick 3 I want all letters except S. How can I write
a criteria that would filter for these choices. Currently I have the
criteria: Like Nz([forms]![frmReports]![cboClassType],"*") which handles
every event except the 3 option.
Click to expand...
Click to expand...
 
Back
Top