[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access 2010 vba to validate date format from imported excel worksheet

Posted on 2012-09-11
Medium Priority
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
  • 2
  • 2
LVL 77

Expert Comment

ID: 38386719
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

ID: 38386992
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

peter57r earned 2000 total points
ID: 38387056
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

ID: 38387115
Many thanks

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

830 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