Access 2010 vba to validate date format from imported excel worksheet

Hi,

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"
DoEvents
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"
                Else
            '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
            

            ' DATE FORMAT CHECK. THIS IS THE BLOCK OF CODE THAT DOES NOT CATCH THE WRONG FORMAT DATE
            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
Loop



Many thanks
andrewpiconnectAsked:
Who is Participating?
 
peter57rCommented:
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.
0
 
peter57rCommented:
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.
0
 
andrewpiconnectAuthor Commented:
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?
0
 
andrewpiconnectAuthor Commented:
Many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.