query design help needed

[kathy]

I have an address table that stores Street Address, P.O.
Box, City, State, Zip. Some of my clients want the PO
Box to be their default address and do not want the
street address to be printed. Is there a way to write a
query that will show only the POBox if there is something
in this field and not show the street address field for
this record. But for records with only a street address
it needs to show the street addresss.

I guess I could do this by building an extra table, but I
am wondering if I can do this in the query itself
somehow.

Thanks for any help.

Kathy

 

[John Vinson]

I have an address table that stores Street Address, P.O.
Box, City, State, Zip. Some of my clients want the PO
Box to be their default address and do not want the
street address to be printed. Is there a way to write a
query that will show only the POBox if there is something
in this field and not show the street address field for
this record. But for records with only a street address
it needs to show the street addresss.

A quick way to do this is to use the NZ() function as a calculated
field in a query:

ShowAddr: NZ([PO Box], [Street Address])

If PO Box is NULL the street address will be displayed; if it isn't,
you'll only see the PO.

 

[Ken Snell]

Use a calculated field, such as this:

SELECT IIf(Len([P.O.Box] & "") <> 0, [P.O.Box], [Street Address]) AS
PostAddress
FROM [Address Table];

 

[Wayne Morgan]

SELECT IIf(IsNull([Table1].[POBox]),[Table1].[StreetAddress],[Table1].[POBox]) AS Address
FROM Table1;

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Sure you can. If you're using JET as the db engine you can do this:

SELECT IIf([PO] Is Not Null, [PO], Address) As Adrs,
City, State, Zip
FROM Addresses
WHERE ... etc. ...

If you are using MSDE or SQL Server:

SELECT CASE [PO] Is Not Null THEN [PO] ELSE Address END As Adrs,
City, State, Zip
FROM Addresses
WHERE ... etc. ...

See the Access Help articles on the IIf() function (aka Immediate If
function); and, see the SQL Books On Line article on the CASE
statement.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

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

iQA/AwUBP7CGLYechKqOuFEgEQI+8ACgnnvoNPNJgyCjjabkyFSfm2ga3z8An3iQ
BJdRMbsh2uLMcorsNEq310WX
=Tscf
-----END PGP SIGNATURE-----

 

[Dale Fye]

MG,

Ref your SQL/MSDE code. You could also use the Coalesce function

SELECT Coalesce([PO], [Address]) Adrs, City, State, Zip
FROM Addresses
WHERE ... etc. ...

Coalesce accepts a list of parameters and will return the first
non-null value.

--
HTH

Dale Fye


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Sure you can. If you're using JET as the db engine you can do this:

SELECT IIf([PO] Is Not Null, [PO], Address) As Adrs,
City, State, Zip
FROM Addresses
WHERE ... etc. ...

If you are using MSDE or SQL Server:

SELECT CASE [PO] Is Not Null THEN [PO] ELSE Address END As Adrs,
City, State, Zip
FROM Addresses
WHERE ... etc. ...

See the Access Help articles on the IIf() function (aka Immediate If
function); and, see the SQL Books On Line article on the CASE
statement.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

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

iQA/AwUBP7CGLYechKqOuFEgEQI+8ACgnnvoNPNJgyCjjabkyFSfm2ga3z8An3iQ
BJdRMbsh2uLMcorsNEq310WX
=Tscf
-----END PGP SIGNATURE-----

 

[Guest]

Thanks, just didn't occur to me to use a calculated field
for something other than numbers -- the perils of being
new at this. Kathy

 

[Wayne Morgan]

I see Coalesce as a SQL Reserved Word, but Access flags it as an unknown
function. It may work in a pass-through query if SQL recognizes it. I even
tried clicking the option to use SQL Sever Compatible Syntax and still
received the error.