Force Time Field with airSlate SignNow

Force time field

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross in today's video we're going to talk about making sure times are entered in 15-minute increments we're going to force users to enter times in 15-minute increments with a validation rule today's question comes from sharon from puyallup washington i hope i'm pronouncing that right one of my access expert students sharon says i'm creating a time card in microsoft access how do i restrict time entry to 15 minute increments i was hoping to enter a validation rule that warns them they must enter time in 15 minute increments and let the user fix it instead of automatically rounding i don't know how to structure the validation rule well let's take a look how to do this i'm going to take my blank template you can download this from my website it's free there's a link down below let's create a table create table design i'll throw an id in here auto number and let's just throw a date time in here that's a date time field i'll save this as my date t okay nice and simple and if i comb over here to put dates in i can put dates and times in right 1 1 20 at 6 32 pm nothing's stopping you okay now now validation rules are right down here and you can use validation rules to restrict this data entry and if they violate that rule pop up a message that's the violation text i've got another video on how basic validation rules work i'll put a link to it down below go watch that first if you've never used a validation rule before you can do things like make sure a customer's credit limit doesn't go over five thousand dollars and things like that now let's start off easy let's say i want the user to type in whole minutes no seconds are allowed okay let's go to the validation rule design validation rule now there are functions to determine the day month year hours minutes and seconds of a particular date time field to make sure the user enters no seconds in that means seconds of that date time has to be zero so second and notice there's a second function see it's a second then a time put a time value in here now our field name is date time so put in date time like that close parenthesis but be careful because as soon as i tab off and watch what happens it puts it in quotes this is one of those times we have to make sure you put the brackets around your field name i want the field date time not the word date time okay so second date time equals zero that's my validation rule so you can enter whatever time you want whatever date time you want as long as the seconds are zero all right let's save this it says data integrity rules have changed existing data may not be valid in other words it's going to have to check and make sure that all the data in the table matches that validation rule all right but now let's go back to table view here and if i change this to let's say 633 okay that's good if i change it to 633 and not a w and two seconds look at that one or more values are prohibited by the validation rule okay so that's how you set that up now sharon wants this to be in 15 minute increments so that means the seconds have to be zero and the minutes have to be either 0 15 30 or 45. how do we do that well just like the second function there is a minute function and an hour function okay and a day function and a function there's all kinds of i cover all these in my class by the way and my access expert 11 class i'll put a link to that down below too so second has to be zero and minute of date time has to also be zero let's leave it at that for now this will force it to be whole hours okay because second has to be zero and minute has to be zero save that date integrity rules have changed hit yes now look it says existing data violates the new setting for the validation rule in other words you've got data in your table that doesn't work with that all right just cancel this now flip back over here and watch this if i change this to 6 34 it yells at me okay but if i change this to six o'clock now i'm good all right second is zero minute zero so now we just tweak this so we say here minute is zero and minute is 15 and then it is 30 but in in programming terms that's an or condition okay so this has to be true and this has to be true or a bunch of other options in here so we're going to do this open parentheses right we're going to copy this to our clipboard right here ctrl c it's going to retype it every time all right or minute is 15 or minute is 30 or minute is 45 close parentheses all right i can zoom in and show you all of these here so you can see it there it is nice and big okay second is zero and this whole thing and this whole thing is minute is zero or minute is 15 or minute is 30 or a minute is 45 and if they violate that the validation text pops up makes something a little nicer than what axis pops up right time has to be entered in 15 minute increments or intervals or whatever you want to say save it yes let's open it back up again here we go all right next record 115 good and if you want military time you can format that as well right design view in your format here right just h colon n that's all you need that's short time all right save it i'm usually just typing in the format codes okay if the if the date portion doesn't matter to you all right 3 30 good 13 31 oh time has to be entered in 15-minute increments okay gotta fix it 13.45 good and there you go that's how you set up that validation rule hope that answers your question sharon want to learn more about this 15 increment stuff well sharon didn't want to automatically round but i do so in the extended cut i'm going to show you how to round the start times down and the end times up this is handy if you're billing a customer right if you start work at 302 and you finish at 358 well they're getting billed from three o'clock to four o'clock okay we'll use the after update event and we'll use the time serial function now all that's in the members only extended cut 11 minutes long silver members and up get access to all of my extended cut videos and there's getting close to 100 of them now how do you become a member click on the join button below the video silver members and up will get access to all of my extended cut tech help videos live video and chat sessions and other perks after you click the join button you'll see a list of all the different membership levels that are available each with its own special perks but don't worry these tech help videos are going to keep coming as long as you keep watching them i'll keep making more and they'll always be free if you enjoyed this video please give me a thumbs up and feel free to share it wherever you think it might help people who are interested in access make sure you subscribe to my channel which is completely free and click on the bell icon to select all to be notified every time i post a new video youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a new video click on the link below to join my mailing list click on the show more link below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more now if you have not yet tried my free access level one course check it out now it covers all the basics of microsoft access it's over three hours long and you can find it on my website or on my youtube channel i'll include a link below you can click on and if you like level one level two is just one dollar and that is free for all members of my youtube channel at any level want to have your question answered in a video just like this one visit my tech help page and you can send me your question there if you have a specific problem you need help with or you'd like to discuss having a database built for your needs i do offer one-on-one consulting be sure to follow my blog and find me on facebook twitter and of course youtube once again my name is richard ross thank you for watching this tech help video brought to you by accesslearningzone.com i hope you enjoyed this video and you learned something today i'll see you again soon you