Results 1 to 6 of 6
  1. #1
    ouh is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    3

    MS Access Query Help

    Hi all,



    New to the forum and SQL and MS Access.

    I need some help with a date validation query.

    I have a dataset where all the columns need validating, one of these columns is a date column. In Access this date column is a text column as the dates are manually entered and can be incorrect. I need to find these incorrect dates in order to send the dataset back to be corrected. I use the ISDATE function and can identify which dates are not in the correct format. However I need to do other date validation checks i.e. whether a date is in the future. Is it possible to build a query that will look at each date in the column and if it looks like a date then treat it as a date, if it doesn't look like a date then ignore?

    Hope that makes sense.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have a look at the CDate() function . So something along the lines of
    Code:
    MyDate: IIF(IsDate(CDate[YourFIeld]),CDate([YourField]), Null)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ouh is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    3
    thanks for the quick response.

    will that work in a statement like:

    select id, Date1, Date2
    from table 1
    where IIF(IsDate(CDate[Date1]),CDate([Date1]).Null) > IIF(IsDate(CDate[Date2]),CDate([Date2]),Null);

    I'm unable to try it out at the moment hence the question.

    Thanks again.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are Date1 and Date2 the best names you have? Don't these dates mean something more specific?
    I would make the date field a Date datatype in the table.
    You could have a Date picker on a form to reduce manual data entry errors.
    CDate and DateDiff could be useful in your validation.

    Good luck.

  5. #5
    ouh is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    3
    Date1 and Date2 where just examples. One is a registration date and the second an expiration date. I cannot change the datatype as this could turn a typo into a valid date and pass validation when it shouldn't. The initial validation would be to check for valid date types in the format dd/mm/yyyy, anything else would be a failure. Next validation would be to see if there are any values where expiration date is before registration date as these would fail validation. Maybe I could create another table and store all strings that look like dd/mm/yyyy and convert this into a Date datatype.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ouh View Post
    The initial validation would be to check for valid date types in the format dd/mm/yyyy, anything else would be a failure.
    What is date format is moot - what counts is is the entry date or not! Really all dates in Access are long integers. E.g. the value of today's date is 43303. When you format your control linked to date field as "dd/mm/yyyy", Access accepts entries in this format, and maybe a couple of other formats too - but not all possible date formats.

    With control formatted as date, user can't enter anything Access doesn't recognize as date - like 66/03/2018. When the control is formatted as string, you have to check for every month, falls the day into limits or not (leap years included). Otherwise user can enter the string "66/03/2018" without any problems. Are you in dire need for this work?

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

Similar Threads

  1. Replies: 3
    Last Post: 05-13-2016, 01:15 AM
  2. Replies: 1
    Last Post: 12-14-2015, 04:26 PM
  3. Replies: 2
    Last Post: 10-07-2015, 12:28 PM
  4. Replies: 3
    Last Post: 04-24-2012, 08:32 AM
  5. Replies: 12
    Last Post: 05-22-2011, 03:49 PM

Posting Permissions

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