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?
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?