Input Mask

[Serge-Nanaimo]

Hi,

I want to do business card with Phone number and cell number.
I have the formula to stipulate that if there is no Phone number then choose Cell number as below.

=IIf(IsNull([PhoneNO]),"Cell: " & [Cell],[PhoneNo] & " Cell: " & [Cell])

My question is :
Is there a way that I can input a Mask to see the phone number as (222) 222-2222 vice 2222222222 when using an Iif ?



Tks.

[andy49]

If it's being displayed in a text box and the phone numbers will always be the same format, you could set up a function something like

Function masknumber (phone as integer) as string

masknumber = "(" & left(phone,3) & ")" & mid(phone,4,3) & "-" & right(phone,4)

end function

Maybe test somewhere that the phone number was the right length too?

[Serge-Nanaimo]

Hi, thanks for the info.
Unfortunately I can't get it to work. I never worked with code before, so I'm probably not grasping all the concept of it.

[HiTechCoach]

It can be done by adding format() to the phone numbers using the format string "(000) 000-0000"

Try:

=IIf(IsNull([PhoneNO]),"Cell: " & Format([Cell],"(000) 000-0000")),Format([PhoneNo],"(000) 000-0000") & "  Cell: " & Format([Cell],"(000) 000-0000"))

or simplifying your original code to

=Trim(IIf(IsNull([PhoneNO]),"",[PhoneNo]) & "  Cell: " & [Cell])

would then be

=Trim(IIf(IsNull([PhoneNO]),"",Format([PhoneNo],"(000) 000-0000")) & " Cell: " & Format([Cell],"(000) 000-0000"))

[Serge-Nanaimo]

Works perfect. Thank you very much.
Serge.

[HiTechCoach]

You're welcome.

Glad we could assist.

[Serge-Nanaimo]

Hi again,

Trying to do the same thing but with the Postal Code in a query.

Expres1: [Address ] & " " & [City] & ", " & [StateOrProvince] & ", " & Format([PostalCode ],"L0L 0L0")

For some reason it doesn't leave a space in the middle of the Postal Code.

Tks in adv.

[andy49]

Not sure it's possible in a query. What about checking if the 4th digit is a space and then inserting one if it isn't

Or better to check data at source?




Sent from my iPhone using Tapatalk

[HiTechCoach]

Hi again,

Trying to do the same thing but with the Postal Code in a query.

Expres1: [Address ] & " " & [City] & ", " & [StateOrProvince] & ", " & Format([PostalCode ],"L0L 0L0")

For some reason it doesn't leave a space in the middle of the Postal Code.

Tks in adv.

What you are using trying to use is an input mask for a textbox control. This doesn't work the same for the Format().

Try:

Expres1: [Address ] & " " & [City] & ", " & [StateOrProvince] & ", " & Format([PostalCode ],">@@@ @@@")

[Serge-Nanaimo]

Tks Boyd,

Works great.
:-)

[HiTechCoach]

You're Welcome.

Glad we could assist.