Adding Numeric Fields and Setting Data Validation Rules

Locked lesson.

Upgrade

About this lesson

Add three numeric fields to the table and set the properties for each including data validation rules. 

Quick reference

Adding Numeric Fields and Setting Validation Rules

In this lesson, we are going to complete our table by adding the final three numeric fields and setting up validation rules and text to control the values entered into the field. 

When to use

Numeric fields are used whenever we want to add a field that contains numbers instead of text. We can apply validation rules and text to any field, numbers, or text to help control the information going into the field and provide users with helpful instruction. 

Instructions

Adding Numeric Fields

So far, the fields we have added to the table have all been text fields. Now, it's time to add some numeric fields to show the date the ticket was logged, the charge amount, and the SLA. 

  • From the Home tab, click Views and select Design View. 
  • Click in the Field Name column and type 'DateLogged'. 
  • Press the Tab key. 
  • In the Data Type column, select Date from the menu. 
  • In the Description column, type 'Date the ticket was logged'. 

The format for the date field should be changed to 'Medium'. 

  • Click in the Field Name column and type 'Charge'. 
  • Press the Tab key. 
  • In the Data Type column, select Currency from the menu. 
  • In the Description column, type 'Charge to fix this issue'. 
  • Click in the Field Name column and type 'SLA'. 
  • Press the Tab key. 
  • In the Data Type column, select Number from the menu. 
  • In the Description column, type 'Service Level Agreement for this ticket'. 
  • Set Field Properties as required. 

Adding Validation Rules and Text

Validation allows us to set a rule on our field and pop up a message if something is entered into the field that isn't valid. We can add useful text to the message to let users know what needs to be entered into the field. 

For example, there is a minimum call-out charge of $50. So, any value that is entered into the 'Charge' field must be greater than or equal to $50. 

  • Select the 'Charge' field. 
  • Click in the Validation Rule field property and type '>=50'.  

If a user enters a number below 50, we want a message to pop up that displays validation text. 

  • Click in the Validation Text field and type 'Minimum charge must be greater than or equal to 50'. 
  • Click in the Required field and select Yes. 
  • Click in the Indexed field and select Yes (Duplicates OK). 

Testing the Validation Rule

  • From the Home tab, click Views and select Datasheet View. 
  • Click in the Charge column. 
  • Type a number less than 50. 

If the validation rule has been created correctly, a pop-up message should appear that displays the validation text. 

Applying Number Formatting

Numeric values can be formatted. For example, we can change the number of decimal places, the currency symbol, and add a comma separator. Formatting options are found on the Table Fields ribbon in the Formatting group. 

Hints & tips

  • The default currency format we see in the datasheet view is determined by our region settings on our PC. 
