IF Function - Two Fields??

[nike2000]

Hi,
I have a query with a number of fields but would like to filter on the following two fields:

Location1 and Location2

The records in both of these fields will only have the following data:

London, New York, Tokyo, Hong Kong and can also be Null.

I would like to be able to show in the query only the records that have London in either of the two fields.
How can this be written in an IF Statement.

Thanks for your help
Nike

 

[PWise]

no need for a if just a -or- statment
where Location1='london' or Location2='london'

 

[nike2000]

hi pwise,
Thanks for getting back to me.
This suggestion won't work because the Location 1 field can be London but the Location 2 field may have New York.
This could also be reversed.
These fields are independant of each other.
So this would mean that where London was on one field and not the other it would not show that record.
Nike

 

[PWise]

what results do you get when try this i think does work

SELECT Location.*
FROM Location
WHERE (((Location.Location1)="london&quot) OR (((Location.Location2)="london&quot);

 

[nike2000]

Hi Pwise,
I already have the query built and is quite complex already.
It would be asier just to have the If statement, can this be done?
Many thanks
Nike

 

[PWise]

i dont know why it is so hard to add
(Location1="london" OR Location2="london&quot
to the end of the select statment

but if it is so hard try select ......., ([Location1]="london" OR [Location2]="london&quot as LocIsLondon

and in the where clause

where..... and ([Location1]="london" OR [Location2]="london&quot=true

 

[nike2000]

because the same record can have two different locations placing London in both will only return records with London in both fields. There are variations in the two Location fields that can be London New York or New York London.

the Iif statement is the best option.
can this be done using an Iif statement?

 

[Golom]

You are incorrect in that statement. Placing "London" in both (for example) will include a record if EITHER location has the value "London" because the statement is an OR statement. Your assertion would be true if the boolean operator was an AND operator.

As to using IIF ... that's not it's function.

Clauses in the WHERE part of an SQL statement should return TRUE or FALSE. An IIF could be constructed like this:

   IIF (location1='London' OR location2='London',TRUE,FALSE)

but that is exactly equivalent to

   location1='London' OR location2='London'

without the IIF since the boolean expression returns TRUE or FALSE already.