If Then in a Query

  • Thread starter Thread starter Joe F. Roush III
  • Start date Start date
J

Joe F. Roush III

I am writing a mailing lablel Database in which some people provide home
addresses and others provide business addresses. In the contact table I have
Firstname, lastname, business address, home address, and a yes no field. I
want to create a query in which it searches for everyone with the lasname
"smith" and if the yes no field has a 1 select the home address and if the
yes/no has a 0 select the business address. Something like:

SELECT LastName, Firstname, (If y/n = 1 then HomeAddress) (if y/n = 0 then
BusinessAddress)
FROM tblContact
WHERE LastName = 'Smith'

If this doesn't make sense let me know and I will try to explain a little
better
 
SELECT LastName, Firstname, IIf([y/n] = 1, [HomeAddress], [BusinessAddress])
AS TheAddress
FROM tblContact
WHERE LastName = 'Smith'
 
You were pretty close to the right syntax. It is
IIf (condition, value if true, value if false)

Remember that in Access, Yes/No fields are -1 if Yes, 0 if No.
You can refer to the results as Yes or No or -1 or 0,
either way is OK.

Select IIF ([LastName]=Yes,[BusinessAddress],[HomeAddress]) As ActiveAddress
From tblContact
Whee LastName='Smith'

This will give Bus. Addr if Yes, otherwise gives home address.
If you want to deliberately check for "No" also, you can nest a
2nd IIf clause inside the first's (else) section:
Select IIf ([LastName]=Yes,[BusinessAddress],
IIf([LastName]=No,[HomeAddress])) As ActiveAddress
From ... etc.

I'm pretty sure you can omit the (else) component if it isn't needed.
If the code complains without it, you can always specify
a result of Null or 0 or an empty string.

Any results you specify have to have matching data types.

Bruce Pick
 
(I'm repoasting this reply, as it is now 9 hours
since I posted it and I still don't see it on the
newsgroup for some reason)

You were pretty close to the right syntax. It is
IIf (condition, value if true, value if false)

Remember that in Access, Yes/No fields are -1 if Yes, 0 if No.
You can refer to the results as Yes or No or -1 or 0,
either way is OK.

Select IIF ([LastName]=Yes,[BusinessAddress],[HomeAddress])
As ActiveAddress
From tblContact
Whee LastName='Smith'

This will give Bus. Addr if Yes, otherwise gives home address.
If you want to deliberately check for "No" also, you can nest a
2nd IIf clause inside the first's (else) section:
Select IIf ([LastName]=Yes,[BusinessAddress],
IIf([LastName]=No,[HomeAddress])) As ActiveAddress
From ... etc.

I'm pretty sure you can omit the (else) component if it isn't needed.
If the code complains without it, you can always specify
a result of Null or 0 or an empty string.

Any results you specify have to have matching data types.

Bruce Pick
 
Back
Top