I'm using a combined value text box for a cell phone number.
="Cell:" + [CP] , I can't keep the formating from the table. I've tried
using a similiar format that I received last week concerning a zip code
format, but I get syntex error or 'missing comma or paranthese.
The goal here is to specify that this phone is a cell phone since it
displays right next to a home phone, but I don't want Cell to show if there
isn't one.
Any help is appreciated.

Can you give a few examples of a cell phone number (that you want to display)
and some "non" cell phone numbers (that you don't want to display)


--
Wayne
Manchester, England.



"beth" wrote:

> I'm using a combined value text box for a cell phone number.
> ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> using a similiar format that I received last week concerning a zip code
> format, but I get syntex error or 'missing comma or paranthese.
> The goal here is to specify that this phone is a cell phone since it
> displays right next to a home phone, but I don't want Cell to show if there
> isn't one.
> Any help is appreciated.

It would help if you posted what you tried.

The following expression should work.

= IIF(Len([CP] & "") > 0, "Cell: " & [CP],Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

beth wrote:
> I'm using a combined value text box for a cell phone number.
> ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> using a similiar format that I received last week concerning a zip code
> format, but I get syntex error or 'missing comma or paranthese.
> The goal here is to specify that this phone is a cell phone since it
> displays right next to a home phone, but I don't want Cell to show if there
> isn't one.
> Any help is appreciated.

I need to display any number that appears in the table in a std format (area
code) ###-####. This is for a membership directory and will eventually go up
on the website and directly to a printer. So if no one gave me a cell no or a
fax no I don't want the label to be displayed on the report. I can get the
info to appear when I want I just can't maintain the input mask format.

"Wayne-I-M" wrote:

> Can you give a few examples of a cell phone number (that you want to display)
> and some "non" cell phone numbers (that you don't want to display)
>
>
> --
> Wayne
> Manchester, England.
>
>
>
> "beth" wrote:
>
> > I'm using a combined value text box for a cell phone number.
> > ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> > using a similiar format that I received last week concerning a zip code
> > format, but I get syntex error or 'missing comma or paranthese.
> > The goal here is to specify that this phone is a cell phone since it
> > displays right next to a home phone, but I don't want Cell to show if there
> > isn't one.
> > Any help is appreciated.

This is the base I started with, then added & deleted commas and parenthese
="Cell:" = [CP],format([CP], "(000) 000-0000)

"John Spencer" wrote:

> It would help if you posted what you tried.
>
> The following expression should work.
>
> = IIF(Len([CP] & "") > 0, "Cell: " & [CP],Null)
>
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
>
> beth wrote:
> > I'm using a combined value text box for a cell phone number.
> > ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> > using a similiar format that I received last week concerning a zip code
> > format, but I get syntex error or 'missing comma or paranthese.
> > The goal here is to specify that this phone is a cell phone since it
> > displays right next to a home phone, but I don't want Cell to show if there
> > isn't one.
> > Any help is appreciated.
>

I finally got it-
="Cell: "+IIf(Len([CP])=10,Format([CP],"(000) 000-0000"),[CP])
Thanks for being there again!

"John Spencer" wrote:

> It would help if you posted what you tried.
>
> The following expression should work.
>
> = IIF(Len([CP] & "") > 0, "Cell: " & [CP],Null)
>
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
>
> beth wrote:
> > I'm using a combined value text box for a cell phone number.
> > ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> > using a similiar format that I received last week concerning a zip code
> > format, but I get syntex error or 'missing comma or paranthese.
> > The goal here is to specify that this phone is a cell phone since it
> > displays right next to a home phone, but I don't want Cell to show if there
> > isn't one.
> > Any help is appreciated.
>

You have a syntax error and Cell: will be displayed regardless of whether
there is number in the field or not. If you use John's suggestion with a
Format added, I think it will get you what you want:
= IIF(Len([CP] & "") > 0, "Cell: " & Format([CP],"(000) 000-0000"),Null)

--
Dave Hargis, Microsoft Access MVP


"beth" wrote:

> This is the base I started with, then added & deleted commas and parenthese
> ="Cell:" = [CP],format([CP], "(000) 000-0000)
>
> "John Spencer" wrote:
>
> > It would help if you posted what you tried.
> >
> > The following expression should work.
> >
> > = IIF(Len([CP] & "") > 0, "Cell: " & [CP],Null)
> >
> > John Spencer
> > Access MVP 2002-2005, 2007-2008
> > The Hilltop Institute
> > University of Maryland Baltimore County
> >
> > beth wrote:
> > > I'm using a combined value text box for a cell phone number.
> > > ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> > > using a similiar format that I received last week concerning a zip code
> > > format, but I get syntex error or 'missing comma or paranthese.
> > > The goal here is to specify that this phone is a cell phone since it
> > > displays right next to a home phone, but I don't want Cell to show if there
> > > isn't one.
> > > Any help is appreciated.
> >

I got it with this-
="Cell: "+IIf(Len([CP])=10,Format([CP],"(000) 000-0000"),[CP])
Thanks for everyone's help

"Klatuu" wrote:

> You have a syntax error and Cell: will be displayed regardless of whether
> there is number in the field or not. If you use John's suggestion with a
> Format added, I think it will get you what you want:
> = IIF(Len([CP] & "") > 0, "Cell: " & Format([CP],"(000) 000-0000"),Null)
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "beth" wrote:
>
> > This is the base I started with, then added & deleted commas and parenthese
> > ="Cell:" = [CP],format([CP], "(000) 000-0000)
> >
> > "John Spencer" wrote:
> >
> > > It would help if you posted what you tried.
> > >
> > > The following expression should work.
> > >
> > > = IIF(Len([CP] & "") > 0, "Cell: " & [CP],Null)
> > >
> > > John Spencer
> > > Access MVP 2002-2005, 2007-2008
> > > The Hilltop Institute
> > > University of Maryland Baltimore County
> > >
> > > beth wrote:
> > > > I'm using a combined value text box for a cell phone number.
> > > > ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> > > > using a similiar format that I received last week concerning a zip code
> > > > format, but I get syntex error or 'missing comma or paranthese.
> > > > The goal here is to specify that this phone is a cell phone since it
> > > > displays right next to a home phone, but I don't want Cell to show if there
> > > > isn't one.
> > > > Any help is appreciated.
> > >

I thought you said you did not want Cell: to show if there is no number. As
you have it written, it will show. Also, I would not trust your Len = 10
test.
--
Dave Hargis, Microsoft Access MVP


"beth" wrote:

> I got it with this-
> ="Cell: "+IIf(Len([CP])=10,Format([CP],"(000) 000-0000"),[CP])
> Thanks for everyone's help
>
> "Klatuu" wrote:
>
> > You have a syntax error and Cell: will be displayed regardless of whether
> > there is number in the field or not. If you use John's suggestion with a
> > Format added, I think it will get you what you want:
> > = IIF(Len([CP] & "") > 0, "Cell: " & Format([CP],"(000) 000-0000"),Null)
> >
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "beth" wrote:
> >
> > > This is the base I started with, then added & deleted commas and parenthese
> > > ="Cell:" = [CP],format([CP], "(000) 000-0000)
> > >
> > > "John Spencer" wrote:
> > >
> > > > It would help if you posted what you tried.
> > > >
> > > > The following expression should work.
> > > >
> > > > = IIF(Len([CP] & "") > 0, "Cell: " & [CP],Null)
> > > >
> > > > John Spencer
> > > > Access MVP 2002-2005, 2007-2008
> > > > The Hilltop Institute
> > > > University of Maryland Baltimore County
> > > >
> > > > beth wrote:
> > > > > I'm using a combined value text box for a cell phone number.
> > > > > ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> > > > > using a similiar format that I received last week concerning a zip code
> > > > > format, but I get syntex error or 'missing comma or paranthese.
> > > > > The goal here is to specify that this phone is a cell phone since it
> > > > > displays right next to a home phone, but I don't want Cell to show if there
> > > > > isn't one.
> > > > > Any help is appreciated.
> > > >

I don't want cell to show, I used + not &. I printed it and it looks the way
I want, but will the len 10 be a problem when I save HTML

"Klatuu" wrote:

> I thought you said you did not want Cell: to show if there is no number. As
> you have it written, it will show. Also, I would not trust your Len = 10
> test.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "beth" wrote:
>
> > I got it with this-
> > ="Cell: "+IIf(Len([CP])=10,Format([CP],"(000) 000-0000"),[CP])
> > Thanks for everyone's help
> >
> > "Klatuu" wrote:
> >
> > > You have a syntax error and Cell: will be displayed regardless of whether
> > > there is number in the field or not. If you use John's suggestion with a
> > > Format added, I think it will get you what you want:
> > > = IIF(Len([CP] & "") > 0, "Cell: " & Format([CP],"(000) 000-0000"),Null)
> > >
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "beth" wrote:
> > >
> > > > This is the base I started with, then added & deleted commas and parenthese
> > > > ="Cell:" = [CP],format([CP], "(000) 000-0000)
> > > >
> > > > "John Spencer" wrote:
> > > >
> > > > > It would help if you posted what you tried.
> > > > >
> > > > > The following expression should work.
> > > > >
> > > > > = IIF(Len([CP] & "") > 0, "Cell: " & [CP],Null)
> > > > >
> > > > > John Spencer
> > > > > Access MVP 2002-2005, 2007-2008
> > > > > The Hilltop Institute
> > > > > University of Maryland Baltimore County
> > > > >
> > > > > beth wrote:
> > > > > > I'm using a combined value text box for a cell phone number.
> > > > > > ="Cell:" + [CP] , I can't keep the formating from the table. I've tried
> > > > > > using a similiar format that I received last week concerning a zip code
> > > > > > format, but I get syntex error or 'missing comma or paranthese.
> > > > > > The goal here is to specify that this phone is a cell phone since it
> > > > > > displays right next to a home phone, but I don't want Cell to show if there
> > > > > > isn't one.
> > > > > > Any help is appreciated.
> > > > >