Display Different Field Data in Same Report Location if Null Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I write a query/expression that will search my
[tblCONTACTS].[OfficePhone] (direct phone line) for a value where If Null it
will then go to my [tblCOMPANY].[Phone] (main phone line) and display that
value in the same location on a Company Contact Report I'm designing?

Some contacts also have their home office address they prefer to receive
mail from, so I hope to use the same solution in my Mailing Labels Report.

Thank you
 
Try a text box bound to:
=Nz([OfficePhone], [Phone])

Make sure the text box does not have the same Name as a field (e.g. it can't
be called OfficePhone or Phone.)

It's a bit more involved with addresses. Since they involve multiple fields,
it's possible to have partial addresses (e.g. City field filled in, but
street name/number missing.)

If one contact can have multiple addresses/phone numbers, you may be better
off creating a related table, i.e. use a one-to-many relation where one
client has many addresses. Use an AddressType field to indicate street,
postal, etc. Use a Priority (Number) field to specify preferred address. You
can then sort a subquery to get their top priority current address, and/or
give preference to a particular type (e.g. postal form mail outs), or select
only street addresses for a directory.

If you want to know more about subqueries, here's a starting point:
http://allenbrowne.com/subquery-01.html
 
I see how the expression will default to [Phone] if [OfficePhone] is null,
but how will it hide [Phone] rather than display both fields if [OfficePhone]
also has a value?

Also, the way the mailing label queries are configured all of the address
fields must have a value or it won't be included in the output, with the
exception of [AddressLine2]. But I've not set the same parameters for the
Company Contact reports as in the phone example, so I can see your point in
regards to that output.

I have a "tblCONTACTSMULTIPLE" to handle my one-to-many Contacts who belong
to multiple Companies, and serves as a bridge table for "tblCOMPANY",
"tblCONTACTS" and "tblADDRESS"; I have a separate address table because a
Company can have multiple addresses - Onsite, Mailing and a handful have
out-of-state home office addresses. Contacts can also belong to multiple
companies (they can own/do consulting for several distinct companies NOT part
of a parent holding company).

For mailing labels and reports I have Y/N flags for [PrimaryCompany] and
[PrimaryContact] that both must be met (unless querying for employee
positions that are not primary contacts), and Y/N flags for [MailingAddress]
and [OnsiteAddress] where labels are output only to [MailingAddress], and
Company Contact reports include both.

And, is there a way to use "Trim" as in the following examples to evaluate
the mergled fields as a single value that must not be Null for a Contact's
direct home office address, and if it is Null default to the flagged
[PrimaryCompany] Address in the same way I'm trying to figure out the
[OfficePhone] v. [Phone] dilemma :

=Trim([AddressLine1] & " " & [AddressLine2])
=Trim([City] & ", " & [State] & " " & [Zip])

Thanks Allen!
 
Replies in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ngincolorado said:
I see how the expression will default to [Phone] if [OfficePhone] is null,
but how will it hide [Phone] rather than display both fields if
[OfficePhone]
also has a value?
Click to expand...

See help on Nz(), in the code window.

The 2nd argument is used only if the first is null.
Also, the way the mailing label queries are configured all of the address
fields must have a value or it won't be included in the output, with the
exception of [AddressLine2]. But I've not set the same parameters for the
Company Contact reports as in the phone example, so I can see your point
in
regards to that output.

I have a "tblCONTACTSMULTIPLE" to handle my one-to-many Contacts who
belong
to multiple Companies, and serves as a bridge table for "tblCOMPANY",
"tblCONTACTS" and "tblADDRESS"; I have a separate address table because a
Company can have multiple addresses - Onsite, Mailing and a handful have
out-of-state home office addresses. Contacts can also belong to multiple
companies (they can own/do consulting for several distinct companies NOT
part
of a parent holding company).

For mailing labels and reports I have Y/N flags for [PrimaryCompany] and
[PrimaryContact] that both must be met (unless querying for employee
positions that are not primary contacts), and Y/N flags for
[MailingAddress]
and [OnsiteAddress] where labels are output only to [MailingAddress], and
Company Contact reports include both.
Click to expand...

Okay this is a good structure.

I prefer a Number field to specify the priorirty rather that a yes/no to
indicate if it is the primary one, because it means the records are
independent of each other, e.g. if the number 1 contact/address is out of
circulation temporarily or permanently, the second one gets returned by your
function/subquery.

It usually involves a subquery to return the ID of the preferred, current
address. Alternatively write a VBA function that accepts the arguments for
the client, and optionally address type and date (if you have a full history
of addresses), and returns the string suited for the address panel
(OpenRecordset sorted as desired, and build string from first record.)
And, is there a way to use "Trim" as in the following examples to evaluate
the mergled fields as a single value that must not be Null for a Contact's
direct home office address, and if it is Null default to the flagged
[PrimaryCompany] Address in the same way I'm trying to figure out the
[OfficePhone] v. [Phone] dilemma :

