Hi Art
You can do this a little more simply using the Nz() function.
Instead of:
IIf(IsNull([Street]),([POBoxNo]),[Street]) AS Address
use this:
Nz([POBoxNo],[Street]) AS Address
All the best
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, 6 August 2014 11:46
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Address field logic in a report
I actually figured this one out myself.
SELECT tblApplicantAddresses.ApplicantID, tblApplicantAddresses.CurrentAddressFlag, tblApplicantAddresses.POBoxNo, tblApplicantAddresses.Street, tlkpCity.CityName, tlkpState.StateName, tblApplicantAddresses.ZipCode, IIf(IsNull([Street]),([POBoxNo]),[Street]) AS Address, [CityName] & ", " & [StateName] & " " & [ZipCode] AS CSZ
FROM (tblApplicantAddresses LEFT JOIN tlkpCity ON tblApplicantAddresses.CityID = tlkpCity.CityID) LEFT JOIN tlkpState ON tblApplicantAddresses.StateID = tlkpState.StateID
WHERE (((tblApplicantAddresses.CurrentAddressFlag)=True));
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Only those who will risk going too far can possibly find out how far one can go."
On Monday, August 4, 2014 3:24 PM, "dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
I am creating an Address listing report and what I am trying to do is as follows:
I have a POBox field and a Street field. I would like to be able in my report to if PO Box field is not empty then display it or if the Street field is not empty display it and if they are both not empty then display the PO Box field by default.
This is the query that I am working with:
SELECT tblApplicantAddresses.ApplicantID, tblApplicantAddresses.CurrentAddressFlag, tblApplicantAddresses.POBoxNo, tblApplicantAddresses.Street, tlkpCity.CityName, tlkpState.StateName, tblApplicantAddresses.ZipCode
FROM (tblApplicantAddresses LEFT JOIN tlkpCity ON tblApplicantAddresses.CityID = tlkpCity.CityID) LEFT JOIN tlkpState ON tblApplicantAddresses.StateID = tlkpState.StateID
WHERE (((tblApplicantAddresses.CurrentAddressFlag)=True));
Any ideas would be great.
Thank you,
Art Lorenzini
Sioux Falls, SD
Posted by: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar