Query Problem

[Guest_imported]

What I am trying to do is when a user enters a value into a form it pulls up that information in a report. All that is working but the only problem I am having is when the user puts in multiple values...for example I have the fields "File Number", "Complaint Received", and "Incident Type." The query works fine if the user only enters one information. But if the user enters "File Number" and also "Incident Type" it pulls up all records with the file number, and also all the records with the incident type. What I would like it to do is only provide the record which matches them both. So if the file number is 0502205 and the incident type is "No License" it will only pull up the record that matches both. (What is doing now is if file number 2938223 has an incident type of No License it also pulsl up that file.) Any help. I dunno if it's my criteria or what. But for each field I put it on a seperate or line...Please help! Thanks

 

[tlbroadbent]

You need to change the WHERE criteria from OR to AND.

Example: Returns records that match col1 OR col2 criteria
Select * From Table
Where col1=26 Or col2='abc'

Example: Returns records that match col1 AND col2 criteria
Select * From Table
Where col1=26 AND col2='abc'

In the query designer grid, you would enter the criteria on the same row to creatre an AND condition. 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]

You say that your query is pulling information, it is just treating it with an OR instead of an AND. If you look at the query in design mode, go to the upper left corner and change the view from design view to SQL view (first button on the menu bar). In the WHERE clause, change the word OR to AND and then try the query again. Terry M. Hoey

 

[tlbroadbent]

Change the query as follows.

SELECT * FROM Table
WHERE (col1=[forms].[frmname].[text1]
OR [forms].[frmname].[text1] Is Null)
AND (col2=[forms].[frmname].[text2]
OR [forms].[frmname].[text2] 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.