Microsoft Access 2016 Data Validation Using Masks Types And Expressions Video Tutorial

Microsoft Access 2016 Data Validation Using Masks Types And Expressions Video Tutorial

  The way to ensure that data entered into Microsoft Office's Access database is by using data validation. This can be done by choosing the correct data types and also limiting them. Then we can use Data Masks to ensure date is entered in a certain formatted way. The powerful way to ensure that date is correctly validated is to use the Expression Builder. This video gives an introduction to the above.  
Transcript (machine generated so it contains errors)
1. 00:00:00:00 / 00:00:07:25 - hello this video is all about basically
2. 00:00:03:27 / 00:00:10:32 - how to ensure data entered into your
3. 00:00:07:25 / 00:00:13:04 - Access database is correct
4. 00:00:10:32 / 00:00:15:59 - ie validated and you restricted to
5. 00:00:13:04 / 00:00:18:48 - certain types it's an introduction video
6. 00:00:15:59 / 00:00:21:89 - so it should be a short one let's have a
7. 00:00:18:48 / 00:00:24:89 - quick look let's start a new blank
8. 00:00:21:89 / 00:00:28:19 - database make it larger ok
9. 00:00:24:89 / 00:00:31:01 - now as you can see when you get your
10. 00:00:28:19 / 00:00:34:80 - tables you have certain fields you can
11. 00:00:31:01 / 00:00:36:39 - go into the design view and mode will
12. 00:00:34:79 / 00:00:39:05 - save it as but yeah just keep that
13. 00:00:36:39 / 00:00:42:89 - stable one if we go into design view
14. 00:00:39:05 / 00:00:45:57 - you'll see the kind of types that it is
15. 00:00:42:89 / 00:00:47:91 - okay for example the first one is an
16. 00:00:45:57 / 00:00:50:75 - auto number data type it automatically
17. 00:00:47:90 / 00:00:53:42 - increases numbers and it takes a long
18. 00:00:50:75 / 00:00:57:00 - integer format let's look at some other
19. 00:00:53:42 / 00:01:02:96 - data types for example over here let's
20. 00:00:57:00 / 00:01:06:71 - call it currency okay and that will then
21. 00:01:02:96 / 00:01:09:09 - ensure that you are entering numbers so
22. 00:01:06:71 / 00:01:12:62 - I'll just call this currency give it a
23. 00:01:09:09 / 00:01:19:72 - name now
24. 00:01:12:62 / 00:01:23:03 - over here if I wanted to put in a 3/4
25. 00:01:19:72 / 00:01:24:90 - you see it says the value you enter does
26. 00:01:23:04 / 00:01:28:59 - not match the currency data type in this
27. 00:01:24:90 / 00:01:31:89 - column so by choosing the data type
28. 00:01:28:59 / 00:01:34:46 - itself you've automatically created a
29. 00:01:31:89 / 00:01:38:25 - certain kinda like verification system
30. 00:01:34:46 / 00:01:42:68 - to ensure that users do not cause
31. 00:01:38:25 / 00:01:45:59 - problems by entering incorrect data then
32. 00:01:42:68 / 00:01:51:58 - let's go over here let's take shell we
33. 00:01:45:59 / 00:01:51:58 - say short text now we'll call it short
34. 00:01:52:12 / 00:01:58:17 - text
35. 00:01:54:39 / 00:02:02:20 - that's fine oops little s X right then
36. 00:01:58:17 / 00:02:06:42 - at the front but now if we go to a
37. 00:02:02:20 / 00:02:09:06 - design view you'll see our short text
38. 00:02:06:42 / 00:02:13:42 - which will just change the name over
39. 00:02:09:06 / 00:02:16:29 - there and basically you can limit the
40. 00:02:13:41 / 00:02:20:07 - size of the text of it okay that'll be
41. 00:02:16:29 / 00:02:25:04 - 255 characters let's bring it down to
42. 00:02:20:08 / 00:02:31:51 - shall we say three so it's a simple one
43. 00:02:25:04 / 00:02:33:54 - let's go back it'll ask us to save yes
44. 00:02:31:50 / 00:02:36:90 - it'll say some data may be lost because
45. 00:02:33:54 / 00:02:39:43 - basically if you are we're doing this at
46. 00:02:36:90 / 00:02:41:22 - the beginning of creating our table so
47. 00:02:39:43 / 00:02:43:54 - it's not a problem but let's assume you
48. 00:02:41:22 / 00:02:50:78 - had already created a table and you had
49. 00:02:43:53 / 00:02:54:72 - shall we say 255 letters in those fields
50. 00:02:50:78 / 00:02:58:35 - they would be now reduced to only three
51. 00:02:54:72 / 00:03:00:81 - so you'd lose a lot of those ok so just
52. 00:02:58:35 / 00:03:02:79 - bear that in mind if you do changes
53. 00:03:00:81 / 00:03:05:91 - later okay
54. 00:03:02:79 / 00:03:08:23 - so now we have our short text let's try
55. 00:03:05:91 / 00:03:12:09 - and enter more than those three
56. 00:03:08:22 / 00:03:15:39 - characters for example hello let's see
57. 00:03:12:09 / 00:03:17:70 - if it works it stopped at the first L
58. 00:03:15:40 / 00:03:19:65 - didn't allow anything more and as you
59. 00:03:17:70 / 00:03:22:44 - can hear the keyboards typing away it
60. 00:03:19:65 / 00:03:27:31 - stopped so that is also a way to
61. 00:03:22:44 / 00:03:31:26 - validate entering of data into it
62. 00:03:27:31 / 00:03:34:44 - there's other data types okay you can
63. 00:03:31:26 / 00:03:46:95 - take for example yes and no this is
64. 00:03:34:44 / 00:03:48:98 - basically yes or no okay it's nice to
65. 00:03:46:95 / 00:03:52:06 - stick them together because sometimes
66. 00:03:48:98 / 00:03:57:81 - spaces cause problems if programmers are
67. 00:03:52:06 / 00:03:59:68 - trying to access fields okay now what
68. 00:03:57:81 / 00:04:04:20 - yes or no is it's basically a boolean
69. 00:03:59:68 / 00:04:07:65 - which is true or false so you take it it
70. 00:04:04:20 / 00:04:07:93 - means it's yes it's on don't take it in
71. 00:04:07:65 / 00:04:11:73 - me
72. 00:04:07:93 / 00:04:13:98 - means no okay so that's another way of
73. 00:04:11:73 / 00:04:16:44 - validating it so for example if you
74. 00:04:13:98 / 00:04:19:81 - wanted to create shall we say is
75. 00:04:16:44 / 00:04:22:33 - customer currently active okay you could
76. 00:04:19:81 / 00:04:24:63 - have a boolean over there yes or no so
77. 00:04:22:32 / 00:04:25:59 - that's also restricted so people don't
78. 00:04:24:63 / 00:04:28:12 - type yes
79. 00:04:25:60 / 00:04:31:60 - don't type no some would have small some
80. 00:04:28:12 / 00:04:34:76 - would have a big letter and we would
81. 00:04:31:60 / 00:04:37:90 - cause a little bit of confusion so I
82. 00:04:34:76 / 00:04:40:83 - would suggest that when we are going
83. 00:04:37:89 / 00:04:43:81 - through there we look at basically the
84. 00:04:40:83 / 00:04:48:11 - different kinds of fields that you can
85. 00:04:43:81 / 00:04:52:36 - use okay there's all sorts of fields
86. 00:04:48:12 / 00:04:55:56 - attachments can be files pictures okay
87. 00:04:52:36 / 00:04:58:36 - hyperlinks would be a link and then
88. 00:04:55:56 / 00:05:02:07 - you'd basically have all these other
89. 00:04:58:36 / 00:05:06:63 - extra types okay so now let's look at
90. 00:05:02:07 / 00:05:09:33 - another way of formatting our text to
91. 00:05:06:63 / 00:05:12:31 - ensure it's validated and it's not
92. 00:05:09:33 / 00:05:14:43 - entered incorrectly phone numbers and
93. 00:05:12:31 / 00:05:17:56 - email addresses are typical kinda like
94. 00:05:14:43 / 00:05:21:87 - examples let's start with a phone number
95. 00:05:17:56 / 00:05:27:18 - okay so let's give it a number and let's
96. 00:05:21:87 / 00:05:30:18 - call it number now the way we're gonna
97. 00:05:27:18 / 00:05:34:65 - do this is actually by using an input
98. 00:05:30:18 / 00:05:39:75 - mask okay let's assume you wanted to
99. 00:05:34:65 / 00:05:44:13 - have the first three numbers as an area
100. 00:05:39:75 / 00:05:46:62 - code and that needs to be entered so for
101. 00:05:44:13 / 00:05:50:28 - example we'd do that would have a
102. 00:05:46:62 / 00:05:56:61 - backslash in our input mask and then
103. 00:05:50:27 / 00:06:03:06 - brackets and then we'll have three
104. 00:05:56:61 / 00:06:07:36 - numbers okay and then we basically close
105. 00:06:03:06 / 00:06:10:95 - it off simple as that okay now let's try
106. 00:06:07:36 / 00:06:15:34 - another type using a phone number okay
107. 00:06:10:95 / 00:06:20:25 - where we need to specify how many digits
108. 00:06:15:33 / 00:06:21:57 - and an area code okay the way to do this
109. 00:06:20:25 / 00:06:23:64 - is actually by you
110. 00:06:21:57 / 00:06:28:12 - using an input mask and we'll switch to
111. 00:06:23:64 / 00:06:31:26 - design mode okay for our input mask
112. 00:06:28:12 / 00:06:34:38 - let's assume we are going to have an
113. 00:06:31:26 / 00:06:38:52 - area code surrounded by brackets okay
114. 00:06:34:38 / 00:06:41:22 - now to ensure that the certain
115. 00:06:38:52 / 00:06:46:59 - characters are coming in the number box
116. 00:06:41:22 / 00:06:50:37 - we need to actually have a backslash so
117. 00:06:46:59 / 00:06:53:01 - that's a backslash and then that will
118. 00:06:50:37 / 00:06:55:65 - ensure that our Open bracket actually is
119. 00:06:53:01 / 00:07:03:27 - shown and then let's say it needs to be
120. 00:06:55:65 / 00:07:05:22 - three digits so oops the number sign so
121. 00:07:03:27 / 00:07:08:12 - three digits and then once again
122. 00:07:05:22 / 00:07:11:86 - backslash we close off our bracket and
123. 00:07:08:12 / 00:07:16:05 - then let's say we need seven digits
124. 00:07:11:86 / 00:07:20:46 - after our area code let's have our
125. 00:07:16:05 / 00:07:25:99 - number sign one two three four five six
126. 00:07:20:46 / 00:07:29:68 - seven let's test it out you need to save
127. 00:07:25:99 / 00:07:32:71 - it that's fine for a phone number let's
128. 00:07:29:68 / 00:07:36:81 - enter in as you can see it needs three
129. 00:07:32:70 / 00:07:40:59 - digits in the area code so that's for
130. 00:07:36:81 / 00:07:44:52 - example two zero three and then seven
131. 00:07:40:59 / 00:07:49:38 - digits so let's say one two three and
132. 00:07:44:52 / 00:07:52:24 - then four five six seven and if I try to
133. 00:07:49:38 / 00:07:56:76 - enter more digits it doesn't allow me so
134. 00:07:52:24 / 00:07:59:94 - that has restricted us to entering phone
135. 00:07:56:76 / 00:08:02:22 - numbers in a certain fashion which is a
136. 00:07:59:94 / 00:08:06:03 - very good way by using an input mask
137. 00:08:02:22 / 00:08:09:72 - there are special characters that you
138. 00:08:06:03 / 00:08:14:16 - can use in input mass for example you
139. 00:08:09:72 / 00:08:18:09 - can use and symbols which means it's a
140. 00:08:14:16 / 00:08:20:37 - required character if you put like a
141. 00:08:18:09 / 00:08:23:88 - question mark it's an optional character
142. 00:08:20:37 / 00:08:26:05 - you can have wildcard characters which
143. 00:08:23:88 / 00:08:29:94 - is the star I would suggest you actually
144. 00:08:26:05 / 00:08:32:01 - do take a quick look online okay for
145. 00:08:29:94 / 00:08:34:36 - input masks special characters and
146. 00:08:32:01 / 00:08:34:81 - you'll find a list of them okay but this
147. 00:08:34:36 / 00:08:38:33 - is
148. 00:08:34:82 / 00:08:41:33 - is a very simple straightforward way of
149. 00:08:38:33 / 00:08:45:19 - creating a phone um okay now we come to
150. 00:08:41:33 / 00:08:47:60 - another type of validation which uses
151. 00:08:45:19 / 00:08:53:75 - expressions okay
152. 00:08:47:60 / 00:08:55:79 - let's say email email is validated some
153. 00:08:53:75 / 00:08:59:21 - sort of text then you have an @ symbol
154. 00:08:55:78 / 00:09:01:21 - then you have some more text and then
155. 00:08:59:21 / 00:09:04:10 - it's a full stop and then it's like
156. 00:09:01:22 / 00:09:08:93 - a.com some more text okay
157. 00:09:04:10 / 00:09:16:15 - so let's give this short text let's call
158. 00:09:08:92 / 00:09:20:65 - it email okay now we go to our design
159. 00:09:16:15 / 00:09:25:17 - view and our validation rule this is
160. 00:09:20:65 / 00:09:28:51 - where we need to be okay click there and
161. 00:09:25:17 / 00:09:30:76 - it gives us an expression builder this
162. 00:09:28:51 / 00:09:32:80 - is actually quite a complicated topic
163. 00:09:30:76 / 00:09:35:87 - but we're just giving you a general
164. 00:09:32:80 / 00:09:39:04 - introduction so the first thing is we're
165. 00:09:35:87 / 00:09:42:74 - trying to find it like what I said some
166. 00:09:39:04 / 00:09:45:28 - characters followed by an @ symbol some
167. 00:09:42:74 / 00:09:51:44 - more characters followed by a full stop
168. 00:09:45:28 / 00:09:54:34 - and then some more characters so we go
169. 00:09:51:44 / 00:09:57:50 - to an operator and then comparison so we
170. 00:09:54:35 / 00:10:01:00 - want to make it like a certain
171. 00:09:57:50 / 00:10:04:80 - expression so that and I'll just paste
172. 00:10:01:00 / 00:10:08:50 - listen but very simple I'll explain
173. 00:10:04:80 / 00:10:11:00 - what's going on here we have the speech
174. 00:10:08:50 / 00:10:13:73 - marks which is indicating text so
175. 00:10:11:00 / 00:10:16:27 - basically this is all gonna be text
176. 00:10:13:73 / 00:10:21:23 - within here open speech marks and speech
177. 00:10:16:27 / 00:10:24:19 - marks then we have our star which is any
178. 00:10:21:23 / 00:10:27:40 - character is the question mark which is
179. 00:10:24:20 / 00:10:30:50 - any character so we need these two then
180. 00:10:27:40 / 00:10:34:37 - it's followed by an @ symbol that we do
181. 00:10:30:50 / 00:10:38:89 - need and then any kind of characters
182. 00:10:34:37 / 00:10:40:66 - then a full stop and then any more
183. 00:10:38:89 / 00:10:44:78 - characters that's a very basic
184. 00:10:40:66 / 00:10:48:83 - validation rule so let's click OK
185. 00:10:44:78 / 00:10:55:62 - and validation texicans we get it wrong
186. 00:10:48:84 / 00:10:58:70 - so enter a valid email okay let's see
187. 00:10:55:62 / 00:11:02:03 - let's go back it'll ask us is save it
188. 00:10:58:70 / 00:11:03:44 - might say things like this don't worry
189. 00:11:02:03 / 00:11:04:64 - because we've already created a few
190. 00:11:03:44 / 00:11:08:70 - fields
191. 00:11:04:64 / 00:11:10:07 - that's why yep okay so email let's test
192. 00:11:08:70 / 00:11:14:75 - it out to see if it works
193. 00:11:10:08 / 00:11:16:67 - so bob.com does that work now enter a
194. 00:11:14:75 / 00:11:24:77 - valid email address
195. 00:11:16:66 / 00:11:27:26 - Bob app let's say Windows ninja ninja
196. 00:11:24:77 / 00:11:33:29 - and then we have our full stop
197. 00:11:27:26 / 00:11:39:21 - dot-com it accepts it so this is how we
198. 00:11:33:29 / 00:11:46:37 - do data validation in Access let's go
199. 00:11:39:21 / 00:11:49:56 - here and for our phone number instead of
200. 00:11:46:37 / 00:11:54:45 - having them as the number sign if we
201. 00:11:49:55 / 00:11:58:50 - actually have them as zeros 1 2 3 4 5 6
202. 00:11:54:45 / 00:12:05:33 - 7 and then over here as well we'll have
203. 00:11:58:50 / 00:12:11:85 - them as zeros 1 2 3 bring it back save
204. 00:12:05:33 / 00:12:15:59 - what we need to do now whoops is let's
205. 00:12:11:85 / 00:12:15:59 - just delete everything
206. 00:12:19:16 / 00:12:30:14 - okay so 203 one two three four five six
207. 00:12:25:49 / 00:12:33:48 - we're missing one you see it needs that
208. 00:12:30:14 / 00:12:38:60 - extra character so that's data
209. 00:12:33:48 / 00:12:40:81 - validation for the vast majority of the
210. 00:12:38:60 / 00:12:44:72 - simple basic tasks that will be needed
211. 00:12:40:81 / 00:12:44:71 - hopefully this video has helped
Visit our YouTube channel: https://www.youtube.com/channel/UCFj1BHYIUYfPWPb1Xn5qFIg