Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-11-06 : 08:20:43
|
| Carlos writes "I'm using Access 97 and I am trying to give the user the option to search on up to four columns at a time. The result would then be rows which matched all input values. The SQL shown below works as long as the user inputs a value for each of the [Forms]![AdvancedPCSearch]![src4advsrch*] search text boxes. The problem is that (should they want to) they need be able to leave one of the search text boxes on the form blank. I've tried joins, considered union commands, no joy. The only way I can conceive of doing this at the moment is using procedures and checking to see if a search box is null before including it in a search, then doing some sort of join at the end. This seems pretty cumbersome to me.SELECT *FROM PCWHERE (((PC.Site)=[Forms]![AdvancedPCSearch]![src4advsrchsite])AND((PC.VPN)=[Forms]![AdvancedPCSearch]![src4advsrchVPN])AND((PC.CloneVersion)=[Forms]![AdvancedPCSearch]![src4advsrchClone])AND ((PC.YearOfPurchase) Between [Forms]![AdvancedPCSearch]![src4advsrchYearRngStrt] And [Forms]![AdvancedPCSearch]![src4advsrchYearRngEnd]));" |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-06 : 12:43:21
|
| It's easy:WHEREiif(isnull([Forms]![AdvancedPCSearch]![src4advsrchsite]), True,[Forms]![AdvancedPCSearch]![src4advsrchsite] = PC.Site)AND... {and so on} ... |
 |
|
|
|
|
|
Searching multiple columns but ignoring null search strings.