Login to download
  • 00:04 So far, pretty much every field that we've added into our
  • 00:08 table has been a text base field.
  • 00:11 So in this lesson, I want to show you how you can add numeric fields.
  • 00:15 And we're going to add a date field.
  • 00:17 We're going to add a currency field.
  • 00:19 And we're going to add a number field.
  • 00:22 So, if we refer back to our example, we're going to add a field, so
  • 00:26 we can display the date that the ticket was logged.
  • 00:30 We're going to add a field that shows what the charge is for that ticket.
  • 00:34 And we're going to add a field that is going to show the SLA attached to this
  • 00:39 ticket, which is the service level agreement how many days this call or
  • 00:44 this ticket needs to be resolved in.
  • 00:46 So, let's start out with date logged.
  • 00:48 I'm going to go back to our database.
  • 00:50 Let's open up our ticket table and jump straight into Design View.
  • 00:55 So, our first field is going to be DateLogged.
  • 01:02 Now this time we're going to choose the date and time data type.
  • 01:07 Let's give it a description.
  • 01:12 And let's out the field properties.
  • 01:16 Now for this one, because it is a date we can choose a format for this date.
  • 01:21 So, if we click the drop down arrow,
  • 01:23 we can choose which date format we would prefer to use.
  • 01:27 So, I think I'm going to go for this one here, the medium date.
  • 01:32 Again, is this field required?
  • 01:34 Double-click, Yes, it is.
  • 01:35 Are we going to index this field?
  • 01:37 Yes, we are.
  • 01:39 Because we might want to search on the date field.
  • 01:42 The next field to add is the Charge field.
  • 01:48 Now this is going to display monetary values.
  • 01:51 So for this one, the data type is going to be Currency.
  • 01:55 And it's worth noting that the default currency that you use or that's set for
  • 02:00 your system is the default currency that will be used here.
  • 02:03 And let's check out our properties.
  • 02:05 So once again, I'm going to make sure I have that field selected.
  • 02:08 The format is Currency.
  • 02:10 Now if you do want to change that,
  • 02:12 you can choose from the drop down to a different type of currency.
  • 02:15 And we're going to introduce something slightly different here.
  • 02:19 Now notice that two of the field properties that we have
  • 02:22 are Validation Rule, and Validation Text.
  • 02:25 So, what you can basically do here is set up some controls on this field.
  • 02:30 For example, it might be that we always have a minimum call out
  • 02:34 charge to fix the problem of $50.
  • 02:37 So, the charge amount that's entered into the table cannot be less than $50,
  • 02:42 because there is always a minimum.
  • 02:44 So, we might want to protect this field from anybody entering in
  • 02:49 anything under $50.
  • 02:51 And we do that by adding in a validation rule.
  • 02:54 So, my validation rule here is that anything entered into
  • 02:59 this field must be greater than or equal to 50.
  • 03:03 And then underneath I can some text that will pop up if
  • 03:08 they do enter in anything that's invalid.
  • 03:12 Charge must be greater than or equal to $50.
  • 03:16 So, now that we've done, let's us just test that our validation is working.
  • 03:20 Lets jump back into Datasheet View.
  • 03:24 Yes, I want to save.
  • 03:26 Again, we're getting that integrity roll pop up.
  • 03:28 I'm going to say Yes, and then we're going to Cancel.
  • 03:31 Let's go through and complete all of our new fields.
  • 03:34 So, the technician for this first record is Mark Porter.
  • 03:38 So, let's type that in, Mark Porter.
  • 03:42 The description, well, let's widen out this field a bit.
  • 03:46 And the problem is Printer is jamming.
  • 03:51 The date logged, we can choose from a pick list, any date we like in here.
  • 03:55 So, the date that I want to enter is January the 10th.
  • 03:59 And the charge for this is going to be $200.
  • 04:02 But let's test our validation rule first of all.
  • 04:05 I'm going to try entering in a value that's under $50.
  • 04:08 So, let's just say 40.
  • 04:10 When I click on another field, notice what I get.
  • 04:13 That validation text is popping up in this little window.
  • 04:17 Minimum charge must be greater than or equal to 50.
  • 04:20 So, I can see that my validation is working quite nicely.
  • 04:24 So, let's just put the actual value in, 200.
  • 04:27 And I'm going to complete technician details for the other one,
  • 04:33 let's just say, Sally Walters, was going to say Broken Mouse.
  • 04:39 And I'm just going to choose the 9th,
  • 04:43 and this is going to be $150.
  • 04:47 So, really nice and straightforward to add that validation text in.
  • 04:52 Now if we just quickly jump back to Design View, we have one more field to add.
  • 04:56 And that is going to be the SLA.
  • 04:59 Now the SLA is going to be a numeric field, it's going to be one day,
  • 05:03 two days, three days, seven days.
  • 05:05 So, our data type is going to be number.
  • 05:08 So, now let's set our options.
  • 05:10 Now when it comes to a number field,
  • 05:12 in the field size notice that by default it's got long integer in there.
  • 05:17 And if we click the drop down, we have a few different options that we could use.
  • 05:21 And these are probably not going to mean too much to you.
  • 05:24 So, what I would encourage you to do is to have a little look through the help files,
  • 05:30 you can just search for number data types and
  • 05:32 you can have a little read as to what each of these means.
  • 05:36 Now, I know that long integer is a very,
  • 05:38 very long number just to put it in layman's terms.
  • 05:41 Integer is a slightly shorter number, and byte is a smaller number.
  • 05:46 So, I know I don't need a long integer.
  • 05:48 So, I'm just going to go for integer in this case.
  • 05:51 And in the required area, is this field required?
  • 05:53 Yes, it is.
  • 05:54 Is it going to be indexed?
  • 05:56 Yes, it is.
  • 05:57 Let's do our final data input.
  • 05:59 Let's go back to Datasheet View.
  • 06:01 Yes, to save the table.
  • 06:03 That's Yes and Cancel through that integrity check.
  • 06:07 And then we can enter in our SLA.
  • 06:10 So, for this first one it is 7 days, and
  • 06:13 we're going to say 5 days for the next one.

Lesson notes are only available for subscribers.

Gift this course