Select value if available, otherwise use specified value

[tjb1]

This is my current table ( see attached ) and the form ( see attached ) and when I enter 00 as the type in the form, I want it to return only records that have 00. If I enter 01 or any other number that isn't 00 as the type, I want it to return only the records that have XX. 00 and 01 are not constants, they can be anything from 00 to 93. I've tried using iif statements but I can't get it to return values the way I want and I'm not very knowledgeable with SQL to do it that way.



What I am trying to do here is exclude results. There will always be a record with the type as XX so if the user enters a type that does not have a record it will return the XX record but if the user enters say type 54 and there is a record that matches everything else and has a type of 54 I want it to return that one.

[pbaldy]

I'm not sure of your goal, as there seem to be two different questions. Either way, I'd use code behind the button that tests the input, then sets the source of the subform or listbox as appropriate. If the second paragraph is the real goal, you can use DCount() to see if there are any records first.