		Site Navigation
	Home
	Products
	Tags
	Levels
	Dates
	Authors

		5 MOST RECENT
	Click to view Braintrove article: Perform a Case-Insensitive String Comparison without Using Translate() Written by Gregory Scot Collins Thursday, 20 August 2009, 7:21 PM Products: InfoPath and Office Hidden in undocumented optional parameters of an extended function, there is a way to perform a case-insensitive string comparison without the need for the translate() function. Perform a Case-Insensitive String Comparison without Using Translate()
	Click to view Braintrove article: Force a Field Value to a Boolean Using Rules Written by Gregory Scot Collins Wednesday, 29 July 2009, 9:42 AM Products: InfoPath and Office Custom data validation is not available for fields bound to a secondary data source making it a challenge to validate to a specific data type. Using rules you can force a field value to a Boolean. This is especially useful when standardizing unconventional Boolean values copyied from a secondary data source. Force a Field Value to a Boolean Using Rules
	Click to view Braintrove article: Quickly Crop to a Shadow Written by Gregory Scot Collins Friday, 29 May 2009, 8:55 AM Product: Photoshop Learn how to crop to a shadow perfectly in a few seconds without the need to fuss with zoom and look for slight color variations. Quickly Crop to a Shadow
	Click to view Braintrove article: Sum Repeating Time Values Written by Gregory Scot Collins Tuesday, 14 April 2009, 2:33 PM Products: InfoPath and Office Sum time values in a repeating structure by converting them to seconds, and then display the results as seconds, minutes or hours. Sum Repeating Time Values
	Click to view Braintrove article: Preview and Open Forms after Upgrading to Internet Explorer 8 Written by Gregory Scot Collins Friday, 10 April 2009, 12:29 PM Products: InfoPath, Internet Explorer, Office, Visual Studio, Visual Studio Tools for Applications, Visual Studio Tools for Office and Windows After upgrading to Internet Explorer 8 you might find that you can no longer preview or open your managed code forms. There is an easy solution to this. Preview and Open Forms after Upgrading to Internet Explorer 8

		5 MOST POPULAR
	Click to view Braintrove article: Pass Query String Parameters to an ASP.NET Xml Control Written by Gregory Scot Collins Friday, 19 May 2006, 5:19 PM Product: ASP.NET Use the Page_Load method of your ASPX page to attach a list of parameters to your asp:Xml control that will be passed through to your XSL transform. Pass Query String Parameters to an ASP.NET Xml Control
	Click to view Braintrove article: Access a Method in a Master Page with Code-Behind Written by Gregory Scot Collins Friday, 16 June 2006, 7:09 AM Product: ASP.NET Write code for pages with code-behind to access a method in an immediate or parent master page from a content page. Access a Method in a Master Page with Code-Behind
	Click to view Braintrove article: Prevent Namespace Prefixes from Being Copied to the Output Written by Gregory Scot Collins Monday, 1 May 2006, 7:18 AM Products: HTML, JavaScript, XML, XPath and XSLT The extra namespace prefixes required to transform XML are not always desirable on the output. This article describes a technique for excluding specific namespace prefixes from the output. Prevent Namespace Prefixes from Being Copied to the Output
	Click to view Braintrove article: Create Groups in a Drop-Down List Box Written by Gregory Scot Collins Saturday, 17 June 2006, 5:57 AM Products: CSS, FireFox, HTML and Internet Explorer Create grouped option-sets in your drop-down list boxes, each with its own non-selectable header. Create Groups in a Drop-Down List Box
	Click to view Braintrove article: Change the Default Action of the Power Button on the Windows Vista Start Menu Written by Gregory Scot Collins Friday, 8 December 2006, 5:30 AM Product: Windows In Windows Vista, you can change the default action for the power button on the Start menu, as well as for other power events, in the advanced power settings. Change the Default Action of the Power Button on the Windows Vista Start Menu

		5 MOST FORGOTTEN
	Click to view Braintrove article: Install Windows 7 Beta onto a Virtual Machine Written by Gregory Scot Collins Wednesday, 25 February 2009, 9:52 AM Products: Virtual Server and Windows Walk through the simple process of installing Microsoft Windows 7 Beta onto a virtual machine with screenshots for every step in the process. Install Windows 7 Beta onto a Virtual Machine
	Click to view Braintrove article: Configure Lab Color Mode Defaults for Braintrove Articles Written by Gregory Scot Collins Wednesday, 30 April 2008, 10:36 PM Product: Photoshop Make the appropriate changes in a couple of dialog boxes in order to complete the tasks of Braintrove articles involving the Photohshop Lab Color mode. Configure Lab Color Mode Defaults for Braintrove Articles
	Click to view Braintrove article: Permanently Change the Default Styles for New Word Documents Written by Gregory Scot Collins Wednesday, 18 February 2009, 4:54 PM Products: Office and Word Using this surprisingly simple technique, you can quickly change the way that all newly created Microsoft Word documents look. Permanently Change the Default Styles for New Word Documents
	Click to view Braintrove article: Enable Themes in Windows Server Written by Gregory Scot Collins Monday, 31 March 2008, 3:25 PM Product: Windows Server Consumer versions of Windows have great themes. These themes are available to Windows Server too, but must be enabled before you can enjoy them. Enable Themes in Windows Server
	Click to view Braintrove article: Use Logical Operators in Place of Conditional Expressions Written by Gregory Scot Collins Wednesday, 28 February 2007, 9:22 AM Product: SQL Server Simplify your SQL query statements and reduce code by replacing conditional IF-ELSE expressions with appropriate logical operators. Use Logical Operators in Place of Conditional Expressions

		Microsoft MVP