=Trim([AddressLine1] & " " & [AddressLine2])
=Trim([City] & ", " & [State] & " " & [Zip])
Click to expand...

As soon as you concatenate spaces into the string, it is no longer null, so
you can't test and select from multiple addresses like that.
Thanks Allen!


Allen Browne said:
Try a text box bound to:
=Nz([OfficePhone], [Phone])

Make sure the text box does not have the same Name as a field (e.g. it
can't
be called OfficePhone or Phone.)

It's a bit more involved with addresses. Since they involve multiple
fields,
it's possible to have partial addresses (e.g. City field filled in, but
street name/number missing.)

If one contact can have multiple addresses/phone numbers, you may be
better
off creating a related table, i.e. use a one-to-many relation where one
client has many addresses. Use an AddressType field to indicate street,
postal, etc. Use a Priority (Number) field to specify preferred address.
You
can then sort a subquery to get their top priority current address,
and/or
give preference to a particular type (e.g. postal form mail outs), or
select
only street addresses for a directory.

If you want to know more about subqueries, here's a starting point:
http://allenbrowne.com/subquery-01.html
Click to expand...
Click to expand...
 
The 2nd argument is used only if the first is null.

The way too easy solution sometimes flies right by me - thanks for going
easy on me.
Okay this is a good structure.
Click to expand...

Thanks again - I'm a little defensive on the structure because it was the
only solution I thought I could use to handle the various relationships, and
the client knows just enough about database design to be dangerously critical
of me "making it more complex than it needs to be".
I prefer a Number field to specify the priorirty rather that a yes/no to
indicate if it is the primary one, because it means the records are
independent of each other, e.g. if the number 1 contact/address is out of
circulation temporarily or permanently, the second one gets returned by your
function/subquery.

It usually involves a subquery to return the ID of the preferred, current
address. Alternatively write a VBA function that accepts the arguments for
the client, and optionally address type and date (if you have a full history
of addresses), and returns the string suited for the address panel
(OpenRecordset sorted as desired, and build string from first record.)
Click to expand...

The home office "direct" Address I'm trying to incorporate is linked to the
Contact through the tblCONTACTSMULTIPLE without being assigned to a
CompanyID. But for now, all of my queries draw a Contact's Address through
the [PrimaryCompany] that is indicated, using the AddressID assigned to the
Company in the bridge table; the AddressID field is blank in the
tblCONTACTSMULTIPLE for all ContactMultipleIDs with ContactIDs, except for
those with Addresses independent of any Company they are linked to. This is
why right now the queries and reports only display the main Company
information until I can work through this linkage/priority issue to display
the "direct" Address first, but If Null then display the main Company Address.

I've thought about adding [DirectAddressLine1], [DirectCity], etc. fields to
tblCONTACTSMULTIPLE which is where I have my direct [OfficePhone],
[OfficeFax] fields, rather than in tblADDRESS linked by AddressID but my
instincts tell me I'll have problems with that. Your Priority Number field
idea is intriguing, but I have concerns about it because it means populating
a couple thousand Contacts with a priority level I honestly have no clue how
to assign (Owners, Presidents and GMs currently are typical [PrimayContact],
[PrimaryCompany] Contacts; COO's, Sales Mgrs, EVPs, Attorneys, Vendor Reps,
etc. have no priority that can be discerned for the purposes of my client,
but some of them have home "direct" addresses).

A problem I foresee is the structure of how I've built the links for
AddressID to ContactID through the [PrimaryCompany] AddressID for the 80% who
belong to just one company and have no direct address concerns, and to link
an AddressID directly to a ContactID (who may or may not be a
[PrimaryContact]) through a ContactsMultipleID involves another data entry
step to enter in the same address for the 97% of ContactIDs who will have no
direct address was a method the client rejected.

Also, it's worth noting that in tblCONTACTSMULTIPLE, the 3% I'm concerned
with are the only Contact records that have a ContactsMultipleID, ContactID
and AddressID assigned, with no CompanyID assigned. The 97% have
ContactsMultipleID, ContactID and CompanyID assigned, with no AddressID
assigned. And all Company records have ContactsMultipleID, CompanyID and
AddressID assigned, with no ContactID assigned. Can these distinctions be
used in creating a query that handles my dilemma?

Lastly, is there a streamlined way to change my Primary Contact/Company
field data type from Y/N to Numeric across all of the queries, forms and
reports I've already developed so that if I come to the conclusion that your
Priority Numeric proposal is still the most feasible and I change the field
type in the table I won't have to go to every query, form and report to make
the corresponding changes. I don't have Object Dependencies or AutoCorrect
enabled in this database - they corrupted a prior database I developed and I
had to export everything to a new database. If not, I guess making those
manual data type changes falls under the less glamorous part of database
development.

