Input Mask for Neg. # to appear with ( )

[Amy Schmid]

Is there a way to set an input mask that a number entered -1234 to appear as
(1234)?

 

[John Spencer]

You can have the number appear that way if you use the format property. If it
is stored in a number field it will always have a negative sign.

Since you didn't say WHERE you want the value displayed that way it is a bit
difficult to tell you specifically, but the format with two decimal places
would be

#### ;(####);0 ;0

That should display the value as you indicated - the four sections are for
positive number, negative number, zero, and null values


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

 

[Amy Schmid]

I am sorry I was not more clear. I am looking for the numbers to appear with
( ) in the form and on the reports.

To clarify your response:
I put this code: #### ;(####);0 ;0 in the property value of the table?

Question: the #### is the code for positive number, regardless of how many
numbers are in the actual number? Same with the (####) for the negative
numbers?

 

[John Spencer]

Actually, that was probably overkill. You should be able to use
the format property and set it to

"#;(#);0;0"

Or you can use VBA to format the value.
Format(-1234,"#;(#);0;0")

You could put it in the format property of the table, but it won't transfer to
your reports and forms if they are already built. You will have to assign the
value to the format property of the individual controls on the forms and
reports.

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

 

[Amy Schmid]

Hi John,

I am trying to use this code. It will not let me enter more than one
character. I tried expanding the number of # signs and still no luck. I
think I am going to scrap this and tell the group requesting it to just use
the negative symbol.

 

[John Spencer]

You are entering the FORMAT string for the format property into the Input Mask
property.

When it comes to entry they really need to enter the minus sign. If the
numbers are ALWAYS going to be negative, you could allow them to enter the
number and then you would use code to make the value negative in the controls
after update event.

If Me.SomeNumberControl > 0 Then
Me.SomeNumberControl = - Me.SomeNumberControl
End If

You could still DISPLAY the number with parens using the format property.

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

 

[Amy Schmid]

John,

I can now enter the full digit in a field, but the display is not with
parens. I entered the code you suggested in the Design View of my table in
on the general tab for my number, in the Input Mask field.

In the format field, I have chosen standard so that the comma will appear.

Thanks for all your help with this.

 

[John Spencer]

Ok, so now you want commas. You can't use standard format as that does not
display parens for negative numbers unless you have customized the Regional
options in the operating system - control Panel : Regional and Language
Options : Customize button

Set the format property to
#,###;(#,###);0

Check the help for guidance.

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

 

[John W. Vinson]

In the format field, I have chosen standard so that the comma will appear.

If you want the Standard format, use it.

If you want a different format - with parentheses! - then don't use the
Standard format (which has no parentheses); instead follow John's accurate
instructions.