Results 1 to 8 of 8
  1. #1
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155

    Validating date

    In the attached DB the dates entered in the REMARKS field need to be validated, such that they should be in the format dd-mm-yy and should also not be less than todays's date. How to achieve this?
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,002
    dont let them enter data directly into your target table.
    make a temp table with a date field. the form will validate the date.
    then they click save, and a query updates the actual field .

  3. #3
    Bob Fitz is online now Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,205
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,923
    Quote Originally Posted by Kundan View Post
    In the attached DB the dates entered in the REMARKS field need to be validated
    Can't open your app in v2007, so I'm guessing here...but 'REMARKS field' sounds like a Memo Field...or Long Text, in later versions of Access...and things like dates that need to be validated shouldn't be entered in these fields, but in a separate control/field.

    Entries for this kind of thing should be entered in a separate Table...related to your main Table...and displayed as a Subform...with a Date field and a Remarks field for each 'remarks' entry. Then validating the Date field would be a simple matter.

    The cardinal rule for Memo/Long Text fields is that they never contain material that needs to be manipulated in any way...by sorting, searching, formatting, etc.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    @ Linq
    I re-saved the dB in A2010 (my version) as A2007 format - hope you can open it.

    You were mostly correct - the REMARKS Field Type is TEXT / Field Size = 200.
    I also think the OP should be using a sub form for entering dates.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,923
    Thanks for the effort, Steve, but I still get the 'unknown format' error message.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    @ Linq
    One last try....

    Click image for larger version. 

Name:	Form1.png 
Views:	11 
Size:	91.2 KB 
ID:	40720

    Click image for larger version. 

Name:	Table1.png 
Views:	11 
Size:	105.0 KB 
ID:	40721
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,923
    Thanks, Steve!

    In that case (even though REMARKS is simply a Text Field with a size of 200...rather than a Memo Field) the answer in #4 still applies.

    Trying to separate the date portion out of a String is going t be problematic and, as noted above, really shouldn't be done, design-wise.

    Does the date appear just anywhere in the String...or is it, perhaps, the first thing entered? The latter would make it somewhat easier to parse out.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Validating
    By Kundan in forum Forms
    Replies: 17
    Last Post: 04-16-2019, 09:46 PM
  2. Validating the table
    By XPeriment in forum Access
    Replies: 5
    Last Post: 08-10-2016, 09:43 AM
  3. Validating data against two tables
    By MTSPEER in forum Programming
    Replies: 3
    Last Post: 03-30-2015, 10:48 AM
  4. validating fields
    By bhatia.puja in forum Access
    Replies: 3
    Last Post: 07-28-2011, 05:24 AM
  5. Validating tables
    By JVagenheart in forum Database Design
    Replies: 1
    Last Post: 06-10-2006, 09:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums