SQL Or clause

[database]

Hi, Im trying to do a easy search function which compares a few column with different datatype. if there is any data in the column that is similar to the query, my data will be displayed. Is this possible?

below is my sql query but it seems there is some error with it.

SELECT FirstName, LastName, OID FROM Occupant WHERE OID = input OR FirstName = 'input' OR LastName = "input"

the datatype for my firstname & lastname is string. the oid is number. Im using ms access as my database.

[LifeinAsia]

The main error I see is the double quotes at the end:

OR LastName = "input"

Change to
OR LastName = 'input'

Other than that, it looks good, although you may get an error if input is a non-numeric value. What error are you getting?

[database]

the database seems to ignore my 'and' clause at the back.
it will display data which i do not wish to display.

this is the 'and' clause of my query. the and clause works fine but they will be ignored when i add in the or clause.

AND Address.AddID = PersonDetails.AddID AND PersonDetails.Status = 'active'

[LifeinAsia]

the database seems to ignore my 'and' clause at the back.

Huh, what AND clause? What you posted only has ORs in it:

WHERE OID = input OR FirstName = 'input' OR LastName = "input"

they will be ignored when i add in the or clause.

That is correct. The DB does not need to process every single clause in the where statement.

Consider this simplified example:
SELECT *
FROM MyTable
WHERE Field1=1 OR Field1=2 OR field1=3

If a row has a value of 2 for Field1 and 2 for Field2, the following will happen-
DB checks Field1=1 (false)
DB checks if Field1=2 (true)
DB doesn't need to check if Field1=3 because the statement is already true.
DB displays the row.

Now let's look at the following statement:
WHERE Field1=1 OR Field1=2 OR field1=3 AND Field2=1
DB checks Field1=1 (false)
DB checks if Field1=2 (true)
DB doesn't need to check if Field1=3 (or Field2=1) because the statement is already true.
DB displays the row.

What you need to do is add parenthesis. For the example, you probably want:
WHERE (Field1=1 OR Field1=2 OR field1=3) AND Field2=1
In this case:
DB checks Field1=1 (false)
DB checks if Field1=2 (true)
(DB doesn't need to check if Field1=3 because the parenthetical statement is already true.)
DB checks if Field2=1 (false)
DB does not display the row.

[database]

Hi,
Is it possible if i want
WHERE (Field1=1 OR Field2=1 Or Field3=1) AND (Clause)
I want
DB CHECKS Field1=1 (True)
DB CHECKS Field2=1 (True)
DB CHECKS Field3=1 (True)
AND Clause (True)

will the db check records field1=1 is true and proceed to check records field2=1 & check records field3=1
& these records will be compare with my AND Clause which compares the primary key of other table & display these records?

I know that this seems a little complicated but if this is possible, it would be great.

[database]

Hi,

I have managed to solve the error by placing my and clause at the front. Thanks alot for the help.