Thanks again.
 
First up, it may not be worth the effort to change your y/n field into a
numeric one. It's probably fine the way it is, now that you have everything
else in place.

The way I personally relate persons and companies is to put them all in the
one table. You can then create groupings of clients (companies, households,
committees, ...) either as formal or ad hoc groups. Again, you already have
a suitable, normalized structure in place so I'm not suggesting you change
it, but if you are interested in alternative approaches, the core idea is
explained here:
People in households and companies - modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

That example doesn't include addresses and phone numbers, but would use a
related table of addresses with a ClientID foreign key field (so one client
can have many addresses.) Since "client" could be person or company, a
company can have multiple addresses, a person can have multiple addresses in
their own right, and a person can have an address that applies to specific
situations (e.g. you can use the address of the company when addressing that
person in their role at that company, but use their personal address or the
address of another company where the person works in other contexts.)

It's very flexible, and quite a simple structure. The only problem I have is
when somebody says they want a flat-file printout of the data, showing how
everyone relates to everyone/everything else. This web of human
relationships does not lend itself to a simple print out (even with just a
few thousand clients), because the levels of connectivity are unlimited and
potentially recursive.

If the Priority (Number) field intrigues you, the way I program that is that
the first address entered for a client defaults to 1, and the next to 2, and
so on (assigned using DMax() in Form_BeforeInsert). Most often, the user
enters the main address first, so it all works without them having to think
much. There is no requirement for the field to be unique, i.e. a client can
have two addresses both of priority 1 if they don't care. (Makes it simpler
to re-prioritize.) Documentation says the software is allowed to serve up
either address when there are equal priorities; in practice, they get the
lower autonumber value (so usually the one entered first.) Similarly there's
no requirement for the priority numbers to be sequential, i.e. it's purely
for sorting purposes.

The address table we use also has a StartDate field (Date/Time, required,
defaults to today) indicating when this address becomes operational, and an
EndDate field (Date/Time, Null if the address is current) indicating when to
stop using this address. This means the user can maintain a history of
addresses, and the function/subquery can determine what would have been the
client's default address at any time. (We generally let them delete
addresses too, unless the client has special business rules requiring the
history to be complete.)

Not sure if that answers the question you were asking.
Hopefully it stimulates some creative thinking.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ngincolorado said:
The 2nd argument is used only if the first is null.
Click to expand...

The way too easy solution sometimes flies right by me - thanks for going
easy on me.
Okay this is a good structure.
Click to expand...

Thanks again - I'm a little defensive on the structure because it was the
only solution I thought I could use to handle the various relationships,
and
the client knows just enough about database design to be dangerously
critical
of me "making it more complex than it needs to be".
I prefer a Number field to specify the priorirty rather that a yes/no to
indicate if it is the primary one, because it means the records are
independent of each other, e.g. if the number 1 contact/address is out of
circulation temporarily or permanently, the second one gets returned by
your
function/subquery.

It usually involves a subquery to return the ID of the preferred, current
address. Alternatively write a VBA function that accepts the arguments
for
the client, and optionally address type and date (if you have a full
history
of addresses), and returns the string suited for the address panel
(OpenRecordset sorted as desired, and build string from first record.)
Click to expand...

The home office "direct" Address I'm trying to incorporate is linked to
the
Contact through the tblCONTACTSMULTIPLE without being assigned to a
CompanyID. But for now, all of my queries draw a Contact's Address
through
the [PrimaryCompany] that is indicated, using the AddressID assigned to
the
Company in the bridge table; the AddressID field is blank in the
tblCONTACTSMULTIPLE for all ContactMultipleIDs with ContactIDs, except for
those with Addresses independent of any Company they are linked to. This
is
why right now the queries and reports only display the main Company
information until I can work through this linkage/priority issue to
display
the "direct" Address first, but If Null then display the main Company
Address.

I've thought about adding [DirectAddressLine1], [DirectCity], etc. fields
to
tblCONTACTSMULTIPLE which is where I have my direct [OfficePhone],
[OfficeFax] fields, rather than in tblADDRESS linked by AddressID but my
instincts tell me I'll have problems with that. Your Priority Number
field
idea is intriguing, but I have concerns about it because it means
populating
a couple thousand Contacts with a priority level I honestly have no clue
how
to assign (Owners, Presidents and GMs currently are typical
[PrimayContact],
[PrimaryCompany] Contacts; COO's, Sales Mgrs, EVPs, Attorneys, Vendor
Reps,
etc. have no priority that can be discerned for the purposes of my client,
but some of them have home "direct" addresses).

