Problem with using function in criteria

[npsavidge]

Hi,

I have a form that has 3 selection criteria, date from and to and staff member. I have allowed the users to not select a staff member which would select all. The value of these selections are passed into global vars, and i have a function that gets the global vars which are used in the criteria. If the combo for staff member is left blank a value of 0 is assigned to the global glbStaffID, however when i use the following in my criteria

IIf(MI_Staff_ID()<>0,mi_staff_Id(),>0)
Where MI_Staff_ID is the function to return the value

If the staff drop down is blank, then i get no results rather than all.

Can anyone advise. Staff ID is the field that has this criteria in and is on number type

TIA
Nathan

 

[namliam]

Rather than dong things this away, try searching the forum for "Search Form" and see where that leads you.

Basicaly you should addapt the SQL to suite your current needs not try to build SQL that suite's all needs.