Query Search with Blank Fields

[bomber1]

I don't know if this is going to make any sense but I have been troubled by this several times. I am trying to set up a query that will filter out different files listed in a table. Each file has a 5 digit code. On a form that was created you have 5 different boxes where you enter each digit. I need a query that will not only send back files that match a certain 5 digit combination, but I also need it to send back files that have certain digits say I only enter the first and third digit and leave the rest blank. I want the query to send back the files that have those first and third digits. I can't seem to find a combination with and/or critrias that will do this. Either I have to enter all five and get back one file or if the query is set up with OR I get back every file that has any digit in any position. I hope that makes sense. Thanks.

 

[tlbroadbent]

I'm not sure if I fuly understand your requiremnt but this might do what you want. If not please post your current query for comparison.

Select * From Table
Where (Mid(code,1,1)=textbox1 or textbox1 is null)
And (Mid(code,2,1)=textbox2 or textbox2 is null)
And (Mid(code,3,1)=textbox3 or textbox3 is null)
And (Mid(code,4,1)=textbox4 or textbox4 is null)
And (Mid(code,5,1)=textbox5 or textbox5 is null)
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[THoey]

In agreement with Terry that I don't think I completely understand, but you may also want to look into the LIKE clause and the use of the different wildcards. I believe that a "?" will accept any character in on position, so you could search for '1??3??' to find strings that match.

Hope that helps... Terry M. Hoey