SQL Wildcard and Is Null statements

[Miethr]

I have a SQL that enables the user to search the form from different combo boxes to narrow down the data. I can get this to work but it only finds the fields that exactly match their input data. I want them to be able to type something in the combo box and then it pull up anything with that value in that field, whether it is 1839B or B1839 it should find both if I type in 1839

Here is my sql statements

SELECT DISTINCT [WeldingSpecification].[Spec], [WeldingSpecification].[Steel Type], [WeldingSpecification].[Group11], [WeldingSpecification].[Group143], [WeldingSpecification].[Substitute1]
FROM WeldingSpecification
WHERE ((([spec] & "") Like IIf([Forms]![frmSearchCriteriaMain]![spec] Is Null,"*", [Forms]![frmSearchCriteriaMain]![Spec] & "*")) 
And (([SteelType] & "") Like IIf([Forms]![frmSearchCriteriaMain]![SteelType] Is Null,"*",[Forms]![frmSearchCriteriaMain]![SteelType] & "*")) 
And (([Group11] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Group11] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Group11] & "*")) 
And (([Group143] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Group143] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Group143] & "*"))
 And (([Substitute1] & "")([spec] Like "*" & Forms![frmSearchCriteriaMain]!Spec & "*" &  "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*"))

Any help is appreciated, please ask if you do not understand fully

Riley

 

[KenHigg]

I'm not sure I would build the search criteria like this, but just a thought; Can you simply add another '*' to the front of the text string? (See pc of code below)

WHERE ((([spec] & "") Like IIf([Forms]![frmSearchCriteriaMain]![spec] Is Null,"*", "*" & [Forms]![frmSearchCriteriaMain]![Spec] & "*"))

 

[Miethr]

It allows me to do this for the code, but it gives me the error "The text you entered is not an item in the list" I am typing in 1839 and the item is B1839. Do I need this to be a list box or a text box to enable what I want to do?

 

[KenHigg]

Hum... My bad. I see where you may have the is null thing wrong. Maybe:

WHERE ((([spec] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![spec]) ,"*", "*" & [Forms]![frmSearchCriteriaMain]![Spec] & "*"))

???

 

[Miethr]

For some reason, before I entered that new code you gave me, my filter is only working for the very last And statement, my substitute1. I can't figure out why that is the case considering they are all the same thing just different field names.

 

[Miethr]

Ok, I figured that part out. here is the code.


SELECT DISTINCT [WeldingSpecification].[Spec], [WeldingSpecification].[Steel Type], [WeldingSpecification].[Group11], [WeldingSpecification].[Group143], [WeldingSpecification].[Substitute1]
FROM WeldingSpecification
WHERE ((([spec] & "") Like IIf([Forms]![frmSearchCriteriaMain]![spec] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Spec] & "*"))
And (([SteelType] & "") Like IIf([Forms]![frmSearchCriteriaMain]![SteelType] Is Null,"*",[Forms]![frmSearchCriteriaMain]![SteelType] & "*"))
And (([Group11] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Group11] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Group11] & "*"))
And (([Group143] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Group143] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Group143] & "*"))
And (([Substitute1] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")));


not exactly sure what had changed, but it works, I don't ask questions

 

[Miethr]

Still haven't figured out how to get it to recognize just partial entries, it is still giving me the error that the text entered is not in the entry list. While I realize this is true, I want it to search individual characters not the whole word. Any ideas on this?

 

[Miethr]

Ken, if you are still looking at this I have a strange occurance. I can get the code to work when I keep it simple For example:

SELECT DISTINCT [WeldingSpecification].[Spec], [WeldingSpecification].[SteelType], [WeldingSpecification].[Group11], [WeldingSpecification].[Group143], [WeldingSpecification].[Substitute1]
FROM WeldingSpecification
WHERE ((([spec] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![spec]),"*","*" & [Forms]![frmSearchCriteriaMain]![Spec] & "*")) And (([SteelType] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![SteelType]),"*","*" & [Forms]![frmSearchCriteriaMain]![SteelType] & "*")) And (([Group11] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Group11]),"*","*" & [Forms]![frmSearchCriteriaMain]![Group11] & "*")) And (([Group143] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Group143]),"*","*" & [Forms]![frmSearchCriteriaMain]![Group143] & "*")) And (([substitute1] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]![Substitute1] & "*")));

But I also have 10 different substitute fields that I want to search using only the Substitute 1 text box. I then add this code, I have included both of my types, my own and the one you modified for the previous code. The problem is when I try to incorporate the code above and the code below it stops working the way I want it to. The first set of code works like it used to, and the bottom set seems to work sometimes...

Or (([Substitute2] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")) 
Or (([Substitute3] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")) 
Or (([Substitute4] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")) 
Or (([Substitute5] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")) 
Or (([Substitute6] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")) 
Or (([Substitute7] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")) 
Or (([Substitute8] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")) 
Or (([Substitute9] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")) 
Or (([Substitute10] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*")));

Using this code I hope to still enable the full table to show when substitutes 2-10 don't return values, but that will appear in the Substitute text box.

Or (([Substitute2] & "")  Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*"))
Or (([Substitute3] & "")  Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*"))
Or (([Substitute4] & "")  Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*"))
Or (([Substitute5] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*"))
Or (([Substitute6] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*"))
Or (([Substitute7] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*"))
Or (([Substitute8] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*"))
Or (([Substitute9] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*"))
Or (([Substitute10] & "") Like IIf(isnull([Forms]![frmSearchCriteriaMain]![Substitute1]),"*","*" & [Forms]![frmSearchCriteriaMain]!Substitute1 & "*")));

So you see my predicament is to use both of these and get my wildcard to work, which alone it does, and the substitute full search to work, which with the old code worked.
how do they not work together?

 

[KenHigg]

Hum... The whole thing is a bit cumbersome in my humble opinion...

I think I would build the required sql string on the fly when it is needed...

 

[Miethr]

I would agree it is cumbersome, but as i am quite new at this I don't even know what you are refering to "on the fly" where would this be and what do you think it would require?

 

[KenHigg]

Give me a few minutes to do a sample db and I'll post it for you...

Look here at the sample db 'lstbxCriteria'...

 

[Miethr]

could you save it as MS 97, that's what I am using, and it won't recognize it.

 

[KenHigg]

could you save it as MS 97, that's what I am using, and it won't recognize it.

Check it now... You may want to clean up the comments as they are good for explaining what's going on but clutter it up a bit...