Help on a Search Form

[ablatnik via AccessMonster.com]

I first would like to thank everyone whoever posted answers to the numerous
questions others have asked. You are ones who make this site so important
and invaluable to users such as I.

Thank you.

I am posting my first question to this site and am looking for an answer even
if it takes my database in a new direction. I've detailed

I am designing a new database for my work on my own time to surplant an Excel
spreadsheet that is currently in use and want to go live January 2009.

The current spreadsheet is for tracking maintenance work requests, on average
15 to 20,000 entries a year with daily and mothly reports. I have all the
reports and forms done and working fine. While maintenance was working in
one of the dorm rooms, property disappeared and I was asked to find who
during a certain date range accessed this dorm room. Importing the data into
Access I was able to find the data in the tables with filters, but this
database will be menu driven.

The search form I created works on occasion. The fields I have are:

Building Name: cboBuilding_Name (Combo Box unbound)
Room Location: txtRoom_Location (Text Field unbound)
Search Button with the follow Event Procedure:
Private Sub Search_Click()
Me.Requery
End Sub

Private Sub ShowAll_Click()
Me.StartDate = Null
Me.EndDate = Null
Me.cboBuilding_Name = Null
Me.txtRoom_Location = Null
Me.Requery
End Sub

The search works fine but to narrow down this list I added StartDate and
EndDates respectively.
Now the form works sometimes correctly.

If I enter just a Building the search works fine.
If I enter just a Room Location it works fine.
If I enter Building and Room Location it works fine.
If I enter Building, Room Location and dates the results are correct.
The problem: If I enter just dates, the results return every work order in
the database as if I didn't enter any criteria at all.

Since Access reformatted the query to incorporate Is Null I am listing the
SQL statement.

SELECT Buildings.Building_Name, [Work Orders].Room_Location, [Work Orders].
[Date Opened]
FROM Buildings INNER JOIN [Work Orders] ON Buildings.Building_ID = [Work
Orders].Buildings
WHERE (((Buildings.Building_Name)=[Forms]![frmSearch Work Orders]!
[cboBuilding_Name]) AND (([Work Orders].Room_Location) Like "*" & [Forms]!
[frmSearch Work Orders]![txtRoom_Location] & "*") AND (([Work Orders].[Date
Opened]) Between [Forms]![frmSearch Work Orders]![StartDate] And [Forms]!
[frmSearch Work Orders]![EndDate])) OR (((Buildings.Building_Name)=[Forms]!
[frmSearch Work Orders]![cboBuilding_Name]) AND (([Work Orders].Room_Location)
Like "*" & [Forms]![frmSearch Work Orders]![txtRoom_Location] & "*") AND (((
[Work Orders].[Date Opened]) Between [Forms]![frmSearch Work Orders]!
[StartDate] And [Forms]![frmSearch Work Orders]![EndDate]) Is Null)) OR (((
[Work Orders].Room_Location) Like "*" & [Forms]![frmSearch Work Orders]!
[txtRoom_Location] & "*") AND (([Forms]![frmSearch Work Orders]!
[cboBuilding_Name]) Is Null)) OR (((Buildings.Building_Name)=[Forms]!
[frmSearch Work Orders]![cboBuilding_Name]) AND (([Forms]![frmSearch Work
Orders]![txtRoom_Location]) Is Null)) OR ((([Forms]![frmSearch Work Orders]!
[cboBuilding_Name]) Is Null) AND (([Forms]![frmSearch Work Orders]!
[txtRoom_Location]) Is Null));

I can read and grasp SQL statements but cannot write the statements myself.
That is why I use query formatting. Any help would be appreciated.

 

[Allen Browne]

The problem here lies in the say all the Is Null tests are grouped together.

When you mix ANDs and ORs in criteria, the bracketing is important:
(a AND b) OR c
will not give the same results as:
a AND (b OR c)

Consequently, you will need to re-write the criteria to test the control for
Null in the same phrase where you deal with it. You will end up with this
kind of thing:

WHERE
(([Forms]![frmSearch Work Orders]![cboBuilding_Name] Is Null)
OR (Buildings.Building_Name =
[Forms]![frmSearch Work Orders]![cboBuilding_Name]))
AND
(([Forms]![frmSearch Work Orders]![txtRoom_Location] Is Null)
OR ([Work Orders].Room_Location Like "*" &
[Forms]![frmSearch Work Orders]![txtRoom_Location] & "*"))
AND ...

Notice the single-brackets around ORs, but these are combined in another set
of brackets between the ANDs.

While this works, it does get difficult and clumsy to maintain, and
inefficient to execute. An alternative approach is to build the filter
string dynamically from just the controls where the user actually entered
some criteria. This does require some understanding of VBA, but here's an
example to download and follow:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I first would like to thank everyone whoever posted answers to the numerous
questions others have asked. You are ones who make this site so important
and invaluable to users such as I.

Thank you.

