Formatting a number within text.

[Jerry McNabb]

I am creating a report in Access 2002. It is based on a union query. The
Union query unites the output of 9 select queries. A number of the queries
have expressions similar to this: "PhoneNote: (IIf (IsNull ([Home
Phone]),"Home Phone not Available", (IIf (PublishHomePhone, ("Home " & [Home
Phone]), "Home Phone Unpublished"))))." When the phone number appears I want
the standard punction, i.e. (123) 456-7890 in the report. Is there any way to
do that?

Thank you.

 

[Ken Snell \(MVP\)]

How are you storing the phone number in the database -- as 9999999999
numbers?

If yes, change the query's calculated field's expression to this:

PhoneNote: (IIf (IsNull ([Home Phone]),"Home Phone not Available", (IIf
(PublishHomePhone, ("Home " & "(" & Left([Home
Phone], 3" & ") " & Mid([Home Phone], 4, 3" & "-" & Right([Home Phone], 4)),
"Home Phone Unpublished"))))

 

[Jerry McNabb]

Thank you. I knew there had to be some operator such as Mid but I couldn't
fined it in help. I had enough trouble finding Right and Left even though I
knew they existed and thought I remembered the format.

 

[Douglas J. Steele]

Any reason not to use

PhoneNote: IIf (IsNull ([Home Phone]),"Home Phone not Available", IIf
(PublishHomePhone, "Home " & Format([Home Phone], "(###)-###-####"),
"Home Phone Unpublished"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

How are you storing the phone number in the database -- as 9999999999
numbers?

If yes, change the query's calculated field's expression to this:

PhoneNote: (IIf (IsNull ([Home Phone]),"Home Phone not Available", (IIf
(PublishHomePhone, ("Home " & "(" & Left([Home
Phone], 3" & ") " & Mid([Home Phone], 4, 3" & "-" & Right([Home Phone],
4)), "Home Phone Unpublished"))))

--

Ken Snell
<MS ACCESS MVP>

I am creating a report in Access 2002. It is based on a union query. The
Union query unites the output of 9 select queries. A number of the
queries
have expressions similar to this: "PhoneNote: (IIf (IsNull ([Home
Phone]),"Home Phone not Available", (IIf (PublishHomePhone, ("Home " &
[Home
Phone]), "Home Phone Unpublished"))))." When the phone number appears I
want
the standard punction, i.e. (123) 456-7890 in the report. Is there any
way to
do that?

Thank you.

 

[Jerry McNabb]

I did that and now the phone number which is stored as 1234567890, displays
as (123) -7890. What happened to the 456?

 

[Jerry McNabb]

I tried that and I got an error message that I had an invalid date. I didn't
understand that.

I also wanted to do something that would pass through the report. I don't
think this would do that.

 

[Ken Snell \(MVP\)]

Sorry - typo on my part:

PhoneNote: (IIf (IsNull ([Home Phone]),"Home Phone not Available", (IIf
(PublishHomePhone, ("Home " & "(" & Left([Home
Phone], 3" & ") " & Mid([Home Phone], 4, 3) & "-" & Right([Home Phone], 4)),
"Home Phone Unpublished"))))

 

[Douglas J. Steele]

Odd. It worked when I tested it.

Try replacing the octothorpes (#) with zeroes:

PhoneNote: IIf (IsNull ([Home Phone]),"Home Phone not Available", IIf
(PublishHomePhone, "Home " & Format([Home Phone], "(000)-000-0000"),
"Home Phone Unpublished"))

I'm not quite sure I understand what you mean by "pass through the report".
Whatever appears for that field in the query is what will appear in the
report.

 

[Jerry McNabb]

What I get now is Home () 456-7890.

 

[Ken Snell \(MVP\)]

< sigh -- some days, the fingers and eyes just don't coordinate ... >

Sorry - typo on my part:

PhoneNote: (IIf (IsNull ([Home Phone]),"Home Phone not Available", (IIf
(PublishHomePhone, ("Home " & "(" & Left([Home
Phone], 3) & ") " & Mid([Home Phone], 4, 3) & "-" & Right([Home Phone], 4)),
"Home Phone Unpublished"))))

 

[Jerry McNabb]

Yeah, I spotted that typo and still get the "Home () 456-7890." format

 

[Ken Snell \(MVP\)]

Post the expression that you're trying to use. It likely still has some
typo(s) in it.

 

[Jerry McNabb]

You're right. Here's what I'm trying.

PhoneNote: (IIf(IsNull([Home Phone]), "Home Phone not Available", (IIf
(PublishHomePhone, ("Home (" & Left([Home Phone],3) & ") " & Mid([Home
Phone],4,3) & "-" & Right([Home Phone],4)), "Home Phone Unpublished"))))

Access won't even take this one. It complains about a comma.

 

[John Spencer]

I think Ken is not available at present. I think that what you are trying
to do could be accomplished with

PhoneNote: IIf(IsNull([Home Phone]), "Home Phone not Available"
, IIf (PublishHomePhone, "Home (" & Left([Home Phone],3) & ") "
& Mid([Home Phone],4,3) & "-" & Right([Home Phone],4)
, "Home Phone Unpublished"))


Personally, I would probably use something more like the following
PhoneNote: IIf(IsNull([Home Phone]), "Home Phone not Available"
, IIf (PublishHomePhone, " Home " & FORMAT([Home Phone],"(@@@) @@@-@@@@")
, "Home Phone Unpublished"))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Chuck]

You're right. Here's what I'm trying.

PhoneNote: (IIf(IsNull([Home Phone]), "Home Phone not Available", (IIf
(PublishHomePhone, ("Home (" & Left([Home Phone],3) & ") " & Mid([Home
Phone],4,3) & "-" & Right([Home Phone],4)), "Home Phone Unpublished"))))

Access won't even take this one. It complains about a comma.

PhoneNote: (IIf(IsNull([Home Phone]), "Home Phone not Available", (IIf
([PublishHomePhone] = TRUE, ("Home (" & Left([Home Phone],3) & ") " & Mid([Home
Phone],4,3) & "-" & Right([Home Phone],4)), "Home Phone Unpublished"))))

Chuck
--

 

[Ken Snell \(MVP\)]

I think Ken is not available at present.

Yep, was out of town.

I think that what you are trying to do could be accomplished with

PhoneNote: IIf(IsNull([Home Phone]), "Home Phone not Available"
, IIf (PublishHomePhone, "Home (" & Left([Home Phone],3) & ") "
& Mid([Home Phone],4,3) & "-" & Right([Home Phone],4)
, "Home Phone Unpublished"))

Thanks, John. I concur.