A problem I foresee is the structure of how I've built the links for
AddressID to ContactID through the [PrimaryCompany] AddressID for the 80%
who
belong to just one company and have no direct address concerns, and to
link
an AddressID directly to a ContactID (who may or may not be a
[PrimaryContact]) through a ContactsMultipleID involves another data entry
step to enter in the same address for the 97% of ContactIDs who will have
no
direct address was a method the client rejected.

Also, it's worth noting that in tblCONTACTSMULTIPLE, the 3% I'm concerned
with are the only Contact records that have a ContactsMultipleID,
ContactID
and AddressID assigned, with no CompanyID assigned. The 97% have
ContactsMultipleID, ContactID and CompanyID assigned, with no AddressID
assigned. And all Company records have ContactsMultipleID, CompanyID and
AddressID assigned, with no ContactID assigned. Can these distinctions be
used in creating a query that handles my dilemma?

Lastly, is there a streamlined way to change my Primary Contact/Company
field data type from Y/N to Numeric across all of the queries, forms and
reports I've already developed so that if I come to the conclusion that
your
Priority Numeric proposal is still the most feasible and I change the
field
type in the table I won't have to go to every query, form and report to
make
the corresponding changes. I don't have Object Dependencies or
AutoCorrect
enabled in this database - they corrupted a prior database I developed and
I
had to export everything to a new database. If not, I guess making those
manual data type changes falls under the less glamorous part of database
development.

Thanks again.
Click to expand...
 
Allen Browne said:
First up, it may not be worth the effort to change your y/n field into a
numeric one. It's probably fine the way it is, now that you have everything
else in place.
Click to expand...

If my project structure as is can handle what I'm trying to accomplish I'll
save the link for future projects.

I'd like to develop the expression or query that can work through my
challenge to display the "direct" Address first, but If Null then display the
main Company Address. In tblCONTACTSMULTIPLE, the 3% I'm concerned with are
the only Contact records that have a ContactsMultipleID, ContactID and
AddressID assigned, with no CompanyID assigned. The 97% have
ContactsMultipleID, ContactID and CompanyID assigned, with no AddressID
assigned. And all Company records have ContactsMultipleID, CompanyID and
AddressID assigned, with no ContactID assigned. Can these distinctions be
used in creating a query that handles my dilemma?

Or, I've thought about adding [DirectAddressLine1], [DirectCity], etc.
fields to
tblCONTACTSMULTIPLE which is where I have my direct [OfficePhone],
[OfficeFax] fields, rather than in tblADDRESS linked by AddressID but my
instincts tell me I'll have problems with that.

Most of this restates my previous post, so sorry for the redundancy, just
choosing to steer clear of a big rewrite if I can since it sounds like you're
saying my current structure can handle my challenge.

Thanks again.
 
Okay, it would probably be better as a function to retrieve that.

Can't write if for you, but it would start along these lines:
Function ClientAddress(varContactID As Variant) As Variant
Dim rs As DAO.Recorset
Dim strSql As String

strSql = "SELECT ClientMultipleID " & vbCrLf & _
"FROM tblContactsMultiple AS DA " & vbCrLf & _
"WHERE (DA.ContactID = " & varContactID & _
"(DA.PrimaryCompany <> False) " & _
"AND (DA.MailingAddress <> False)"

Set rs = dbEngine(0)(0).OpenRecorset(strSql)
...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ngincolorado said:
Allen Browne said:
First up, it may not be worth the effort to change your y/n field into a
numeric one. It's probably fine the way it is, now that you have
everything
else in place.
Click to expand...

If my project structure as is can handle what I'm trying to accomplish
I'll
save the link for future projects.

I'd like to develop the expression or query that can work through my
challenge to display the "direct" Address first, but If Null then display
the
main Company Address. In tblCONTACTSMULTIPLE, the 3% I'm concerned with
are
the only Contact records that have a ContactsMultipleID, ContactID and
AddressID assigned, with no CompanyID assigned. The 97% have
ContactsMultipleID, ContactID and CompanyID assigned, with no AddressID
assigned. And all Company records have ContactsMultipleID, CompanyID and
AddressID assigned, with no ContactID assigned. Can these distinctions be
used in creating a query that handles my dilemma?

Or, I've thought about adding [DirectAddressLine1], [DirectCity], etc.
fields to
tblCONTACTSMULTIPLE which is where I have my direct [OfficePhone],
[OfficeFax] fields, rather than in tblADDRESS linked by AddressID but my
instincts tell me I'll have problems with that.

Most of this restates my previous post, so sorry for the redundancy, just
choosing to steer clear of a big rewrite if I can since it sounds like
you're
saying my current structure can handle my challenge.

Thanks again.
Click to expand...
 
Back
Top