Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2014
    Posts
    48

    Unanswered: Field Level Validation Rules

    I'm kinda stuck here and I'm hoping that someone here might have a few suggestions. I'm trying to create field level Validation Rules for all the fields in my database tables. I want to put these rules into place to make sure that valid data is being entered as much as possible even for fields that are not required. There are a few that have me stumped and I would appreciate some input.

    1. Four position text field that can be blank but if it is not blank must contain a valid year. This is an Album Release Year or book published year.

    2. Date Added and Date Last Updated are date time fields and I just want to insure that these fields contain a valid date time value.

    3. Is there a way to check for the existance of a record in a primary table before the child record is added. Lookup tables help in this case but I would like to code a validation rule for this if possible.

    4. Check to see if the value of text field contains a valid path. This path is the directory where a given album's mp3 files reside. It's a partial path. Redundant parts like base folder are stored in another table. An example would be \AC DC\Bacl In Black\. The full path is actually D:\My Media\Music\AC DC\Back In Black\

    Any help you can offer is appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by VSCurtis View Post
    1. Four position text field that can be blank but if it is not blank must contain a valid year. This is an Album Release Year or book published year.
    In the table definition, use a validation rule, such as
    Code:
    (Between 1999 And 2099) Or Is Null
    Quote Originally Posted by VSCurtis View Post
    2. Date Added and Date Last Updated are date time fields and I just want to insure that these fields contain a valid date time value.
    In the table definition, define the column as of Date/Time data type.
    Quote Originally Posted by VSCurtis View Post
    3. Is there a way to check for the existance of a record in a primary table before the child record is added. Lookup tables help in this case but I would like to code a validation rule for this if possible.
    Inforce referential integrity of the relationship between both tables.
    Quote Originally Posted by VSCurtis View Post
    4. Check to see if the value of text field contains a valid path. This path is the directory where a given album's mp3 files reside. It's a partial path. Redundant parts like base folder are stored in another table. An example would be \AC DC\Bacl In Black\. The full path is actually D:\My Media\Music\AC DC\Back In Black\
    Possibly through the use of a regular expression in the validation rule (to be verified).
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    48
    Thank you for your suggestions.

    Your suggestions are helpful and give me food for thought. With regard to your suggestion about primary and child tables and enforcing RI, have done already. That still does not prevent the possibility of bad data being entered. I've done it by accident myself while manually entering some FK data. A validation rule would prevent this from happening. That's why I wanted a validation rule put in place if possible.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Your validation check on child records would be tricky to impose in the table design. However the Access 'way' is to enforce this through an embedded form for the child table linked to the host form using the parent key as part of the link criteria.

    To check if a file exists look at the excellent allen browne site, google ms access file exists....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2014
    Posts
    48
    My application will not be done in Access. The Access DB is merely a prototype. The final DB will be upsized from Access to SQL Server. Thanks for your input.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry Access isn't a database, its an application front end.

    What you are talkign about know is confusing Access the front end tool with the back end stroage mechanis, which in Access can be SQL server (or any server for that matter) or JET (the default storage mechanism in Access) if yo0u develop forms in Access those forms can 'talk' to SQL Server or JET data

    What this means in practice is that your front end, whatever it is coded in will have to replicate that Access form paradigm. so you wold be better off posting your question int he more appropriate front end forum as opposed to the Access forum
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by VSCurtis View Post
    My application will not be done in Access. The Access DB is merely a prototype. The final DB will be upsized from Access to SQL Server. Thanks for your input.
    With SQL Server you'll have far more possibilities to define data validation rules.
    Have a nice day!

  8. #8
    Join Date
    Sep 2014
    Posts
    48
    Yes, I am aware that SQL Server will provide more flexibilty. I'm simply trying to put as many pieces in place before I upsize the DB. Healdeam, please do not lecture me as if I'm a newbie to Access or databases in general. I've used EVERY version of Access. I have two BS Degree in my field and I do know the difference between Access and Jet. Please keep your smug comments to yourself.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What I mean (sorry if I was not clear enough) is that I'm not sure that trying to set validation rules in Access (or in Jet ) will help to clear the path to defining them in SQL Server.

    As you know, there are many limitations when you define validation rules in Acces: you cannot use references to other columns, you can only use built-in functions, etc., while all these limitations do not exists when working with SQL Server, where you can use references to other columns of the same table, use user defined functions (to a certain extend), use a whole set of regular expressions (while Access only "knows" a subset of them), use triggers, etc.

    What I would do would be to define the validation rules on paper, then implement them directly in SQL Server without bothering with the Access (Jet) step.
    Have a nice day!

  10. #10
    Join Date
    Sep 2014
    Posts
    48
    I do appreciate your further comments and I agree with you. I also appreciate the fact that you have shown both courtesy and repect in expressing those comments. Thank you for your input.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  12. #12
    Join Date
    Sep 2014
    Posts
    48
    I failed to mention one other field that I've been trying to create a validation rule for. FileDuration text field 10 positions long. Value in this field takes the format of HH:MMS and represents the "play time" or song length of the audio file. Would like to ensure that the value of this field contains a valid passage of time, x hours x minutes and x seconds. Yes, I do have need to record hours. Several chapters in some of my audiobooks are just over an hour long. Value was originally in miliseconds but that seemed useless so I converted to HH:MMS.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Be careful when using a Date/Time column for storing a duration, this is generally considered as a bad practice:
    Durations are best stored as a start date/time value and
    end date/time value. This is especially true given that
    you usually need the start and end points to calculate
    a duration. It is possible to use a TIME data type if the
    duration is less than 24 hours, but this is not what the
    type is intended for and can cause confusion for the
    next person who has to maintain your code.
    The whole document can be downloaded at: Free eBook - 119 SQL Code Smells
    Have a nice day!

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry I disagree
    there is nothing wrong in storing elapsed time in a datetiem column PROVIDING that you never ever try to format the result as a calendar date

    in this application there is no start date/time.

    the alternative is to convert eh elapsed time into number of seconds as an integer value

    as long as you don;t EVER use month and year elements in your formatting AND/or you don't have more than 31 days worth of music its fine.

    the ebook is great, but like most 'rules' in the systems world there's alwasy cases that blow apart the 'rule' as long as you are concious of why you are making that design choice and can justify it then a 'rule' can be set aside

    yes using a datetime column for something that could span years is daft.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Sep 2014
    Posts
    48
    As I stated this field is stored as a text field. This field is 10 positions long in HH:MMS. There is NO start time/date or end date/time only the number of hours minutes and seconds that the file lasts. It could be a song or a chapter from an audiobook, maybe a movie, or a video. My question is: Is it possible to constuct a field level validation rule to ensure that the value stored in this field is a valid value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •