Search form - multiple criteria

[CBrighton]

OK, I may need a fresh set of eyes here (or a fresh brain).

I have a simple search form to search for a member of staff in my database. It has 3 controls, employee number, forename and surname. These 3 controls directly relate to 3 fields in the table (all text fields).

Once you have populated the data you have you click search and a subform becomes visible which is fed by a simple query with the 3 criteria seperated by OR's. The subform is a cont form and has a control button which would open another form filtered by the record you clicked the button on.

So the 3 controls on the main form cause the subform to display any data where any of the 3 fields match.

What I am looking to do is change it to search for all 3, unless the controls are null in which case skip it. I have tried using Iif statements in the criteria to tell it to only have criteria if it is not null but it just doesn't seem to work.

Anyone have any suggestions?

 

[MStef]

I suggest to do this via FILTER BY FORM, CLEAR GRID, APPLY FILTER and REMOVE FILTER icons.

 

[CBrighton]

At the risk of sounding stupid, I have no idea what you are talking about!

I've used the Filter By Form and Apply Filter buttons directly on a table before when looking at (but doing nothing to) data, however I don't know how I would use these features (or the other two I haven't heard of) with the query I have checking the 3 fields on the main form.

BTW I should have mentioned in the OP, I am using Access 2003.

 

[vbaInet]

Or...

I would normally do this sort of thing by building my sql in code. If there will never be a need to search for Null values within any of the fields then you can use something like

[Field] LIKE Nz([Forms]![FormName]![ControlName], "*")

OR

[Field] LIKE IIF(Len([Forms]![FormName]![ControlName] & "") <> 0, [Forms]![FormName]![ControlName], "*")

OR here's an interesting concept from BrianWarnock:

([Field]=[Forms]![FormName]![ControlName]) OR ([Forms]![FormName]![ControlName] Is Null)

 

[CBrighton]

vbaInet - Tried the first suggestion, worked perfectly.

Cheers.

 

[vbaInet]

No problemo!