Formatting a number within text.

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

Guest

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.
 
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"))))
 
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.
 
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)


Ken Snell (MVP) said:
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>




Jerry McNabb said:
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.
Click to expand...
Click to expand...
 
I did that and now the phone number which is stored as 1234567890, displays
as (123) -7890. What happened to the 456?
 
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.
 
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"))))
 
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.
 
< 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"))))
 
Post the expression that you're trying to use. It likely still has some
typo(s) in it.
 
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.
 
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
..
 
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.
Click to expand...

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
--
 
John Spencer said:
I think Ken is not available at present.
Click to expand...

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"))
Click to expand...

Thanks, John. I concur.
 
Back
Top