Okay, so I'm creating this Query by Form. All of my values are text values. In the query design to identify each item to my Query I have my form built very simply. It's just the label next to the unbound text box.

My query function goes something like this for my Account Field

Forms![QBF_Form]![Text0] Or Forms![QBF_Form]![Text0] Is Null

Now I have a start date and an end date function and it goes like this.

Between [Forms]![QBF_Form]![Text4] AND [Forms]![QBF_Form]![Text19] Or [Forms]![QBF_Form]![Text4] Is Null

Now these two functions work great. I can use them in any combination I want. Empty / Empty, Filled / Filled, Empty / Filled, Filled / Empty

So here's my problem. I go to add another field, say Policy Number. I go to use the same function, except changing the text box number so Access knows which field to look at: Forms![QBF_Form]![Text6] Or Forms![QBF_Form]![Text6] Is Null and without fail, it comes back with every value. I can leave the first two fields empty, and put a completely random value into this field and it will return EVERY line item in my table. Of course I've only got about another 5 fields I'd like to make searchable and two of them I'd actually like to make number values but I'll take what help I can get. What's going on????

UPDATE: So now, I can't search with any values empty. I'm going to post my SQL in here and if someone can figure out where I'm going wrong that'd be great. I want to be able to search the values I have, leave fields empty if I don't have it, and heck, maybe (if someone is feeling really generous with their knowledge) maybe find a way to add a wild card into a search field. Please PLEASE PLEASE help...


BTW, I have been teaching myself how to use Access over the past couple weeks, and this forum has been INVALUABLE. Thank you so much everyone!

SELECT [1098-300-400Table].Account, [1098-300-400Table].Transaction_Date, [1098-300-400Table].Policy_Number, [1098-300-400Table].Desk_Code, [1098-300-400Table].Amount, [1098-300-400Table].Age, [1098-300-400Table].Notes
FROM [1098-300-400Table]
WHERE ((([1098-300-400Table].Account)=Forms!QBF_Form!Text0) And (([1098-300-400Table].Transaction_Date) Between Forms!QBF_Form!Text4 And Forms!QBF_Form!Text19) And (([1098-300-400Table].Policy_Number)=Forms!QBF_Form!Text6) And (([1098-300-400Table].Desk_Code)=Forms!QBF_Form!Text8) And (([1098-300-400Table].Amount)=Forms!QBF_Form!Text12) And (([1098-300-400Table].Age)=Forms!QBF_Form!Text14) And (([1098-300-400Table].Notes)=Forms!QBF_Form!Text16)) Or (((Forms!QBF_Form!Text0) Is Null) Or ((Forms!QBF_Form!Text4) Is Null) Or ((Forms!QBF_Form!Text6) Is Null) Or ((Forms!QBF_Form!Text8) Is Null) Or ((Forms!QBF_Form!Text12) Is Null) Or ((Forms!QBF_Form!Text14) Is Null) Or ((Forms!QBF_Form!Text16) Is Null));