Access 2010 vba to validate date format from imported excel worksheet

Posted on 2012-09-11
Last Modified: 2012-09-11

I am setting up a process to upload data from an excel spreadsheet to my access 2010 database. As part of this process i require the data to be validated prior to upload.

I need to validate the values in the date column as follows: (1) a valid date, (2) whether it is a future date (3) whether it is in the format "dd/mm/yyyy".
I have successfully done the first 2 parts of this (see code below).

It is the 3rd part i am stuck on. If the date in the excel spreadsheet has been entered in the wrong format ie "mm/dd/yyyy" my validation code still alows this to pass the validation check.

Below is the code i am using. Any ideas please anyone?

intRowCounter = 1
Do Until intRowCounter > Me.txtLastRow
wsTemp.range("E1").Value = "DepartDate"
Me.lblValidate.Caption = "Depart Date Check: Processing row " & intRowCounter
        If Len(Trim(ws.range("E" & intRowCounter))) > 0 Then
                'VALID DATE CHECK
            If Not IsDate(Trim(ws.range("E" & intRowCounter))) Then
                    wsTemp.range("E" & intRowCounter).Value = "Invalid Depart Date"
                    Me.lblValidate.Caption = "Invalid Depart Date Found."
                    Me.txtFeedback = "Errors were found in the spreadshhet. Please amend and re-check"
            'PAST DATE CHECK
                If IsDate(Trim(ws.range("E" & intRowCounter))) Then
                    If DateDiff("d", Trim(ws.range("E" & intRowCounter)), Now()) > 1 Then
                    wsTemp.range("E" & intRowCounter).Value = "Date is in the past"
                    Me.lblValidate.Caption = "Depart Date is in the past."
                    Me.txtFeedback = "Errors were found in the spreadshhet. Please amend and re-check"
                    End If
                End If
                End If

            If Format(Trim(ws.range("E" & intRowCounter)), "dd/mm/yyyy") <> True Then
                    wsTemp.range("E" & intRowCounter).Value = "Invalid Depart Date"
                Me.lblValidate.Caption = "Wrong Format Depart Date."
                    Me.txtFeedback = "Errors were found in the spreadshhet. Please amend and re-check"
                End If

        End If

intRowCounter = intRowCounter + 1

Many thanks
Question by:andrewpiconnect
    LVL 77

    Expert Comment

    The format() function formats the date as instructed; it doesn't test the format.

    Unless the 'date' in excel is actually entered as a string/text value then 'testing' the format is meaningless. Access will import the date as an integer and then format it in any way you define.

    Author Comment

    Ok. So i understand what you mean but i am trying to avoid a user enterng a date in the excel sheet of 10 Jan 2013 (10/01/2013) as 01/10/2013 which could be read as 1 Oct 2013.

    Is there a way i can validate this to alert the user the date is not in the dd/mm/yyyy format?
    LVL 77

    Accepted Solution

    In excel you can format the cell(s) in the way you want the date to be displayed - so choose a format that has alpha months.

    This does not impose any constraint on how the date is entered but it makes it clear to the user what date they have entered.

    To apply constraints on the data entered select the cells and then choose Data Validation on the Data Tools ribbon.
    You can then set the validation as requiring a date - which will have to conform to regional settings to be recognised. You can also set limits on the values entered.

    Author Closing Comment

    Many thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now