Omit blank information

[bhrosey via AccessMonster.com]

I am putting together a phone directory based on a membership list. I have
it set up so that it looks like this:
Smith, Doug & Mary
Home (123) 555-1212
Doug's Cell (123) 555-4545
Mary's Cell (123) 555-9191

Where the home phone is a field on my query as is the cell numbers.

Home HomePhone
=[firstname] & "'s " & "Cell" & " " CellPhone
=[spousename] & "'s " & "Cell" SpCell

My problem is this...I'm not sure how to make it so that if there is no cell
number, I don't want to show anything on the report. Like this;

Smith, Doug & Mary
Home (123) 555-1212
Doug's Cell (123) 555-4545

 

[Steve]

How about something like this:

Home HomePhone
=[firstname] & " " & IIF(IsNull(CellPhone), "No Cell Phone", "'s " & "Cell"
& " " CellPhone)
=[spousename] & " " & IIF(IsNull(SPCell), "No Cell Phone", "'s " & "Cell" &
" " SPCell)


Steve
(e-mail address removed)

 

[John... Visio MVP]

Again you show your inability to understand a poster's request.
The poster asked for "if there is no cell number, I don't want to show
anything on the report."
You give them "No Cell Phone"

Please stop pretending to be a expert and groveling for work.

John... Visio MVP

How about something like this:

Home HomePhone
=[firstname] & " " & IIF(IsNull(CellPhone), "No Cell Phone", "'s " &
"Cell" & " " CellPhone)
=[spousename] & " " & IIF(IsNull(SPCell), "No Cell Phone", "'s " & "Cell"
& " " SPCell)

Steve

I am putting together a phone directory based on a membership list. I
have
it set up so that it looks like this:
Smith, Doug & Mary
Home (123) 555-1212
Doug's Cell (123) 555-4545
Mary's Cell (123) 555-9191

Where the home phone is a field on my query as is the cell numbers.

Home HomePhone
=[firstname] & "'s " & "Cell" & " " CellPhone
=[spousename] & "'s " & "Cell" SpCell

My problem is this...I'm not sure how to make it so that if there is no
cell
number, I don't want to show anything on the report. Like this;

Smith, Doug & Mary
Home (123) 555-1212
Doug's Cell (123) 555-4545

 

[bhrosey via AccessMonster.com]

Steve thanks for the reply. I guess I didn't explain myself well enough.
While it looks like that would work fine for someone that doesn't have a cell
phone, I'm wanting to not have anything show up when there is no spouse.
Here is what I have.

=[firstname] & "'s " & "Cell" & " "
=IIf([Spousename]=Null,"",[Spousename] & "'s" & " Cell")
but what I get is this;

Bob's Cell (123) 555-1212
's Cell

It's leaving out the spouse name because it's null, but I'm still getting the
rest of the text, I just want it to be blank when there is no spouse

How about something like this:

Home HomePhone
=[firstname] & " " & IIF(IsNull(CellPhone), "No Cell Phone", "'s " & "Cell"
& " " CellPhone)
=[spousename] & " " & IIF(IsNull(SPCell), "No Cell Phone", "'s " & "Cell" &
" " SPCell)

Steve
(e-mail address removed)

I am putting together a phone directory based on a membership list. I have
it set up so that it looks like this:

[quoted text clipped - 16 lines]

Home (123) 555-1212
Doug's Cell (123) 555-4545

 

[bhrosey via AccessMonster.com]

John, please see my reply to Steve below.......

Steve thanks for the reply. I guess I didn't explain myself well enough.
While it looks like that would work fine for someone that doesn't have a cell
phone, I'm wanting to not have anything show up when there is no spouse.
Here is what I have.
=[firstname] & "'s " & "Cell" & " "
=IIf([Spousename]=Null,"",[Spousename] & "'s" & " Cell")
but what I get is this;
Bob's Cell (123) 555-1212
's Cell
It's leaving out the spouse name because it's null, but I'm still getting the
rest of the text, I just want it to be blank when there is no spouse

Again you show your inability to understand a poster's request.
The poster asked for "if there is no cell number, I don't want to show
anything on the report."
You give them "No Cell Phone"

Please stop pretending to be a expert and groveling for work.

John... Visio MVP

How about something like this:

[quoted text clipped - 27 lines]

 

[John... Visio MVP]

You were quite clear, steve is a known troll who provides half answers and
then when the poster appears confused, offers to help for a reasonable fee.

What you need to be doing is checking the existence of SPCell. It is
possible that there is a spousename, but it is also possible the spouse does
NOT have a cell phone. So checking just spousename could fail. If there is a
SPCell, then there is a good chance that spousename also exists.

John... Visio MVP

Steve thanks for the reply. I guess I didn't explain myself well enough.
While it looks like that would work fine for someone that doesn't have a
cell
phone, I'm wanting to not have anything show up when there is no spouse.
Here is what I have.

=[firstname] & "'s " & "Cell" & " "
=IIf([Spousename]=Null,"",[Spousename] & "'s" & " Cell")
but what I get is this;

Bob's Cell (123) 555-1212
's Cell

It's leaving out the spouse name because it's null, but I'm still getting
the
rest of the text, I just want it to be blank when there is no spouse

How about something like this:

Home HomePhone
=[firstname] & " " & IIF(IsNull(CellPhone), "No Cell Phone", "'s " &
"Cell"
& " " CellPhone)
=[spousename] & " " & IIF(IsNull(SPCell), "No Cell Phone", "'s " & "Cell"
&
" " SPCell)

Steve
(e-mail address removed)

I am putting together a phone directory based on a membership list. I
have
it set up so that it looks like this:

[quoted text clipped - 16 lines]

Home (123) 555-1212
Doug's Cell (123) 555-4545

 

[Fred]

So, for each line, you have 4 possible scenarios:

1. Name exists, cell doesn't
2. Cell exists, name doesn't (probably = bad data entry)
3. Neither exists
4. Both exist

Now you have to decide what you want to have happen for each of the 4
scenarios, keepin the answer as simple as possible.

Let's assume it's: If it's #1, #2 or #3, print nothing. If it's #4, print
as you described. Then I think this (not tested) would work:

=iif ([spousename] is not null and [spcell] is not
null,InsertYourOriginalExpressionHere,"")

Make sure everything is set to "can shrink" so that you don't "print" a
blank line.

Hope that helps a little.

 

[John... Visio MVP]

I would include #2, and print "spouse" if spousename is not present.

John... Visio MVP

 

[Fred]

John,

FYI, our answers were simultaneous; I didn't see yours when I wrote mine.

 

[John... Visio MVP]

John,

FYI, our answers were simultaneous; I didn't see yours when I wrote mine.

Great minds think alike, then there is us. ;-)

John... Visio MVP

 

[John W. Vinson]

Great minds think alike, then there is us. ;-)

John... Visio MVP

Great minds run in the same channels...
small minds run in the same gutters! (that's mine, anyway).