CODEWITHSUNDEEP
sqlms-accesssearchparameters
maneesha
maneesha

Reputation: 685

Using Search Parameters in Access 2010

I am working in Access 2010.

I am building a database that lets my organization catalog blank walls in the city --- including their location, what way they face, whether the adjacent lot is vacant or occupied, etc. This information is stored in a table called WallsMaster.

This is my query.

SELECT WallsMaster.StreetAddress, WallsMaster.Zip, WallsMaster.Direction, WallsMaster.LotInfo

FROM WallsMaster

WHERE 

(
((WallsMaster.Zip) Like "*" & [Forms]![Search_Form]![Zip_Search] 
Or 
(WallsMaster.Zip)="" 
Or 
(WallsMaster.Zip) Is Null)

AND 

((WallsMaster.Direction) Like "*" & [Forms]![Search_Form]![Direction_Search] 
Or 
(WallsMaster.Direction)="" 
Or 
(WallsMaster.Direction) Is Null)

AND 

((WallsMaster.LotInfo) Like "*" & [Forms]![Search_Form]![LotInfo_Search] 
Or 
(WallsMaster.LotInfo)="" 
Or 
(WallsMaster.LotInfo) Is Null)
)
;

When I enter a value for Zip but leave the other parameters blank, what I want is all Direction & LotInfo values (including nulls/blanks) for that zip only (not including nulls/blank Zip values) . When I enter Direction but leave the others blank, I want all Zip and LotInfo values (including nulls/blanks) for that Direction (not including null/blank Direction values) , and so on for LotInfo.

What I'm getting when I do this includes nulls/blanks for the parameters I've left blank but also includes nulls/blanks for the field I've indicated a search parameter for.

ie, if I search for Zip 19130 and leave Direction & LotInfo blank, I get this:

Address,Zip,Direction,LotInfo
123 Main St, 19130, South, Vacant
456 Elm St, , North, Vacant
789 Fake St, 19130, , ,
321 K St, 19130, East, Occupied

What I want is this:

Address, Zip, Direction, LotInfo
123 Main St, 19130, South, Vacant  
789 Fake St, 19130, , 
321 K St,19130, East, Occupied
  1. How can I re-write my SQL script to give me the results I want?
  2. For the sake of this example, I'm just sharing a few fields. My real WallMaster table has about 30 fields and hundreds of records and I want my search form to search by any/all of these fields at a time. Is this the most efficient way to write this search?

Upvotes: 1

Views: 714

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52675

You're testing the wrong thing for being equal to "" or Null. You want to test the inputs for that.

SELECT WallsMaster.StreetAddress, WallsMaster.Zip, WallsMaster.Direction, WallsMaster.LotInfo
FROM WallsMaster

WHERE 

(

(WallsMaster.Zip) Like "*" & [Forms]![Search_Form]![Zip_Search] 

Or 

([Forms]![Search_Form]![Zip_Search] ="" 

Or 

[Forms]![Search_Form]![Zip_Search] Is Null))

AND 

((WallsMaster.Direction) Like "*" & [Forms]![Search_Form]![Direction_Search]

Or 

([Forms]![Search_Form]![Direction_Search]="" 

Or 

[Forms]![Search_Form]![Direction_Search] Is Null)

AND 

((WallsMaster.LotInfo) Like "*" & [Forms]![Search_Form]![LotInfo_Search]

Or 

([Forms]![Search_Form]![LotInfo_Search]="" 

Or 

[Forms]![Search_Form]![LotInfo_Search] Is Null)

)

Upvotes: 1

Benjamin Gale
Benjamin Gale

Reputation: 13177

Regarding your second question, I personally wouldn't bother writing a query to filter every conceivable combination of values that you envisage.

Here is a great example from Allen Browne showing how to implement a search form. I have used this in the past and it works brilliantly, however this was back in the day when Access 2003 was the only available option.

This solution soon becomes unwieldy if you need to add more fields to your table or the business rules change slightly.

Instead, just load your data into a datasheet and utilize the automatic filtering that Microsoft access 2010 offers you out of the box. You will be able to filter all columns in any combination you wish and this will even work if you add new fields to your table (providing the forms data source is set up correctly)

Upvotes: 1

iDevlop
iDevlop

Reputation: 25272

What about this path ? 

WHERE WallsMaster.Zip Like "*" & NZ([Forms]![Search_Form]![Zip_Search],"")  
AND (WallsMaster.Direction) Like "*" & NZ([Forms]![Search_Form]![Direction_Search],"")
AND (WallsMaster.LotInfo) Like "*" & Nz([Forms]![Search_Form]![LotInfo_Search],"")

Upvotes: 0

Related Questions