This article has been viewed 135 times.

Force a Field Value to a Number Using Rules

Page 1 of 2

Written by Gregory Scot Collins

Friday, 20 March 2009, 9:57 AM

This article has been tested to work with the following products and versions. No guarantee of compatibility, with or without modification, is offered for products or versions other than those listed.

InfoPath 2007 and 2003 (Microsoft)
Office 2007 and 2003 (Microsoft)

IN THIS ARTICLE:

Most secondary data sources use the string data type for all nodes and sadly, custom data validation is not available, as shown in Dialog 1. While you could use code for custom data validation, you might consider using rules to force a value into compliance.

Dialog 1. Data validation is disabled for fields bound to secondary data source nodes.

This article will show how to force a field value to a number using rules. It will also show how to restrict the value further, if desired, to be a whole number, a positive number or a negative number.

Force to number

Use the following steps to create a rule to force a field value to a number:

Right-click the field, and then choose Text Box Properties.
In the Text Box Properties dialog box click Rules, and then click Add.
Name the rule Force to Number, and then click Add Action.
Select Set A Field's Value in the Action drop-down list.
Click the Select A Field Or Group button to the right of the Field text box, and then click OK.
Click the Insert Formula button to the right of the Value text box.
In the Formula text box type: translate(number(.), "Na", "").
Click OK three times.

The number function tries to convert the field value to a number. If it fails, the result is NaN, which stands for Not a Number. The translate function converts NaN to an empty string. The result is either a number or a blank value.

The following additional rules can be added if you want to further restrict the value entered to be a a whole number, a positive number or a negative number.

Force to whole number

Use the following steps to create a rule to force a number to be a whole number:

Add another Rule named Force to Number, and then click Set Condition.
Select Contains in the second drop-down list.
Select Type Text in the third drop-down list, and then type: . (i.e. a decimal point).
Click OK, and then click Add Action.
Select Set A Field's Value in the Action drop-down list.
Click the Select A Field Or Group button to the right of the Field text box, and then click OK.
Click the Insert Formula button to the right of the Value text box.
Type one the following in the Formula text box:

If you want to use standard rounding, type: round(.).
If you want to round down, type: floor(.).
If you wnat to round up, type: ceiling(.).

Click OK three times.

CONTINUED: Force to positive number

1 | 2 | Next >

		Bookmark this Article
	Stumble It!
	Digg It!
	del.icio.us

		List of Figures
	Dialog 1 - Data validation is disabled for fields bound to secondary data source nodes.

		Article Tags
	Data Validation Formulas InfoPath NaN Negative number() Numbers Office Positive Rules Secondary Data Sources Text Boxes translate() Whole Numbers

		Great Deals
	TigerDirect Exclusive Deals, Limited Time Offers, Act Now And Save!
	Find all current special offers on Adobe products.

Copyright © 2006-2010 Braintrove. All rights reserved. Braintrove, braintrove.com, and the Braintrove logo are trademarks of Gregory Scot Collins in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners. Any rights not expressly granted herein are reserved.