I am posting my first question to this site and am looking for an answer
even
if it takes my database in a new direction. I've detailed

I am designing a new database for my work on my own time to surplant an
Excel
spreadsheet that is currently in use and want to go live January 2009.

The current spreadsheet is for tracking maintenance work requests, on
average
15 to 20,000 entries a year with daily and mothly reports. I have all the
reports and forms done and working fine. While maintenance was working in
one of the dorm rooms, property disappeared and I was asked to find who
during a certain date range accessed this dorm room. Importing the data
into
Access I was able to find the data in the tables with filters, but this
database will be menu driven.

The search form I created works on occasion. The fields I have are:

Building Name: cboBuilding_Name (Combo Box unbound)
Room Location: txtRoom_Location (Text Field unbound)
Search Button with the follow Event Procedure:
Private Sub Search_Click()
Me.Requery
End Sub

Private Sub ShowAll_Click()
Me.StartDate = Null
Me.EndDate = Null
Me.cboBuilding_Name = Null
Me.txtRoom_Location = Null
Me.Requery
End Sub

The search works fine but to narrow down this list I added StartDate and
EndDates respectively.
Now the form works sometimes correctly.

If I enter just a Building the search works fine.
If I enter just a Room Location it works fine.
If I enter Building and Room Location it works fine.
If I enter Building, Room Location and dates the results are correct.
The problem: If I enter just dates, the results return every work order
in
the database as if I didn't enter any criteria at all.

Since Access reformatted the query to incorporate Is Null I am listing the
SQL statement.

SELECT Buildings.Building_Name, [Work Orders].Room_Location, [Work
Orders].
[Date Opened]
FROM Buildings INNER JOIN [Work Orders] ON Buildings.Building_ID = [Work
Orders].Buildings
WHERE (((Buildings.Building_Name)=[Forms]![frmSearch Work Orders]!
[cboBuilding_Name]) AND (([Work Orders].Room_Location) Like "*" & [Forms]!
[frmSearch Work Orders]![txtRoom_Location] & "*") AND (([Work
Orders].[Date
Opened]) Between [Forms]![frmSearch Work Orders]![StartDate] And [Forms]!
[frmSearch Work Orders]![EndDate])) OR
(((Buildings.Building_Name)=[Forms]!
[frmSearch Work Orders]![cboBuilding_Name]) AND (([Work
Orders].Room_Location)
Like "*" & [Forms]![frmSearch Work Orders]![txtRoom_Location] & "*") AND
(((
[Work Orders].[Date Opened]) Between [Forms]![frmSearch Work Orders]!
[StartDate] And [Forms]![frmSearch Work Orders]![EndDate]) Is Null)) OR
(((
[Work Orders].Room_Location) Like "*" & [Forms]![frmSearch Work Orders]!
[txtRoom_Location] & "*") AND (([Forms]![frmSearch Work Orders]!
[cboBuilding_Name]) Is Null)) OR (((Buildings.Building_Name)=[Forms]!
[frmSearch Work Orders]![cboBuilding_Name]) AND (([Forms]![frmSearch Work
Orders]![txtRoom_Location]) Is Null)) OR ((([Forms]![frmSearch Work
Orders]!
[cboBuilding_Name]) Is Null) AND (([Forms]![frmSearch Work Orders]!
[txtRoom_Location]) Is Null));

I can read and grasp SQL statements but cannot write the statements
myself.
That is why I use query formatting. Any help would be appreciated.

 

[ablatnik via AccessMonster.com]

Thanks Allen. I will try this and while I'm at it, go through your Tips for
Casual, Serious and Programmers. I just might go back to the basics after
reading this. I realized there is a lot I did not understand.

The problem here lies in the say all the Is Null tests are grouped together.

When you mix ANDs and ORs in criteria, the bracketing is important:
(a AND b) OR c
will not give the same results as:
a AND (b OR c)

Consequently, you will need to re-write the criteria to test the control for
Null in the same phrase where you deal with it. You will end up with this
kind of thing:

WHERE
(([Forms]![frmSearch Work Orders]![cboBuilding_Name] Is Null)
OR (Buildings.Building_Name =
[Forms]![frmSearch Work Orders]![cboBuilding_Name]))
AND
(([Forms]![frmSearch Work Orders]![txtRoom_Location] Is Null)
OR ([Work Orders].Room_Location Like "*" &
[Forms]![frmSearch Work Orders]![txtRoom_Location] & "*"))
AND ...

Notice the single-brackets around ORs, but these are combined in another set
of brackets between the ANDs.

While this works, it does get difficult and clumsy to maintain, and
inefficient to execute. An alternative approach is to build the filter
string dynamically from just the controls where the user actually entered
some criteria. This does require some understanding of VBA, but here's an
example to download and follow:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

I first would like to thank everyone whoever posted answers to the numerous
questions others have asked. You are ones who make this site so important

[quoted text clipped - 83 lines]

myself.
That is why I use query formatting. Any help would be appreciated.