By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,293 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,293 IT Pros & Developers. It's quick & easy.

Condition with an Else

P: n/a

I have the following scenario. I am trying to run a query where a
student's address will be pulled from a table if the student's address
has been selected as "preferred". If no "preferred" address exists,
then the address that has an address_type of "home" should be selected.

In essence I am thinking of something like this:

if SRS_PERSON_ADDRESS.PREFERRED_STATUS="1"
Select SRS_PERSON_ADDRESS.COUNTRY from SRS_PERSON_ADDRESS
else
select SRS_PERSON_ADDRESS.COUNTRY from SRS_PERSON_ADDRESS where
SRS_PERSON_ADDRESS.ADDRESS_TYPE_CODE="Home"
I have tried this, but it doesn't work:

IIf(SRS_PERSON_ADDRESS.PREFERRED_STATUS="1",SRS_PE RSON_ADDRESS.COUNTRY,select
SRS_PERSON_ADDRESS.COUNTRY from SRS_PERSON_ADDRESS where
SRS_PERSON_ADDRESS.ADDRESS_TYPE_CODE="Home")

My idea is to place such a query in the field row or criteria row of
the design view. Maybe I should just be typing this code in the SQL
View window instead? Any ideas?

I am using MS Access 2002 with linked tables.
Marcus
******

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since you select the Country for both options, just something like this:

SELECT Country
FROM SRS_PERSON_ADDRESS
WHERE PREFERRED_STATUS="1"
OR ADDRESS_TYPE_CODE="Home"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQl1k9IechKqOuFEgEQKP0ACeI3oe8MtMAwT6QNxg7SiycE gnqUwAn3AQ
N2VxYZJcUdQGRQLNhlYdvf4U
=rJX+
-----END PGP SIGNATURE-----
Marcus wrote:
I have the following scenario. I am trying to run a query where a
student's address will be pulled from a table if the student's address
has been selected as "preferred". If no "preferred" address exists,
then the address that has an address_type of "home" should be selected.

In essence I am thinking of something like this:

if SRS_PERSON_ADDRESS.PREFERRED_STATUS="1"
Select SRS_PERSON_ADDRESS.COUNTRY from SRS_PERSON_ADDRESS
else
select SRS_PERSON_ADDRESS.COUNTRY from SRS_PERSON_ADDRESS where
SRS_PERSON_ADDRESS.ADDRESS_TYPE_CODE="Home"
I have tried this, but it doesn't work:

IIf(SRS_PERSON_ADDRESS.PREFERRED_STATUS="1",SRS_PE RSON_ADDRESS.COUNTRY,select
SRS_PERSON_ADDRESS.COUNTRY from SRS_PERSON_ADDRESS where
SRS_PERSON_ADDRESS.ADDRESS_TYPE_CODE="Home")

My idea is to place such a query in the field row or criteria row of
the design view. Maybe I should just be typing this code in the SQL
View window instead? Any ideas?

Nov 13 '05 #2

P: n/a
If someone has a "preferred" address and a "Home" address, then both
will be selected in your query example.

Marcus
*********

Nov 13 '05 #3

P: n/a
Marcus wrote:
If someone has a "preferred" address and a "Home" address, then both
will be selected in your query example.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Well, that's what your criteria indicates. If the evaluation of either
of the criteria yields True then you want to select the Country. That
is a logical OR criteria (if one or the other is true then do
"something"). If you have more criteria, it'd be good to post it.

If you want the country when the preferred status = 1 AND the address
type is Home then try the following query:

SELECT Country
FROM SRS_PERSON_ADDRESS
WHERE PREFERRED_STATUS="1"
AND ADDRESS_TYPE_CODE="Home"
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQl3Wc4echKqOuFEgEQL0qACgxU3SvkKxf8rusN61L4i4Cw PhAuYAnAko
Bq4H4l7MY5/mAGkf0jQGyQlJ
=6dG1
-----END PGP SIGNATURE-----
Nov 13 '05 #4

P: n/a
Actually what I require is this:

I want to print the COUNTRY if the address PREFERRED_STATUS=1. If the
address PREFERRED STATUS=0 then I want the COUNTRY to be printed, where
the ADDRESS_TYPE_CODE="Home".

It has this sort of logic:

If ADDRESS.PREFERRED_STATUS=1 Then
select ADDRESS.COUNTRY where ADDRESS.PREFERRED_STATUS=1
Else If ADDRESS.ADDRESS_TYPE_CODE="Home" Then
select ADDRESS.COUNTRY where ADDRESS.ADDRESS_TYPE_CODE="Home"
End If

Is such a script possible in Access 2002?

Marcus
*********

Marcus
********

Nov 13 '05 #5

P: n/a
Actually what I require is this:

I want to print the COUNTRY if the address PREFERRED_STATUS=1. If the
address PREFERRED STATUS=0 then I want the COUNTRY to be printed, where

the ADDRESS_TYPE_CODE="Home".

It has this sort of logic:

If ADDRESS.PREFERRED_STATUS=1 Then
select ADDRESS.COUNTRY where ADDRESS.PREFERRED_STATUS=1
Else If ADDRESS.ADDRESS_TYPE_CODE="Hom*e" Then
select ADDRESS.COUNTRY where ADDRESS.ADDRESS_TYPE_CODE="Hom*e"
End If
Is such a script possible in Access 2002?
Marcus
*********

Nov 13 '05 #6

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, it's possible, but it is not a script (VBA code): you'd have to use
a Query. Like this:

SELECT Country
FROM Address
WHERE Preferred_Status=1
OR (Preferred_Status=0 And Address_Type_Code="Home")

Put the above SQL into a Query's SQL View & run it.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQl61boechKqOuFEgEQLNNACggh9uJUHKZtGUJ2kzZ2ZDxn hY3E4AnR9a
oguPhf821ja8kT+EP5xvOWmF
=BL1n
-----END PGP SIGNATURE-----
Marcus wrote:
Actually what I require is this:

I want to print the COUNTRY if the address PREFERRED_STATUS=1. If the
address PREFERRED STATUS=0 then I want the COUNTRY to be printed, where
the ADDRESS_TYPE_CODE="Home".

It has this sort of logic:

If ADDRESS.PREFERRED_STATUS=1 Then
select ADDRESS.COUNTRY where ADDRESS.PREFERRED_STATUS=1
Else If ADDRESS.ADDRESS_TYPE_CODE="Home" Then
select ADDRESS.COUNTRY where ADDRESS.ADDRESS_TYPE_CODE="Home"
End If

Is such a script possible in Access 2002?

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.