How to use Input Mast in SQL Statement

[terryechols1]

I'm pulling a phone number field in a form via SQL but it doesn't display as the Input Mask dictates in just this one form. All reports and other forms show it correctly so I'm wondering if there is a way to use the mask via format in the SQL statement?

The mask is: \(999") "000\-0000\ 9999;0;_ (as it is in the Customers table)

The mask formats a phone number field that shows, if there, an extension.

Example:
9545651055 13 will display as [phone] 13

Here is the code I have that does everything with the exception of formatting the [Business Phone] field.

SELECT [Customers Extended].ID, [Customers Extended].[Customer Name], [Customers Extended].[Company], FORMAT ('\(999") "000\-0000\ 9999;0;_')([Customers Extended].[Business Phone]) As [Business Phone], [Customers Extended].[Mobile Phone], [Customers Extended].[Fax Number] 
FROM [Customers Extended] 
WHERE (([Customers Extended].ID)<>Form!ID) And (([Customers Extended].[Customer Name]) Like "*" & [txtCustNameContains] & "*") And (([Customers Extended].[Company]) Like "*" & [txtCompanyNameContains] & "*") 
ORDER BY [Customers Extended].[Customer Name];
 

Oddly enough the fax and mobile number fields, both use mask (@@@) @@@-@@@@, both display correctly in the code above. Of course the problem with the business phone is some have extensions.

How can I get these results displayed with the business phone formatted correctly?

Thanks,

[jforbes]

Is this not displaying correctly on a Form, Report or Query?

Also, this syntax is unfamiliar to me:

FORMAT ('\(999") "000\-0000\ 9999;0;_')([Customers Extended].[Business Phone])

Could you explain a little more of what you are attempting to do?

[terryechols1]

The problem with this one is there is no actual field to place a mask on as this is being written "on-the-fly" through a SQL statement.

I've created a search from to find customer details which is tied to a SQL Query as the record source. I'm using this query as the source as it combines the first name/last name fields into "Customer Name". What I need/want to do is use the same type of masking for the format of this column in the displayed results. The mask (as it is used in the database is written like this:

\(999") "000\-0000\ 9999;0;_

What it does is format the string of numbers into groups of (xxx) xxx-xxxx AND the last 9999 part leaves room for a 4 digit extension which is displayed as (xxx) xxx-xxxx ____ (without the underscores). See attached photo.

Oddly, some of the records are showing formatted but other are not. They are stored the same in the database all as numbers only - no spaces or other characters at all. I'm stumped.

Terry

[terryechols1]

OK. Figured it out, not the input mask part but the data entry part. If they type the numbers it works if the copy/paste them in the Customers database then the number doesn't display correctly. Not sure why.

Anyway around that? Anyway to make the SQL Select statement format the business number field IN the statement? This way it winds up displaying correctly everywhere?

OR

Is there a way to pull all the business numbers as numbers ONLY, stripping out anything else, then upload them back again? Would that even work?

Thanks,

[jforbes]

There is a difference between an Input Mask, Format, VBA Format, and SQL Format.

There are a lot of similarities between an Input Mask and Formatting when it comes to a TextBox. For a TextBox, the Input Mask will define how the TextBox acts when the User is entering the Data. It can also make sure that the Mask is stored along with the Data. And it can re-apply the mask when the data is displayed. So if you want to save the parenthesis for the phone number in your field, then using an Input Mask will make this a lot easier.

Performing any sort of formatting in SQL is usually a bad idea. There are plenty of exceptions to this, but it's usually best to return Raw Data, then perform the formatting in a Report or Form.

Usually in this situation, an Input Mask is either setup on the field in the Table or placed on the TextBox of the Form. ...or both, I think if an Input Mask is put on a Field in the Table, when the field is Dropped on a Form or a Report, the Input Mask for the Field is inherited from the Table.

So, to get your stuff working, remove any Formatting in the SQL Select. Remove the Formatting from the Business Phone TextBox. Then I think if you set the Business Phone TextBox's Input Mask to \(999\)999\-9999\ 9999 it should start working for you.

Either way, I don't think you should be messing with Formatting at all for this as it is limited in comparison to an Input Mask. For the Fax and Mobile number working with the Format is an option that you can continue to work with, but the Input Mask wont be available for Data Entry.

Also, there is a button with Tripple Dots (...) on the Input Mask Property that will open a wizard that might help you.